+ Reply to Thread
Results 1 to 5 of 5

Showing average on line graph

  1. #1
    Registered User
    Join Date
    03-19-2004
    Posts
    2

    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
    Trying is the first step towards failure:

    Homer Simpson

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799
    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.

  3. #3
    Registered User
    Join Date
    03-19-2004
    Posts
    2
    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. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799
    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. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    You may wish to have a look at this link as well

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


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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