Excel, Average Line on graphs

davetrousers

Hello All, I have a query for Excel 2003.

I have a column of data that I plot in a graph, I also have a cell that shows the average value in the column of data. Is it possible to show this average value as a straight line on the graph without making another column equal the average value (ie draw a straight line at that single value)?

Hope this makes sense.

Thanks for your help

WhiteChristmas

Makes perfect sense, Dave.

Unfortunately, the answer is no, not in Excel. You could write a macro to do it, if it's a lot of data and you do the plot regularly, but otherwise I'd stick to the manual method.

The usual suspects will be along to tell you that OpenOffice has it as a right-click option in a few minutes.

suicidebob

Can't you just hide the column with the average values?

LucianH

I use Excel a lot but cannot see a way of doing it. But it's not a great problem in putting the average in one cell and then simply having a column with an absolute reference to that cell (or a named reference for the cell) - and then you can hide the column as suggested above)

Stompa

Maybe?

http://www.tvmcalcs.com/blog/comments/adding_an_average_line_to_a_chart_using_defined_names/

LucianH

Stompa wrote: »

Maybe?

http://www.tvmcalcs.com/blog/comments/adding_an_average_line_to_a_chart_using_defined_names/

That's brilliant - I didn't think is was possible.

WhiteChristmas

Great find Stompa!

Now to figure out why it doesn't work in the latest versions...

LucianH

WhiteChristmas wrote: »

Now to figure out why it doesn't work in the latest versions...

I've had it working on both 2003 and 2007 SP2.

davetrousers

Thanks Stompa, I worked through the example and I got that to work ok.

The trouble I then had was in relating it to my work! I have that time and date column in Column A then I have my data in many other columns and about 20 graphs. Hmm think I might have to go with the use another column technique! Thanks