0
$\begingroup$

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.

$\endgroup$
1
$\begingroup$

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

$\endgroup$
0
$\begingroup$

You might want to try to set st_dev such that norm.dist(4.7,0,st_dev,True) = 0.75
Coming up with a strategy to solve this should be straightforward, or use trial and error.
(So, for that st_dev, 75% percent of the population values are below 4.7, which means that 25% are below -4.7, so 50% are in between -4.7 and 4.7, as required).

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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