Books at Amazon.com
Excel Run Chart with Mean and Standard Deviation Lines.
Unlike some commercially available statistics packages, Microsoft Excel does not offer a built in capability to draw lines corresponding to statistical values for a series, such as the mean and the mean ± k standard deviations. The example on this page uses the Add a Horizontal Line to a Column or Line Chart: Error Bar Method to add statistical indicators to a simple run chart.
This example uses 25 values from a normally distributed population:
These values are listed in column B of the worksheet. In D1:F4 of the worksheet, formulas are entered to produce the following values:
Cell D1 is blank, while cell E1 has a text label. The formulas in D2:F4 are:
This simple formula was entered into cell H1 to get a count of the number of points:
A simple run chart was constructed using the data in column B, adding appropriate axis titles.
Range D1:E4 was copied, the chart was selected, and Paste Special was used to add the data as a new series, with series in columns, categories in the first column, and series names in the first row.
This added series is not useful as a line chart. It was selected, and using Chart Type from the Chart menu, it was changed to an XY Scatter series.
Excel automatically added secondary axes to the chart and assigned the XY series to them. The unneeded axes were removed using Chart Options from the Chart menu, and unchecking all secondary chart checkboxes on the Axes tab.
Horizontal (X) Error bars were added to the XY series. The Minus option could have been selected with a value of 25 manually entered, but a more flexible approach was used: after clicking in the Custom (-) entry box, cell H1 was selected, so the box contained a link to cell H1 (=Sheet1!$H$1). The line color of the error bars was changed to red
Data labels were added to the XY series, using the Show Values option. The font color of the labels was changed to red.
The more descriptive labels in F2:F4 were applied to the XY series using Rob Bovey's Chart Labeler, a free add-in available at http://appspro.com. It's easy to install, easy to use, and highly useful. John Walkenbach's Chart Tools, another free add-in from http://j-walk.com, provides similar functionality.
This technique works equally well with a column chart.
Peltier Technical Services, Inc., Copyright © 2013. All rights reserved.