Adding average lines to column plot

[vbacoder]

Hi everyone,

I have a column chart in which the x-axis is split into categories, say apple, pair, grape. For each category, I have a count for each quarter in the year. So the chart is really a clustered column chart showing columns for Q1, Q2, Q3, Q4 in each of apple, pair and grape.

I would now like to add horizontal average lines that span the full horizontal range of each group of columns - one for apples, spanning all 4 columns, another for pair, again spanning all 4 columns under this category, and a third horizontal line for grape.

Please can anyone suggest a good way to do this. I have tried using dash points, but these don't span the full width of the 4 columns in each category.

Thanks,

vcoder

 

[pgc01]

Hi

Jon Peltier explains here how to add a horizontal line to a column chart:

http://peltiertech.com/Excel/Charts/AddLineHorzSeries.html

 

[vbacoder]

Thanks for your reply. I've seen this site, and the instructions for adding a line to a chart are not quite what I want to achieve. I would like to produce average lines for each cluster of columns. Ordinarily this is easy if the x-axis is numerical, but when the axis is categorised by names, this is tricky to achieve.

 

[pgc01]

Hi

You can easily get the average of each cluster from the source values.

For ex., assuming the average of the values in the first cluster is 2, add a series, scatter type, with 2 points (0.6,2) and (1.4,2)

This would add an average line accross the first cluster (assuming you did not change the defauls).

 

[vbacoder]

Yes, thanks. If done on a secondary axis, this works. I used error bars insead of 2 points per line.

vcoder

 

[pgc01]

Yes, thanks. If done on a secondary axis, this works. I used error bars insead of 2 points per line.

vcoder

I'm glad it's working for you, but in my test I created the second series also in the primary axis.