Showing average on line graph

[Gadgetman]

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

[Paul]

Hi Gadgetman,

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.

[Gadgetman]

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

[Paul]

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.

[oldchippy]

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

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