Problems with NORMDIST

[Kevin]

Should I be concerned that NORMDIST(4.92, 4.94, 0.08, FALSE) returns
4.83? This should be the noncumulative (density, mass) function
associated with the normal distribution. Surely it cannot exceed 1
because the entire area under the curve cannot exceed 1, by definition
of probability distribution. The related NORMSDIST problem:
NORMSDIST(-0.25, 0, 1, FALSE) returns 0.3866, which seems reasonable.

Can someone please reconcile the answers, or explain why Excel allows
probability distributions to have values greater than one?

Thank you.

 

[Tom Ogilvy]

4.92 is 25 standard deviations to the left of the mean. Until xl2003, the
Normal distribution function was known to give bad results deep into the
tails. Anything greater than -3 standard deviations is very small. For 25
standard deviations, you probably would be safe in assuming a probability of
0. Also, note that the probability of any single value in a continuous
distribution is considered to be zero.

http://support.microsoft.com/kb/827371/en-us
Description of the NORMDIST function in Excel 2003

 

[Matthias Klaey]

This time it is not a problem with Excel Kevin uses a mean of 4.94
and a standard deviation of 0.08, so 4.92 is about two standard
deviations from the mean.

You need to change the fourth parameter from FALSE to TRUE, then you
get

NORMDIST(4.92; 4.94; 0.08; TRUE)

= 0.401293674

which is the correct result. With FALSE, you get the value of the
density function, which can be well bigger than 1. With TRUE, you get
the desired probability (=(cumulative density) value.

HTH
Matthias Kläy

 

[Guest]

Your assumption that the density cannot exceed 1 is incorrect. To see this
easily, note that almost all of the cumulative area is contained within
mean+/-3*SD, which in your case is a region that is only 0.48 wide; therefore
for that area to approach 1, the height must exceed 1 in at least part of
that region.

Alternately, you can calculate it directly for yourself from the definition
of the normal pdf
=exp(-(((4.92-4.94)/0.08)^2)/2)/sqrt(2*pi())/0.08
Note that the extra parentheses immediately following the minus sign in the
exp() function are required, because Excel follows a non-standard convention
where unary negation is higher in priority than exponentiation.

Jerry

 

[Matthias Klaey]

Sorry, I have to correct myself: The value of 4.92 is only 1/4th of a
standard deviation of 0.08 from the mean of 4.94, because

(4.92 - 4.94)/0.08 = -0.02/0.08 = 0.25

Then the result of 0.4 for the probability value is very plausible.

Greetings
Matthias Kläy

 

[Tom Ogilvy]

Bad math on my part, read something other than what you actually posted (red
faced) - sorry about that.

 

[Kevin]

Jerry,

You are right, and that answers my question. I guess the density has
to exceed 1 in order for the area under the curve to sum to 1. Thanks
so much!

Kevin