How to Use the NORMDIST Function in Google Sheets

The NORMDIST function in Google Sheets returns the normal distribution aka Gaussian distribution for the specified mean/average and standard deviation.

Instead of using the NORMDIST, you can also use the NORM.DIST function in Google Sheets as the latter replaces the former. But I think both will work, at least for now and maybe in the future also for compatibility related purposes.

The normal distribution aka Gaussian distribution is the most important probability distribution that is symmetric about the mean.

It’s important because there are many scenarios where the data tends to be around a central peak.

Let’s consider the height of 11 boys (just a sample from an entire population) at the age of 11. The sample data in the below table is in the range A1:B12 in my Google Sheets file.

DataProbability Distribution
1400.04
1410.06
1420.08
1430.10
1440.11
1450.12
1460.11
1470.10
1480.08
1490.06
1500.04

I have used a NORMDIST formula in an array form in the cell B2 in my sheet to populate the probability distribution.

Bell Curve in Google Sheets

As you can see the data is spread out like a Bell Curve (I will explain how to create a Bell Curve properly in another tutorial).

Update: Bell Curve in Google Sheets.

Let’s see how to use the NORMDIST or the NORM.DIST function in Google Sheets based on the sample data above in column A to calculate the values in column B.

NORMDIST Function Syntax and Arguments in Google Sheets

Syntax

NORMDIST(x, mean, standard_deviation, cumulative)

The same syntax is applicable to the NORM.DIST function also in Google Sheets.

Arguments

x – The input value for which you want the normal distribution.

mean – The arithmetic mean (μ) of the distribution. To get the ‘mu’ value we can use the AVERAGE function in Google Sheets.

standard_deviation – The standard deviation (σ) of the distribution. To get the ‘sigma’ value, we can use the STDEV.P or STDEV.S function depending upon the data. If the data (here ‘x’) is a sample, use the STDEV.S else STDEV.P.

cumulative – Whether to use the cumulative distribution function (logical TRUE or 1) rather than the distribution function (Logical FALSE or 0). In my example above, to calculate the values in column B, I have used the logical FALSE argument.

Example of the Use of NORMDIST | NORM.DIST Function in Google Sheets

For the normal distribution, you must know the average and standard deviation of the ‘x’. In the below example I’ll explain how to get those values.

The heights of boys, which is my sample, are in the range A2:A12 in my sheet. In the cell D2, use the following AVERAGE formula which will return the arithmetic mean (au).

=average(A2:A2)

The heights of boys are actually a sample from an entire population. So I am using the below STDEV.S formula in cell E2 to get the standard deviation (sigma).

=STDEV.S(A2:A12)

Now let’s see how to use the NORMDIST function in Google Sheets.

In cell B2, insert the following formula and in this A2 is the ‘x’ argument. To cover the complete data (heights of boys) drag the formula down.

=NORMDIST(A2,$D$2,$E$2,0)
Example to NORMDIST Function in Google Sheets

When you drag down the NORMDIST (you can also use NORM.DIST) formula, only the ‘x’ changes as the corresponding cell reference A2 is set to relative.

But the Mean and Standard Deviation (the third and fourth arguments) are the same since the corresponding cell references $D$2 and $E$2 are set to absolute in the formula.

As you may know, the dollar sign with the cell references differentiates whether it’s absolute or relative.

NORMDIST Array-Formula Use in Google Sheets

The NORMDIST as well as the NORM.DIST functions in Google Sheets can return an array result. This helps us to avoid the use of a drag-drop formula to cover multiple ‘x’ values.

Here is that formula.

=ArrayFormula(NORMDIST(A2:A12,$D$2,$E$2,0))

Insert the above formula in cell B2. It will expand the result to B3:B12.

Additional Notes

  • In the above formulas, the last argument is 0 which means FALSE. Change it to 1 or TRUE to return the cumulative distribution function instead of returning the probability density function.
  • If the argument ‘standard_deviation’ is less than or equal to 0, the NORM.DIST would return the #NUM! error value in Google Sheets.
  • The formula would return the value of the standard normal cumulative distribution function of ‘x’ if ‘mean’ is 0, standard_deviation is 1, and ‘cumulative’ is TRUE or 1. Actually, in that case, there is another dedicated function named NORMSDIST/NORM.S.DIST.

That’s all about how to use the NORM.DIST or NORMDIST function in Google Sheets. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

3 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.