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.
Data | Probability Distribution |
140 | 0.04 |
141 | 0.06 |
142 | 0.08 |
143 | 0.10 |
144 | 0.11 |
145 | 0.12 |
146 | 0.11 |
147 | 0.10 |
148 | 0.08 |
149 | 0.06 |
150 | 0.04 |
I have used a NORMDIST formula in an array form in the cell B2 in my sheet to populate the probability distribution.
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)
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!
Nice article. Gets across the point in a short and concise manner, and didn’t beat around the bush.
Thank you so much for this! This was a huge help and saved me a bunch of time.
Hi, Morgan Christopher,
Thanks for your valuable feedback!