If you use the Histogram and Chart Wizard, in some cases you may find that the buckets are too broad, too narrow, or otherwise imprecise. Or, you may just want to adjust the bucket sizes.
This happens because the Histogram functions – the functions specifically used to create the histogram tables – are designed to rebalance automatically. We don’t know beforehand what the range of the data will be; and we don’t know that you won’t dramatically change the range later, requiring a different set of buckets.
If you do know the range of data, however, you can use another set of functions to create a histogram. This will give you a great deal of control over the size and spacing of the buckets.
The function SimulationInterval returns the portion of a simulation – as a percentage – that falls within some bounds.
So to create a histogram, start by creating a table of bins. Then use the SimulationInterval function to find the portion of the simulation that falls in that range:
SimulationInterval returns a percentage. The next step is to turn that into a count, for a traditional histogram. To make sure the count is correct for any simulation, instead of using a number you can use the function SimulationTrials. This returns the number of trials in the last simulation.
You now have a histogram table. Use the standard Excel column chart tools to create the chart.
If your data consists of discrete values, see this note about the SimulationInterval function.
Tip: to turn a column chart into a histogram, right-click on one of the columns and select Format Data Series; then set the Gap Width to zero.