Microsoft Office Tutorials and References
In Depth Information
Plotting z-scores with standard deviation bands
Figure 8-44: This combination chart displays a histogram (columns) along with the normal
distribution curve.
The companion CD-ROM contains another example that applies a scaling
factor to the theoretical values. The theoretical data is multiplied by the
number of data points (2600) times the bin size (6). After this transformation,
both data series can use a single value axis.
Plotting z-scores with standard deviation bands
Figure 8-45 shows an XY chart that plots 1,000 values. Each data point in column
A has been converted to a z-score (column B), and that’s the data actually used in
the chart. A z-score is a way of standardizing data, such that the transformed data
has a mean of 0 and a standard deviation of 1. The midpoint on the vertical axis
corresponds to the average data value, and the gridlines correspond to standard
deviation units.
Formulas calculate the mean and standard deviation of the data, and these cells
are given names ( Mean and SD). The z-score calculation is done with simple
formulas. Cell B2, for example, contains this formula:
=(A2-Mean)/SD
The shaded bands are generated by a bar chart series with eight data points (in
the range D2:E9). The bar chart series uses the value axis at the bottom of the chart,
and each bar has been manually formatted to display a graduated color effect.
Because the chart plots transformed data, the chart can be used for any data set
without modification.
Search JabSto ::




Custom Search