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.

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)

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