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 8.2, you see a worksheet 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 8.2, 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 8.2, 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.

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 8.3.

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 values.
• 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 8.3, 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 8.4 shows the completed Edit Series dialog box.
   
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.

• Choosing the Best Tennis Shoes Tennis shoes, which also referred to gym shoes or athletic shoes, are designed primarily to handle sporting activities and other...
• 1 simple tweak to double PC performance So I've made a pretty big promise with that headline but believe me I plan to deliver with this one...
• Stamp Collecting Accessories to Make Your Hobby More Enjoyable [/caption]If you are taking more interest in your stamp collection chances are you are looking for accessories to keep each...
• 5 Tips and Tricks for your iPhone [/caption]I’m not really a read the manual kind of guy.  It has nothing to do with being a typical man...
• DVD Review: Golf Made Simple - Improve Your Golf Swing Indoors "Golf Made Simple - Improve Your Golf Swing Indoors" is a Golf Made Easy DVD that allows you to understand...