NORMDIST(x,mean, stdev,false) vs f(x)

[Betty]

I demonstrate that the Normal distribution is a good approximation of the
binomial in Excel but I found there was a diference in entering the formula


f(x)=1/(stdev*sqrt(2*PI()))*exp(-(x-mean)^2/(2*stdev^2))
and
NORMDIST(x, mean, stdev, false)

For example, for the binomial n=25, p=0.25
binomdist(6,25,0.25,false)=0.182820
normdist(6, np, sqrt(npq),false)=0.183039
f(6)=0.184116

Could someone explain where the difference between NORMDIST and f(x) comes
from?

Thanks!

 

[Jerry W. Lewis]

See Help for "About calculation operators" subtopic "The order in which
Excel performs operations in formulas". There you will learn that Excel
performs unary negation before exponentiation, thus your formula is
equivalent to
=1/(stdev*sqrt(2*PI()))*exp((-(x-mean))^2/(2*stdev^2))
which is, of course, not equal to the Normal pdf.

This aspect of Excel's operator precedence is not the most common
convention, but it is well documented. Moreover, since Excel has always
worked this way, I would be extremely surprised if MS ever changes it.

To get the calculation that you intended, you can use either
=1/(stdev*sqrt(2*PI()))*exp(-((x-mean)^2)/(2*stdev^2))
or
=1/(stdev*sqrt(2*PI()))*exp(0-(x-mean)^2/(2*stdev^2))

Jerry

 

[Conrad Carlberg]

Jerry,

Excellent exposition. I've scratched my head over that myself and failed to
get it.

 

[Jerry W. Lewis]

Thanks.

Jerry