Buzz
Leverage the prowess of Excel's computational functions to streamline your tasks. The Histogram function surpasses others, making probability distribution charting a breeze in Excel.
Build probability distribution charts using Histogram in Excel's Analysis Toolpak for 2007 and later versions. Follow this guide to master the art of using Histogram to chart probability distributions in Excel 2007.
Unlock the Magic of Histograms: Creating Probability Distribution Charts in Excel
Step 1: Install Analysis Toolpak in Excel by following these steps
First, click on Office Button and select Excel Options. A window for Excel Options will appear. Click on the Add-In tab and click the “Go” button.
In the Add-In window, check the box for Analysis Toolpak and press OK. The Configuration Process window will appear.
After completing the installation, navigate to the Data tab, and you'll find Analysis Toolpak.
Step 2: Input values for the variables you want to plot in Excel.
Step 3: Divide the distribution into classes. The minimum number of classes is determined by the formula: 1 + 32logn (where n is the initial number of variable values).
Step 4: In the Data tab, select Data Analysis ->Histogram and click OK. A Histogram window will appear, displaying:
- Input Range: Area containing values of the variable to be plotted
- Bin Range: Area dividing into classes
- Output Range: Starting point of the displayed result
- New worksheet Ply: Display the result on a new sheet
- New workbook: Display the result in a new Book
- Pareto (sorted histogram): Display the result sorted by decreasing frequency
- Cumulative Percentage: Cumulative percentage chart
- Chart Output: Probability distribution chart.
Consider an example: In reality, the exact volume of rock and soil is never precise. Therefore, probability distribution charts are used to determine a unit volume of rock and soil.
To determine a specific unit volume of rock and soil, create a probability distribution chart for 50 test samples.
Step 1: Enter the data for the 50 test samples into an Excel sheet in the data range B2:B51
Step 2: Divide the distribution into classes. In this example, it's recommended to split it into 9 classes (range D2:D10)
As the minimum number of classes calculated is approximately 6.64 (using the formula 1+3.32log50).
Step 3: In the Data tab, select Data Analysis -> Histogram -> OK
Step 4: The Histogram window appears. Choose the data as shown in the figure:
Step 5: Press OK to obtain the result:
Thus, based on the probability distribution chart, a unit volume of rock and soil fluctuates around the value of 15.78 according to the given variable values. Drawing this distribution chart helps accounting professionals reduce dependency on complex accounting software.
This article has guided you on using Histograms to create probability distribution charts in Excel. This method is applicable to Office 2003, Office 2007, Office 2010, and Office 2013. For Office 2010, you are likely already familiar.
In the latest version of Microsoft, you can perform the same steps to draw probability distribution charts in Office 2016. Additionally, you can explore how to draw charts and graphs in Excel on Mytour.
