How to Use Excel NORMDIST Function (2 Applications)

Excel NORMDIST Function

In this article, we will get familiar with the Excel NORMDIST function. We can use this function in statistics as well as hypothesis testing. We will go over two applications of this function.


Download Practice Workbook

You can download the practice workbook from here.


Overview of Excel NORMDIST Function

  • Description

NORMDIST function returns normal distribution for Mean and Standard deviation. In statistics, this function can be used for a variety of things, including hypothesis testing.

  • Generic Syntax

NORMDIST(x,mean,standard_dev,cumulative)

  • Argument Description
ARGUMENT REQUIREMENT EXPLANATION
x Required The supplied value to calculate the distribution
mean Required It indicates the arithmetic average of the distribution
standard_dev Required This Argument describes the standard deviation of the distribution
cumulative Required Basically, it’s a logical value. It decides the form of the function.

Gives cumulative distribution function if the value is TRUE

Gives probability mass function if the value is FALSE

  • Returns

Returns the normal distribution from the specified mean and standard deviation.

  • Available in

In Excel 2010, the NORMDIST function was replaced by the NORM.DIST function. The NORM.DIST function is more precise than the NORMDIST function. In Excel 2010, the NORMDIST function is still available. It is saved in the compatibility functions list to allow compatibility with older Excel versions.


2 Applications of NORMDIST Function in Excel

First and foremost we will introduce the dataset that we will use for this article. In this case, we have a dataset of share prices and their corresponding Normal Distribution value. Moreover, we have the mean and standard deviation of the share price. The dataset is given in the image below:

2 Applications of NORMDIST Function in Excel

Therefore, if we plot a graph of Share Price against Normal Distribution, the graph will look like the following figure.


1. Use of NORMDIST Function to Calculate the Cumulative Distribution of a Dataset

A Cumulative Distribution Function (CDF) describes the probability that a random variable takes on a value less than or equal to some number. For example, we will use our example dataset to calculate the Cumulative Distribution for the first four data of the share price column.

Use of NORMDIST Function to Calculate the Cumulative Distribution of a Dataset

  • Firstly, select cell C10 and insert the following formula:
=NORMDIST(B10,C5,C6,TRUE)
  • Next, press Enter.
  • Then, we can see the value of Cumulative Distribution for share price 200 in cell C10.

Use of NORMDIST Function to Calculate the Cumulative Distribution of a Dataset

  • Finally, inserting the following formulas for all the other values of share prices we will get

the corresponding cumulative distributions.


1.1 Smaller Than a Particular Value

Now, let us just think we want to calculate a cumulative distribution for the values less than 250. Let’s see how we can do this:

  • Firstly, select cell C8 and insert the following formula:
=NORMDIST(C7,C5,C6,TRUE)
  • Then press Enter. We will get the cumulative distribution for values smaller than 250.

The following graph is an illustration of the region of cumulative distribution for the share prices less than 250

Smaller Than a Particular Value


1.2 Between the Range of Two Values

In this example, we will calculate cumulative distribution between the range 250 to 400. Just do the following steps:

  • Firstly, select cell C9 and insert the following formula:
=NORMDIST(C7,C5,C6,TRUE)-NORMDIST(C8,C5,C6,TRUE)

Here, this formula subtracts the value of the cumulative distribution of cell C7 from the value of cell C8.

  • Next, press Enter and we will get the value cumulative distribution from the range 250 to 400. Besides the data table, we can see the graph showing the region for cumulative distribution.

Between the Range of Two Values


1.3 Greater Than a Particular Value

In this case, we will calculate the cumulative distribution for the values greater than 400. Let’s see how can we do this:

  • Firstly, select cell C8 and insert the following formula:
=1-NORMDIST(C7,C5,C6,TRUE)

Here, this formula subtracts the value of the cumulative distribution of cell C7 from 1.

  • Next, press Enter and we can see the cumulative distribution for the values greater than 400. Here, the graph is showing the region of cumulative distribution for values greater than 400.

Greater Than a Particular Value

Read More: The Different Ways of Counting in Excel


Similar Readings


2. NORMDIST Function in Excel to Calculate Probability Mass Function

For this example, we will consider the syntax of the NORMDIST function for Cumulative Distribution. Then put the cumulative argument value FALSE in place of TRUE. As a result, we will get the probability mass function for the data.

In this example, we will calculate the probability mass function for the first four values of the share price of our example data. Just follow the simple steps to do this.

  • Firstly, select cell C10 and insert the following formula:
=NORMDIST(B10,C5,C6,FALSE)
  • Next, press Enter and you will get the value Probability Mass Function for share price 250 in cell C10.  

NORMDIST Function in Excel to Calculate Probability Mass Function

  • Finally, insert the following formulas of the below image for the corresponding values of share prices. As a result, we will get the probability mass distribution for the other values of share prices.

Read More: How to calculate Average, Median, & Mode in Excel


Errors While Using Excel NORMDIST Function

While using the NORMDIST function, we can experience the following two kinds of errors.

1. #NUM! Error

If the Standard Deviation is blank, less than or equal to zero, the NORMDIST function returns ‘#NUM! Error’. Let’s see with examples:

  • Firstly, select cell C12 and insert the following formula.
=NORMDIST(B13,C5, ,FALSE)

In the formula, we will keep the Standard Deviation argument Blank.

  • Next, press Enter and we will get the #NUM! Error in cell C12.

#NUM! Error


2. #VALUE! Error

If any of the supplied inputs is a non-numeric value, the NORMDIST function produces a “#VALUE! error. Let’s see an example of this with the following dataset that we used previously for calculating Probability Mass Distribution.

  • Firstly, select cell C11 and insert the following code:
=NORMDIST(B11,C5,"text",FALSE)

We will use a Text Character in the place of the Standard Deviation argument.

  • Then, press Enter and we will get the  #VALUE! Error in cell C11.

#VALUE! Error


Conclusion

In the end, in this article, we have tried to make clear the concept of NORMDIST function to you. For a better experience download our practice workbook added to this article and practice yourself. If you feel any confusion just leave a comment in the below box.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

2 Comments
  1. You are confusing Probability Mass Function with Probability Density Function.

    Probability Mass Function = Discrete Values
    Probability Density Function = Continuous Values
    Cumulative Distribution Function = Continuous Values

    NORM.DIST(..,..,..,FALSE) = Probability Density Function and NOT Probability Mass Function
    NORM.DIST(..,..,..,TRUE) = Cumulative Distribution Function

    • Thank you Brian for your comment.

      Actually, we believe you are right. However, in Excel the NORMDIST function shows the probability mass function when it is set to False. We have kept the original naming as it.

      NORMDIST Function

      Moreover, if you go through the official documentation, you will notice it is written as “density function” in the latter part. So, chances are it may be displayed as ”probability mass function” but actually it is calculating the ”continuous values”.

      NORMDIST Function Official Doc

Leave a reply

ExcelDemy
Logo