Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / February 2006

Tip: Looking for answers? Try searching our database.

Problems with NORMDIST

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Kevin - 21 Feb 2006 18:15 GMT
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 - 21 Feb 2006 20:57 GMT
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

Signature

Regards,
Tom Ogilvy

> Should I be concerned that NORMDIST(4.92, 4.94, 0.08, FALSE) returns
> 4.83?  This should be the noncumulative (density, mass) function
[quoted text clipped - 7 lines]
>
> Thank you.
Matthias Klaey - 21 Feb 2006 22:21 GMT
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
Signature

www.kcc.ch

>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
[quoted text clipped - 5 lines]
>http://support.microsoft.com/kb/827371/en-us
>Description of the NORMDIST function in Excel 2003

>>"Kevin" <kcincotta@gmail.com> wrote
>> Should I be concerned that NORMDIST(4.92, 4.94, 0.08, FALSE) returns
[quoted text clipped - 6 lines]
>> Can someone please reconcile the answers, or explain why Excel allows
>> probability distributions to have values greater than one?
Matthias Klaey - 22 Feb 2006 00:56 GMT
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
Signature

www.kcc.ch

>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.
Tom Ogilvy - 22 Feb 2006 01:46 GMT
Bad math on my part, read something other than what you actually posted (red
faced) - sorry about that.

Signature

Regards,
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
[quoted text clipped - 17 lines]
> >
> > Thank you.
Jerry W. Lewis - 21 Feb 2006 23:44 GMT
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

> Should I be concerned that NORMDIST(4.92, 4.94, 0.08, FALSE) returns
> 4.83?  This should be the noncumulative (density, mass) function
[quoted text clipped - 7 lines]
>
> Thank you.
Kevin - 22 Feb 2006 17:15 GMT
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

> 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
[quoted text clipped - 22 lines]
> >
> > Thank you.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.