+ Reply to Thread
Results 1 to 7 of 7

Adding an average horizontal line

  1. #1
    Registered User
    Join Date
    09-29-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    91

    Adding an average horizontal line

    I have created charts for data involving the number of parts that is bring bought every month. I have 'months' along the x-axis and 'number of parts' along the y-axis.

    I want to create an average line to show if for a particular month, the number of parts is above the average number of parts per month.

    Is this possible?

  2. #2
    Registered User
    Join Date
    02-06-2008
    Location
    Cheshire, UK
    Posts
    42

    Re: Adding an average horizontal line

    Hi booo

    Yes it can be done quite easily.

    One method is to add an additional series (Name it Avg) with the series values being the Average values and plot it the same way as any of the parts values.

    Hope the above helps.

  3. #3
    Registered User
    Join Date
    09-29-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Adding an average horizontal line

    Thanks NoCanDo,

    But the thing is I am a noob to this and I am not able to understand what you mean when you say add a series.

    It would be very helpful, if you could provide a step by step methodology of the process you are talking about.

    Many thanks,
    Booo

  4. #4
    Registered User
    Join Date
    09-29-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Adding an average horizontal line

    Just an additional info:

    The chart that I have takes in data from a pivot table.

  5. #5
    Registered User
    Join Date
    02-06-2008
    Location
    Cheshire, UK
    Posts
    42

    Re: Adding an average horizontal line

    Hi booo

    Don't know if this will help, but I have attached a simple spreadsheet which charts the relevant table below:

    Jan Feb Mar Apr
    Part-A 30 20 30 40
    Part-B 20 30 35 35
    Part-C 30 25 25 30
    Average 30 30 30 30

    I've used an actual value for the Average rather than calculating one. If you right-click the chart and select 'Source Data' you will notice there are 4 'series' one for each Part and one for the Average.

    The fact you are using a pivot table is immaterial really as the pivot table is only summarising the data set.

    Hope this proves useful.

    Kind regards

    NoCanDo
    Attached Files Attached Files
    Last edited by NoCanDo; 10-18-2010 at 04:00 PM. Reason: Forgot the attachment - apologies

  6. #6
    Registered User
    Join Date
    09-29-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Adding an average horizontal line

    NocanDo

    I was looking at more like having a column chart for the data and then an average line which pass across that chart indicating whether the bars are below the avg or above the avg.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,126

    Re: Adding an average horizontal line

    You will find a whole bunch of chart type and datum line examples here,
    http://www.andypope.info/charts/averageline.htm
    Cheers
    Andy
    www.andypope.info

+ 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