# How can I determine standard deviation in excel given the probability that values will fall within a defined range?

Assuming a normal distribution with a mean of zero, if I am told that 68.2% of the population values are within +/- 4.7, I can conclude that the standard deviation is also 4.7.

But what if I am told instead that 50% of the population values are within +/- 4.7? How can I determine the standard deviation in excel given this information?

Thanks for any assistance you can provide.

Let $$X \sim N(0,\sigma^2)$$ be a centered normal distributed random variable and $$Z\sim N(0,1)$$ be a standard normal distributed random variable and $$\Phi$$ its distribution function. For a given $$a\in \mathbb{R}$$ and $$c \in (0,1)$$ you have, since the density of $$Z$$ is symmetric around $$0$$: \begin{align*} P(- a\leq X \leq a) & = P\left(-\frac{a}{\sigma} \leq Z \leq \frac{a}{\sigma}\right)\\&= \Phi\left(\frac{a}{\sigma}\right) - \Phi\left(-\frac{a}{\sigma}\right)\\&= 2 \Phi\left(\frac{a}{\sigma}\right)-1 \\& \stackrel{!}{=}c \\ \qquad \Rightarrow \qquad \sigma & = \frac{a}{\Phi^{-1}((c+1)/2)}. \end{align*} Hence, for $$a=4.7$$ and $$c=0.5$$ you can derive the value of $$\sigma$$ by writing 4.7/NORM.S.INV(0.75) in excel, as NORM.S.INV(prob) corresponds to $$\Phi^{-1} (prob)$$.