ENGI 4421   Probability & Statistics

---

To Create a Histogram in Excel

1. Start Excel.
2. In the event that you do not see the Analysis Toolpak option available at the right end of the 'Data' menu, you need to activate this Add-in:
   Under 'File' click on 'Options'.
   In the new menu select 'Add-ins', then click the button 'Go...'
   In the new menu check the boxes for 'Analysis Toolpak' and 'Analysis Toolpak - VBA'
   then click 'OK'.
3. Open the text/data file containing the data you wish to analyze.   The data should all be pasted in one column.
4. Determine the first and last bin values.   Determine the number of bins you want to use.   Place the bin values in ascending order in an adjacent column.
5. Under the Data menu, choose Data Analysis.   Then choose Histogram from the list.
6. Select the data for the Input Range.
7. Select the bin values for the Bin Range;
   (optional, Excel can create them automatically, although Excel’s choice can be ugly!).
8. Check the Chart Output box.
9. Click OK.
10. Excel then creates the histogram.   However, there are spaces remaining between the bars.   Right click on the bars and choose Format Data Series.   Under the Options tab, set the gap spacing to zero.

Note that this is not a true histogram – it is actually a bar chart.
The vertical axis of a bar chart is frequency, but for a histogram should be density.
This distinction doesn’t affect the shape of the diagram when the bins are all of equal width.
However the distinction definitely matters when the bins are not all of equal width.

To Create a Normal Probability Plot in Excel

1. Start Excel.
2. Open the text/data file containing the data you wish to analyze.   The data should all be in one column.
3. Ensure that the Analysis Toolpak is active (as above).
4. Create a new column of data adjacent to the original data.   The new column can contain any values as long as it has the same number of entries as the original data.
5. Under the Data menu, choose Data Analysis, and then Regression.   Follow the directions given in the dialog box.
6. Enter values for the Input Y Range.   The Input Y Range contains the data for which you want the probability plot.
7. Enter values for the Input X Range.   These are irrelevant in this case.   We are only interested in the Normal Probability Plot option.
8. Check the Normal Probability Plots option.
9. Click OK.   Excel creates a normal probability plot.
   However, Excel transposes the x and y axes
   and uses a linear scale for the sample percentiles.
10. Click on the newly created chart.   Under Chart in the menu, choose Add Trendline.   Under the Type tab, choose the linear option.

If you have the add-in 'XLSTAT' for Excel (from www.xlstat.com), then you can also generate probability plots for non-normal distributions.
Start XLSTAT from its menu.
Click on XLSTAT again, pull down the menu for 'Discover, explain and predict',
pull down the menu for 'Visualizing data' and click on 'Probability plot'.
The 'Options' tab allows for non-normal populations.
The 'Charts' tab allows confidence intervals to be plotted also.

Histograms and Probability Plots in Minitab

The creation in Minitab of histograms and normal probability plots is covered in the Minitab tutorials.   Minitab allows much better control over these plots.