How to Calculate Probability Distribution in Excel (with Quick Steps)

Get FREE Advanced Excel Exercises with Solutions!

You have come to the right place if you are looking for the answer or some unique tips to calculate Probability Distribution in Excel. There are some quick steps to calculate Probability Distribution in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Letโ€™s move on to the articleโ€™s main discussion.


Download Practice Workbook

You can download the practice workbook from here:


What Is Probability Distribution?

The term probability distribution is in general a representation of the frequency distribution of a specific data series. It illustrates the possibility of certain trials under specific conditions among the variable values. The basic rule of the probability distribution is the probability of a value, the higher the frequency is, and vice versa.

Probability distribution can be shown with or without a graph based on the function used. It is a very useful Excel feature to project population, performance, climatic forecast, business proposition, etc.


Steps to Calculate Probability Distribution in Excel

In this section, I will show you the quick and easy steps to calculate Probability Distribution in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

๐Ÿ“Œ Step 1: Create a Dataset

First, you have to create a dataset including events occurring, the frequency of each event (xi), and a column to get the probability of each event P(x).

Sample Dataset to Calculate Probability Distribution in Excel

  • Then, calculate the sum of the frequency of the events. For this, insert the following formula using the SUM function in cell D11:
=SUM(D5:D10)

Using SUM function

Read More: How to Calculate Binomial Probability in Excel (with Easy Steps)


๐Ÿ“Œ Step 2: Calculate Probability P(x) for Each Event

After that, you will calculate the probability of each event in the dataset. You have to divide the frequency by the total of the event occurring to get the probability value.

  • For this, use the following formula in cell E5:
=D5/$D$11

Calculate Probability P(x) for Each Event

Dragging Fill Handle icon

  • Now, calculate the total of the probability values, which will be 1.

Read More: Exponential Probability Distribution in Excel (2 Suitable Examples)


๐Ÿ“Œ Step 3: Calculate Mean of Probability Distribution in Excel

Now, you will use the following formula to get the Mean value from the probability distribution value.

Formula of Mean value from Probability

Calculate Mean of Probability Distribution in Excel using SUMPRODUCT function

  • Thus, you have calculated the mean value from the probability distribution in Excel. โ€˜

Read More: Calculating Probability in Excel with Mean and Standard Deviation


๐Ÿ“Œ Step 4: Calculate Standard Deviation Probability Distribution in Excel

Then you have to calculate the Standard Deviation from the probability distribution in Excel. The formula for the Standard Deviation of the probability distribution is:

Formula of standard deviation value from Probability

  • For this, first, insert the following formula in cell F5,
=(D5-$D$13)^2

  • Then, drag the Fill Handle icon to apply the same formula to the other cells also.

  • Now, multiply the values of the D and F columns. Insert the following formula in cell G5, and then autofill the column:
=(D5-$D$13)^2

  • Finally, to get the standard deviation, you have to square-root the sum total of the values of column G. For this, insert the following formula in cell D14:
  • Calculate Standard Deviation Probability Distribution in ExcelThus, you have calculated the standard deviation from the probability data.

Read More: How to Calculate Probability of Exceedance in Excel


How to Calculate Normal Probability Distribution in Excel

Probability is a mathematical concept that predicts the likelihood of the occurrence of an event during an experiment. A probability distribution, on the other hand, is a mathematical/statistical function that describes the likelihood of the occurrence of all possible events during that experiment. Of the few types, the normal probability distribution is the single most important and the most commonly used probability distribution in probability and statistics. The Normal Probability Distribution can be characterized by its Mean and Standard Deviation.

  • First, prepare a dataset with the information of 10 studentsโ€™ names and their grades.
  • Secondly, insert the following formula using the AVERAGE function in cell C13:
=AVERAGE(C5:C11)
  • After this, insert the following formula using the STDEV.S function in cell C14.
=STDEV.S(C5:D11)

Sample dataset to Calculate Normal Probability Distribution in Excel

  • At this stage, use the NORM.DIST function to calculate the normal distribution for Mean and Standard deviation in cell D5.
=NORM.DIST(D5,$E$5,$F$5,FALSE)
  • Then, drag the fill handle to autofill the column.

Using NORM.Dist function in Excel

Now, you have to plot the Normal Distribution graph.

  • First, you have to sort the Grade and Normal Distribution values from smallest to largest from the Sort & Filter section in the Home
  • Then, insert a Scatter Chart and select the Marks column as the X-axis data and Normal Distribution column as the Y axis

Calculate Normal Probability Distribution in Excel

  • Thus, you have created the Normal Distribution curve for your dataset.

Conclusion

In this article, you have found how to calculate Probability Distribution in Excel. I hope you found this article helpful. You can visit our website, ExcelDemy, to get more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo