1
$\begingroup$

I know the average (110) and a 75th percentile (120) of a normally distributed data set. I need to calculate the SD. I do not have access to the original data set. Can I calculate it in Excel?

$\endgroup$

3 Answers 3

2
$\begingroup$

For a quick numerical result you can set this as an optimization problem (in R, I do not know excel)

> optim(par=10,function(x){abs(qnorm(0.75,110,x)-120)},method="Brent",lower=1,upper=100)

$par
[1] 14.82602

which results in

> qnorm(0.75,110,14.82602)
[1] 120

Or for a simpler formula

$$SD=\frac{F(0.75)-\mu}{0.6744898}=\frac{120-110}{0.6744898}=14.82602$$

where 0.6744898 was obtained from the standard normal quantile function $F^{-1}(0.75)$.

$\endgroup$
1
  • $\begingroup$ Thank you, spot on. That helps a lot. In Excel, I found =10/NORM.S.INV(0.75) gives me the same answer. $\endgroup$ Commented Jan 25, 2021 at 13:38
1
$\begingroup$

In excel you can calculate this by determining the z value, which in excel is the norm.s.inv function. since,

Std Dev = (x - avg)/z

you can determine the values in your example by the following in excel:

Std Dev = (120 - 110)/norm.s.inv(0.75)

$\endgroup$
1
0
$\begingroup$

A dumb solution using Excel. Use the function NORM.INV where you set the probability at 0.75, the mean at 110 and the standard deviation to a variable that you can find empirically from a list of candidates values:

enter image description here

the solution is the SD value closest to the target of 120 (i.e. 14.8)

$\endgroup$
1
  • $\begingroup$ Thank you it works fine. $\endgroup$ Commented Jan 27, 2021 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.