Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator post in which we are looking at the functions for calculating various statistics related to the normal distribution - namely NORM.DIST, NORM. INV, NORM.S.DIST, NORM.S.INV, and STANDARDIZE.

Note that this post is intended to talk about how to use these functions in Excel - it is * not *a probability primer!

Also note that the functions we discuss here are those present in Excel 2010 and later - very similar functions (without the full stops) are present in Excel 2007 and earlier, and are available for compatibility reasons only in later versions.

Throughout this Tip, I will be using a well-known normal distribution as an example to illustrate each of the points - namely women's heights in inches. I am using a mean of 63.6 inches and a standard deviation of 2.5 inches.

**How to write these functions**

=NORM.DIST(x, mean, standard deviation, cumulative)

=NORM.INV(probability, mean, standard deviation)

=NORM.S.DIST(z, cumulative)

=NORM.S.INV(probability)

=STANDARDIZE(x, mean, standard deviation)

x - The value being tested

mean - The mean of the normal distribution in question (assumed to be 0 for the NORM.S functions)

standard deviation - The standard deviation of the normal distribution in question (assumed to be 1 for the NORM.S functions)

cumulative - An option setting - TRUE or 1 will return the cumulative distribution function (i.e. the probability that the value will be less than or equal to x), and FALSE or 0 will return the probability density function (the probability that the value will be exactly x).

probability - The probability that is being worked back from for the inverse functions

z - The z-value for the standard normal distribution - essentially the same as the x-value for NORM.DIST

NORM.DIST tells us the probability for a result at a particular value - either the spot probability of hitting that exact value, or the cumulative probability of seeing that value or lower. For example, we could calculate the probability that a woman is 5'7" (67 inches) as:

=NORM.DIST(67, 63.6, 2.5, FALSE) = 6.3%

And the probability that a woman is 5'7" **or shorter** would be:

=NORM.DIST(67, 63.6, 2.5, TRUE) = 91.3%

The probability that a woman is between 5' and 6' is the difference between these two functions:

=NORM.DIST(72, 63.6, 2.5, TRUE) - NORM.DIST(60, 63.6, 2.5, TRUE) = 99.96% - 7.49% = 92.47%

NORM.INV does the reverse - that is, if we want a given likelihood of a certain event happening, what threshold should we set? For example, if we want to find the lower quartile of women's heights:

=NORM.INV(25%, 63.6, 2.5) = 61.9 inches

We can invert this by considering symmetry - for example, if we want a threshold that 60% of women are taller than, we can find the one that 40% of women are shorter than.

NORM.S.DIST and NORM.S.INV are essentially the same functions if the mean is 0 and the standard deviation is 1; these functions are used more in raw statistics. STANDARDIZE counts how many standard deviations from the mean a certain value is.

**Generating random numbers that conform to a known distribution**

If you want to generate random numbers that follow a given mean and standard deviation, you can do so by using a RAND() function as the input to a NORM.INV function. This is far fairer for creating dummy data than creating truly random data. Check out the example in the attached file.

Previous post

TOTW index

Next post

*This blog is brought to you by the **Excel Community** where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a **full suite of online training modules** from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.*