The Probability Density Function is a well-known statistical parameter. It shows the probability of a continuous random variable having a value in a particular range. Excel provides some effective functions to calculate the probability density function of a variable. In this article, we will show how to calculate probability density function in Excel.
What Is Probability Density Function?
Probability Density Function (PDF) is a function that represents the density of a continuous random variable in a particular range. For example, in a class, different students get different marks in a particular course. Say, 20 students got 80, 13 students got 75, and so on. A probability density function will predict the probability of a student getting a mark between 80 and 75 or below 75 or above 80 and so on. But the main concern is that the random variable of which we are trying to find the probability density function, has to be a continuous one.
How to Calculate Probability Density Function in Excel: 2 Easy Ways
Throughout this article, we will discuss 2 handy ways to calculate the probability density function in Excel. In the first method, we will use the NORM.DIST function along with the AVERAGE and STDEV functions to find out the probability density function of a given set of data. In the second method, we will try to find out the probability density function of a standard normal distribution using the NORM.S.DIST function. Hence, here the mean will be zero, and the standard distribution will be 1.
1. Using NORM.DIST Function
The NORM.DIST function returns the normal distribution of a variable. Here, we will try to find out the probability distribution of the variable by calculating the mean and standard distribution of a set of values that the variable can take. Finally, we will set the argument “cumulative” to FALSE to find out the probability density function.
Steps:
- Firstly, select the F4 cell and write the following formula,
=AVERAGE(C5:C11)- Then, hit Enter.
- Consequently, we will get the mean of the values.
- Then, choose the F5 cell and type the following,
=STDEV(C5:C11)- Hit Enter.
- As a result, we will get the standard deviation of the data.
- After that, click on the F6 cell and write the following formula down,
=NORM.DIST(76,F4,F5,FALSE)- Finally, hit Enter.
- Consequently, we will get the Probability Density Function for the value 76 according to the data available.
Read More: How to Calculate Empirical Probability with Excel Formula
2. Applying NORM.S.DIST Function
The standard normal distribution is a special type of normal distribution. Here, the mean is always zero and the standard distribution is one. Here, in order to get the probability density function we will have to insert the Z- value as an argument for the NORM.S.DIST function. And like the previous one we will set the value of the cumulative argument as FALSE.
Steps:
- In the beginning, select the C6 cell and type the following formula,
=NORM.S.DIST(C6,FALSE)- Then, hit Enter.
- As a result, we will get the probability density function for the Z- value 1.25 from the standard normal distribution.
How to Make Cumulative Distribution Graph in Excel
In this method, we will use the NORM.DIST function to calculate the cumulative distribution function of the available values. Then, we will plot the values returned by the function to plot the cumulative distribution graph.
Steps:
- To start with, select the D5 cell and type the following,
=AVERAGE(C5:C11)- Then, press Enter.
- As a result, we will get the mean of the values.
- After that, select the E5 cell and write the following formula,
=STDEV(C5:C11)- Then, hit the Enter button.
- As a result, we will get the standard deviation of the values.
- After that, choose the F5 cell and write the following formula down,
=NORM.DIST(C5,$D$5,$E$5,TRUE)- Consequently, we will have our cumulative density function value for the value in the C5 cell.
- For convenience, convert it from general to percentage format.
- Finally, lower the cursor down to autofill the rest of the cells.
- After that, first, select the Marks and Cumulative Distribution columns.
- Secondly, go to the Insert tab.
- Thirdly, from the Charts option select Insert Scatter or Bubble Chart.
- Finally, select any of the scatter charts suitable for you.
- Consequently, we will have the Cumulative Distribution graph for the data.
Read More: How to Calculate Cumulative Probability in Excel
Download Practice Workbook
You can download the practice workbook here.
Conclusion
In this article, we have discussed 2 ways to calculate the probability density function in Excel. After going through this article, readers will have a clear idea about what a probability density function is and how they should calculate it in Excel.
