+ Reply to Thread
Results 1 to 4 of 4

Dynamic Range Column Chart w/ Dynamic Avg Line (Excel 2007)

  1. #1
    Registered User
    Join Date
    03-09-2009
    Location
    Not Where I Should...
    MS-Off Ver
    2007
    Posts
    5

    Dynamic Range Column Chart w/ Dynamic Avg Line (Excel 2007)

    Howdy All!

    I've been working on a project in Access. It works fine, and exports tables to Excel. The number of records varies each time the Access file exports. Therefore, I've created a chart w/ dynamic ranges.

    The chart works fine....It grows and shrinks accordingly. I'd like to include a line to show the average...dynamically. I found an equation that works w/ dynamic ranges. This function works well (found in cell C2).

    However, I can't get that info turned into a line on my column chart. When I add that value into the chart, and change it to a line chart, I end up w/ one single data point. Understandably, because that's all there is.

    Therein lies the question: How do I apply that value dynamically for each record so it shows as a line on my chart?

    I've tried using trend lines, but they don't actually average...so they're not very helpful.

    Attached is an example. Feel free to play w/ the data (add or remove as you will). The column chart changes accordingly, as does the average value in C2. But...I'd still like to get the average value to show as a line...

    Thanks for the help.
    Attached Files Attached Files
    Last edited by BrokenBiker; 03-11-2009 at 06:37 PM.

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

    Re: Dynamic Range Column Chart w/ Dynamic Avg Line (Excel 2007)

    Add a new named range,

    AVGPERCQUAL: =AVERAGE(PercQual)*(ROW(PercQual)^0)

    and change the series formula for the average line series to

    =SERIES("Average",ColumnChartAverageLline.xlsx!FullNames,ColumnChartAverageLline.xlsx!AvgPercQual,2)
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    03-09-2009
    Location
    Not Where I Should...
    MS-Off Ver
    2007
    Posts
    5

    Thumbs up Re: Dynamic Range Column Chart w/ Dynamic Avg Line (Excel 2007)

    Outstanding! Working w/ named ranges and formulas in charts is new business to me. Thanks for the help! It works like a champ.

    I kept the original "line" (the single data point) and added a label to it to reflect the value. I used a second average line w/ your equation to create the actual line in the chart. I just formatted them similarly so they look like one line.

    I attached an updated example for others to see.

    I had conducted many searches to find an answer to this. Evidently, it was fairly well known how to create an "average line" w/ a column/bar chart in earlier versions of Excel. However, it seems that getting it to work w/ 2007 is a pretty tough nut to crack. In fact, I found several places that said you can do it in 2003, but 2007 doesn't support it.

    Well Andy, looks like you proved 'em wrong!

    Major kudos to Andy!




    P.S. Keywords added--dynamic, named ranges, average, column, chart, 2007
    Attached Files Attached Files
    Last edited by BrokenBiker; 03-10-2009 at 09:02 AM.
    Never ride faster than your guardian angel can fly

  4. #4
    Registered User
    Join Date
    10-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Dynamic Range Column Chart w/ Dynamic Avg Line (Excel 2007)

    I am not able to do this , please help on how to add a new average line

+ 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