Search code examples
excelexcel-formulastatisticsdistributionnormal-distribution

NORMDIST function is not giving the correct output

I'm trying to use NORMDIST function in Excel to create a bell curve, but the output is strange.

My mean is 0,0000583 and standard deviation is 0,0100323 so when I plug this to the function NORMDIST(0,0000583; 0,0000583; 0,0100323; FALSE) I expect to get something close to 0,5 as I'm using the same value as the mean probability of this value should be 50%, but the function gives an output of 39,77 which is clearly not correct.

Why is it like this?

Solution

  • A probability cannot have values greater than 1, but a density can.

    The integral of the entire range of a density function is equal 1, but it can have values greater than one in specific interval. Example, a uniform distribution on the interval [0, ½] has probability density f(x) = 2 for 0 ≤ x ≤ ½ and f(x) = 0 elsewhere. See below:

    enter image description here            enter image description here

    enter image description here

    enter image description here

    =NORMDIST(x, mean, dev, FALSE) returns the density function. Densities are probabilities per unit. It is almost the probability of a point, but with a very tiny range interval (the derivative in the point).

    shg's answer here, explain how to get a probability on a given interval with NORMIDIST and also in what occasions it can return a density greater than 1.

    For a continuous variable, the probability of any particular value is zero, because there are an infinite number of values.

    If you want to know the probability that a continuous random variable with a normal distribution falls in the range of a to b, use:

    =NORMDIST(b, mean, dev, TRUE) - NORMDIST(a, mean, dev, TRUE)

    The peak value of the density function occurs at the mean (i.e., =NORMDIST(mean, mean, dev, FALSE) ), and the value is:

    =1/(SQRT(2*PI())*dev)

    The peak value will exceed 1 when the deviation is less than 1 / sqrt(2pi) ~ 0.399,

    which was your case.

    This is an amazing answer on Cross Validated Stack Exchange (statistics) from a moderator (whuber), that addresses this issue very thoughtfully.