# Showing average on line graph

1. ## Showing average on line graph

I have produced a spreadsheet showing daily stats per month.

Column A Days 1-31
Column B September showing the daily stats from 1-31
Column C October showing the daily stats from 1-31

I have a 2-D line graph which shows the daily stats of each month on one graph , (Sept, Oct).

How can I show on the graph , the average figure for each month as a horizontal line

There are a number of Trendline options you can add to a charted series, however none are a horizontal line denoting the actual average of your data series.

For a workaround, can you try this:

1. Insert a column to the right of September, call it Sept Avg
2. Insert a column to the right of October, call it Oct Avg
3. In the first row of Sept Avg put the formula =AVERAGE(\$B\$2:\$B\$32)
-- where column B is the September column
4. Fill that down as far down as column B goes (all values will be the same)
5. In the first row of Oct Avg put the formula =AVERAGE(\$D\$2:\$D\$32)
-- where column D is the October column
6. Fill that down as far down as column D goes (all values will be the same)
7. Hide columns C and E (your average columns)

Select columns B:E and create a new 2D line chart. This will chart 4 data series, two of the lines being horizontal (the average) of each dataset.

3. Thanks pjoaquin, works a treat. I now have my average monthly lines on the graph as well.

Two other questions I would like to ask, in column C the average figure reads 2006.16667. How do I change it to read only two decimal places,eg. 2006.17

Secondly, my line graph is shown on the same worksheet as my data. Can I place the graph onto another worksheet?

Thanks

4. Hi again,
..in column C the average figure reads 2006.16667. How do I change it to read only two decimal places,eg. 2006.17
Select column C, click Format -> Cells -> Number, then set the decimal places to 2. Click OK.
..my line graph is shown on the same worksheet as my data. Can I place the graph onto another worksheet?
You sure can. Right-click near the border of the graph window (the rectangle in which the graph and legend reside), then select Cut. Go to the new sheet, right-click the new sheet and select Paste.

5. You may wish to have a look at this link as well

http://peltiertech.com/Excel/Charts/...orzErrBar.html

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1