Wasim Akram
While working in Microsoft Excel sometimes we need to calculate probability and then plot a histogram graph to make it more lucrative. But you might find it difficult without proper guidelines and functions. Today in this article, I am sharing with you how to create a probability histogram in Excel. Stay tuned!
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Simple Steps to Create Probability Histogram in Excel
In the following, I have shared 3 simple and quick steps to create a probability histogram in Excel. Follow the instructions below.
Step 1: Making an Informative Dataset
As we all know a dice have 6 faces. To check the probability of all faces we will take random numbers from 1 to 6. Here we also took 0 or null to represent a wrong position of the dice.
- Open a workbook and fill with random numbers.
- Next, just beside the random number column we will create a new column named “Probability” where we will determine the probability for those random numbers.
Read More: How to Make a Histogram in Excel Using Data Analysis
Step 2: Using BINOM.DIST Function to Determine Probability
Without moving here and there, we will calculate the probability using the BINOM.DIST function in Excel. The BINOM.DIST function is a statistical function that returns the binomial distribution probability.
- Choose a cell (C5) and insert the below formula down-
=BINOM.DIST(B5,6,1/6,FALSE)Where,
- We have taken our chosen numbers from cell (B5).
- As we are giving 6 trials, for this we have used 6.
- For taking 6 trials the probability stands as 1/6.
- Next, we choose FALSE as it’s a probability mass function.
- Simply, press ENTER to get the output.
- Hence, drag the “Fill Handle” down to fill the rest of the cells.
- Finally, we have successfully calculated probability in Excel.
Read More: How to Create a Histogram in Excel with Bins (4 Handy Methods)
Similar Readings
- How to Make a Stacked Histogram in Excel (3 Easy Methods)
- What Is Bin Range in Excel Histogram? (Uses & Applications)
- Difference Between Excel Histogram and Bar Graph
- How to Make Histogram Using Analysis ToolPak (with Easy Steps)
Step 3: Plotting a Histogram Graph
In this part, we will plot a histogram graph using the data from the table. Let’s fall into it right now.
- Select cells (B5:C11) and click “Recommended Charts” from the “Insert” option.
- From the newly appeared window, choose a “Column Chart” and press OK.
- In conclusion, we have plotted the graph in our worksheet.
- Thereafter, you can also change the chart design to make it more lucrative. For that, choosing the chart go to the “Quick Layout” option from the “Chart Design” feature.
- Accordingly, change the title by double clicking and then typing “Probability Histogram”.
- Similarly, provide your desired “Axis Titles”.
- In conclusion, we have created the probability histogram within a short time. Simple isn’t it?
Read More: How to Plot Histogram in Excel (5 Easy Ways)
How to Create Binomial Distribution Histogram in Excel
The binomial distribution is a statistical calculation to determine the probability under a given set of parameters. The assumptions are taken in such a way that there will be only one outcome. You can draw a binomial distribution histogram in Excel using some simple tricks.
Suppose we have a dataset of a coin’s “Probability of Success” according to its “Number of Flips”. Now we will calculate the probability with multiple terms to understand the binomial distribution.
In order to understand, we have taken 3 scenarios which are- “Chances of 15 heads”, “Less than or equal 15 heads”, “More than 15 heads”. Let’s calculate the binomial probability using the BINOM.DIST function.
Step 1:
- First, choose a cell (C8) and write the below formula down-
=BINOM.DIST(15,C5,C4,FALSE)Where,
- We have taken 15 as we are calculating the chances of 15 heads.
- Total number of Trials is 30 which is in cell (C5).
- We have a probability of 3.
- As it is a probability mass function thus we selected FALSE.
- Gently, hit ENTER to get the probability.
- Similarly, in cell (C9) put the below formula down-
=BINOM.DIST(15,C5,C4,TRUE)Where,
- For Cumulative we used TRUE as the term is a cumulative distribution function.
- Simply, click ENTER.
- In the same fashion, choose cell (C11) and write the below formula-
=1-BINOM.DIST(15,C5,C4,TRUE)
- Similarly, press the ENTER key to get the result.
Step 2:
- Now, choosing cells (B8:C10) select a “2-D Column” from the “Insert” option.
- Thus a chart will be created in the worksheet.
- You can visit the “Quick Layout” option from the “Chart Design” feature to change the layout of the plotted chart.
- Just write down the ‘Chart Title” and “Axis Title” and your binomial distribution histogram will be ready.
Read More: How to Make a Histogram in Excel with Two Sets of Data (4 Ways)
Conclusion
In this article, I have tried to cover almost all the steps to create a probability histogram in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
