Business Technology Article 06 - Histograms (Pareto) in Excel
When the two Monte Carlo Simulation books were written, there was no facility in Excel to automatically produce Histograms. The Analysis Toolpak had a procedure which was strictly manual. You had to prepare your own bins from which cumulative percentages were generated.
By now, those who have used the books would have surely realized that most workouts in the books use a lengthy procedure to:
- Define the number of bins and their size
- Create a frequency table from the results into the bins
- Generate the cumulative percentages of the frequency table
- Sort the bins by descending frequency
- Plot the frequency count and the cumulative percentages (the Pareto Chart)
Even though I had simplified the above by using COUNTIFS() and a quick way for calculating the CUM % (using SUM($A$2:A2)), the procedure was still lengthy.
I tried to develop a VBA module to automate the procedure but found it cumbersome and restricted.
Microsoft came to the rescue (without my even asking them).
Starting with Excel 2016, we get a well-hidden chart called HISTOGRAM. It replaces the above procedure.
- You simply select the results column and plot the chart.
- It decides on its own bin count and size and plots the Pareto chart.
- You can then vary the number of bins.
I used Workout 18 from Part 2 as an example of the Histogram chart.
be downloaded if you Click Here. The workout is:
18. Interviewing Passengers (GEOMETRIC)
In the book, we went through several steps to develop the Pareto chart.
The following steps show you how to use the Histogram chart instead of the above lengthy procedure.
Step 1: using the downloaded workbook, create a new
worksheet in the same workbook. Call it Histogram.
In A1, enter “Time”.
Step 2: copy the results of the simulation from the range B8:B1007 in the Model worksheet. These represent the simulated total time for interviews.
Paste the range AS VALUES into A2:A1001.
Step 3: select the range A1:A1001 and click the menu INSERT / RECOMMENDED CHARTS and select ALL CHARTS:
1) Select the ALL Charts tab
2) Click on the Histogram chart type (left panel)
3) Select the Pareto Chart
Step 4: Excel will generate a Histogram with the timing of the interviews (blue bars) along with an orange line showing the cumulative percentage of the counts:
Excel will select its own number of bins and sort them by descending values.
Step 5: select the horizontal axis and right click on it. Select FORMAT AXIS:
Update the number of bins as you wish, say 10.
Next change the number format to have 0 decimals. This is what you will get:
This is a much faster method than the old manual technique of developing your own bins, creating a histogram and generating cumulative percentages.
To comment, no need to register or log in with Disqus. Enter any name and a check box will enable you to comment as a guest. Your email will be required but Disqus will not show it in the thread. I am committed to your privacy and confidentiality and will not use it or distribute it. Click Here to review guest privacy and commenting policies in Disqus.