Probability functions -HORRIBLE PROBLEM

theodoreallnutt

New Member
Joined
Oct 25, 2006
Messages
2
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

theodoreallnutt

New Member
Joined
Oct 25, 2006
Messages
2
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,650
Members
410,627
Latest member
georgealice
Top