Ms Office Tune Up

When you plot numeric data, it is useful if you can get an indication on the chart where the average value lies. (I use the average in this section, but you can easily apply the techniques I show you here to other values such as the median or mode.)

Probably the easiest way to do this is to create an entirely new data series where each value is the average of the series you want to analyze. For example, in Figure 1, you see a work­sheet that has stock closing prices in column B. I’ve added a new column for the average in column C. Here are the steps you need to follow to insert the average values:

1. Select the entire range into which you want to add the average values (in Figure 1, this is C3:C24).
2. Type (but don’t confirm) the AVERAGE() function formula, making sure that you use absolute cell references for the data you want to average (in Figure 1, we average the range $B$3:$B$24).
3. When you are ready to confirm the formula, press Ctrl+Enter. Excel adds the same formula to all the selected cells.

TIP

You can use a similar technique to plot standard deviation lines, which are lines that represent val­ues that are one standard deviation above and below the average. For the values one standard deviation above the mean, press Ctrl+Enter after the formula, as shown in the following:
=AVERAGE($B$3:$B$24) + STDEV($B$3:$B$24)
The formula for the values one standard deviation below the mean is similar:
=AVERAGE($B$3:$B$24) – STDEV($B$3:$B$24)

When you are ready to add the new series to the chart, here’s a quick method you can use:

1. Select the range, including the header, and then press Ctrl+C to copy it.
2. Select the chart and then press Ctrl+V to paste it. Excel adds the new series, as shown in Figure 2.

Figure 1 To insert the same for­mula into each cell in a range, select the range, type the formula using absolute cell references, and then press Ctrl+Enter.

NOTE

For more control over the pasting of the new series, copy the data, select the chart, choose the Home tab, click the bottom half of the Paste split button, and then click Paste Special. In the Paste Special dialog box that displays, you can see the options that you can use to tell Excel more about your data.

Figure 2 Copy and paste the Average range into the chart to add the new data series.

This technique is handy and useful, but it does clutter your chart with an extra data series. If you prefer to show just the original series, you can use a trick to plot just the average on the vertical (y) axis. For this to work, you must do three things:

• Using the same worksheet, add a formula that calculates the average of the series val­ues.
• Convert your chart to an XY (Scatter) type: Click the chart, choose Design, Change Chart Type, click a layout in the XY (Scatter) category, and then click OK.
• Make sure the minimum value of the horizontal (x) axis is the same as the first category value in your data series. For example, in the stock price worksheet shown in Figure 2, the first category value is August 2, 2007 (the number 39296), so the minimum value for the horizontal axis should be the same date (that is, the value 39296). If you need to change the minimum value for the horizontal axis, click the axis and then choose Layout, Format Selection to display the Format Axis dialog box. In the Axis Options tab, click the Minimum: Fixed option, type the initial category value in the text box, and then click Close.

You can now follow these steps to add a vertical axis marker for the average value:

1. Click the chart and then choose Design, Select Data. Excel displays the Select Data Source dialog box.
2. Click Add to display the Edit Series dialog box.
3. (Optional) Type a Series Name.
4. Click inside the Series X Values range box and then click the first category value in the original data series.
5. Click inside the Series Y Values range box, delete the default value, and then click the cell that contains the average. Figure 3 shows the completed Edit Series dialog box.



Figure 3 Use the Edit Series dialog box to select the first category value in the original series and the cell containing the aver­age value.

6. Click OK to return to the Select Data Source dialog box.
7. Click OK. Excel adds the new data series.
8. Choose the Layout tab, click the new series in the Chart Elements list, and then click Format Selection to display the Format Data Series dialog box.
9. Click the Marker Options tab, click the Built-in option, use the Type list to select a marker style, and then click Close.
10. Choose Layout, Data Labels, and then click where you want the label to display.
11. Click the label once to select it and then click it again to get a cursor inside for editing.
12. Delete the value, type Average, and then click outside the label.
13. Format the label as desired.

Figure 4 shows the stock price chart with the Average value added to the vertical (y) axis.

Figure 4 The Average value in C3 is plotted on the chart’s vertical (y) axis.

Technorati Tags: plotting average, value axis

Popularity: 3% [?]