Adnan Masruf
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.
Download Practice Workbook
You can download the practice workbook here.
What Is Probability Density Function?
The Probability Density Function (PDF) is the 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, the random variable of which we are trying to find the probability density function, has to be a continuous one.
2 Easy Ways to Calculate Probability Density Function in Excel
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 Probability in Excel (3 Practical Examples)
Similar Readings
- How to Model Uniform Probability Distribution in Excel (3 Ways)
- How to Calculate Conditional Probability in Excel (2 Easy Ways)
- Poisson Probability Distribution in Excel (4 Practical Examples)
- How to Create Option Probability Calculator in Excel
- How to Get Simulation Probability in Excel (with Easy Steps)
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.
Read More: How to Use Normal Probability Distribution in Excel (3 Cases)
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 (with Easy Steps)
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.
Related Articles
- How to Calculate Binomial Probability in Excel (with Easy Steps)
- How to Calculate Variance of Probability Distribution in Excel
- Exponential Probability Distribution in Excel (2 Suitable Examples)
- How to Apply Weighted Probability in Excel (3 Useful Methods)
- Find Marginal Probability in Excel (2 Simple Ways)
- How to Make a Probability Tree Diagram in Excel (3 Easy Methods)
