# I know the average (110) and a 75th percentile (120). Calculate the SD

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?

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)$$.

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

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)

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:

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

• Thank you it works fine. Commented Jan 27, 2021 at 7:18