Probability functions -HORRIBLE PROBLEM

[theodoreallnutt]

Hi,

can anyone tell me why in my version of Excel 2000, all the probability functions, NORMDIST, BETADIST, GAMMADIST, etc return values of more than one??? Surely these functions are supposed to integrate to one but I get crazy values that don't integrate to anything.. somtimes nearly 100 other times nearly 1000!

Help much appreciated - pulling my hair out here..

Thanks.

 

[theodoreallnutt]

As an example: try a colun of integers 1 to 10 as input for Normdist with, say 4 as mean and 1.3 as the s.d. You will get a reasonable looking answer with all the probabilities adding up to nearly 1.

Now try a column of data with 0 to 0.01 in 0.001 increments. Use Normdist on it with 0.004 as the mean and 0.0013 as the s.d..

thats: =NORMDIST(AH50,4,1.3,FALSE) on 1 to 10 . sum = 1
and: =NORMDIST(AH50,0.004,0.0013,FALSE) on 0 to 0.01 sum = 1000!!!!

Am I missing something?

 

[Oaktree]

Welcome to the board.

I don't really have an answer for why this happens, but it looks like it's an issue of Excel putting the decimal in the wrong place.

For example:

=NORMDIST(0.005,0.004,0.0013,FALSE) = 228.2849
=NORMDIST(5,4,1.3,FALSE) = 0.228285

and

=NORMDIST(0.006,0.004,0.0013,FALSE) = 93.97422
=NORMDIST(6,4,1.3,FALSE) = 0.093974

The issue seems to be corrected with numbers >=1.

I'd try using = NORMDIST(x*1000, u * 1000, s * 1000, FALSE) for small values of x, u, and s.