ENGR 3423   Probability & Statistics

To Create a Histogram in Excel

  1. Start Excel.
  2. Under the Tools option in the menu, choose Add-Ins.   Check Analysis Toolpak.   This loads the data analysis toolpak.
  3. Open the text/data file containing the data you wish to analyze.   The data should all be 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 Tools 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).
  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.

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. Load the Analysis Toolpak as follows:
    Under the Tools menu, choose Add-ins.   From the list select Analysis Toolpak.   This will allow you to perform many statistical functions within Excel.
  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 Tools 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.
  10. Click on the newly created chart.   Under Chart in the menu, choose Add Trendline.   Under the Type tab, choose the linear option.   Under the Options tab, check Display R-squared value on chart.   The square root of the R-squared value is the correlation value.

To Create a Histogram in Minitab

[See also the first Minitab tutorial.]

  1. Start Minitab.
  2. Under the File option in the menu, choose Import ASCII Data.   Load the given data into column C1.
  3. Under the Graph option in the menu, choose Histogram.   Enter the location of the data in the Variables box.   Enter the midpoints of the first and last bin, and the bin width.   This produces a histogram.
  4. Under the Edit option in the menu, choose Set Patterned Data.   Generate 60 data points ranging from the first bin to the last bin.
  5. Under the Calculation option in the menu, choose Probability Distributions.   Generate the appropriate number of the data points based on the mean and standard deviation of the given data.
  6. Then, under the Graph option in the menu, choose Scatter Plot.   Plot the distribution curve for the data using the input data and the associated probabilities.

      [Return to your previous page]

Created 2001 10 30 and last modified 2003 08 29 by Dr. G.H. George,
from information supplied by Paul Stuckey.