Overlay 4 years of data as a line on 4 years of columns for several x category labels
 

Dear Charting aficianados,

The tutorial at
http://peltiertech.com/Excel/Charts/...olAndLine.html is the
closest example I can find to a charting conundrum I am facing, though
what I am hoping to achieve is still a little different.

I've made a mock up of what I'm hoping to achieve - though I have had
to use drawing objects for the line series, to overlay on the columns -
I'm sure there is a way to get excel to do this - do you think there
is? (sheet named mock-up)

A workbook with mocuk-up and my other charting attmempts can be found:
http://www.savefile.com/files/2690840

The graph is meant to be a chart of four-years of data in columns
clustered for five different individual entities (in this case
buildings) what I want to do is overlay their peers' performance as a
line over the top of the individual properties performance.

Failing this I had thought about adding the peers' performance as x,y
and customising the secondary x-axis so that the data points of the
lines are in line with the columns. Another idea was to make two
graphs, the column in one graph and then the line in the second, and as
long as the layout is the same and the background transparent I thought
it might be possible to arrange them and group them in Word exactly on
top of each other??? Bit of a work-around!

Using actual data for both series, the best I can manage at the moment
is the chart on sheet OwnAndComparatorAdjCols in the attached workbook
- but I think you will agree it is visually not too great.

If you were able to say if it is possible to achieve the chart rather
hopefully mocked up using floating lines on top of real columns - I
would be very grateful if you could point me to any resources that
describe this.

Yours gratefully,
Matthew Tippett

Overlay 4 years of data as a line on 4 years of columns for several x category labels
 

HI eMTee,

One way of doing this is with a little bit of VBA. See a sample at
http://www.edferrero.com/charting.aspx - look for 'Columns and Lines'

Very quick-and-dirty sample I'm afraid, bit rushed for time at the moment.

Ed Ferrero
http://www.edferrero.com


Dear Charting aficianados,

The tutorial at
http://peltiertech.com/Excel/Charts/...olAndLine.html is the
closest example I can find to a charting conundrum I am facing, though
what I am hoping to achieve is still a little different.

I've made a mock up of what I'm hoping to achieve - though I have had
to use drawing objects for the line series, to overlay on the columns -
I'm sure there is a way to get excel to do this - do you think there
is? (sheet named mock-up)

A workbook with mocuk-up and my other charting attmempts can be found:
http://www.savefile.com/files/2690840

The graph is meant to be a chart of four-years of data in columns
clustered for five different individual entities (in this case
buildings) what I want to do is overlay their peers' performance as a
line over the top of the individual properties performance.

Failing this I had thought about adding the peers' performance as x,y
and customising the secondary x-axis so that the data points of the
lines are in line with the columns. Another idea was to make two
graphs, the column in one graph and then the line in the second, and as
long as the layout is the same and the background transparent I thought
it might be possible to arrange them and group them in Word exactly on
top of each other??? Bit of a work-around!

Using actual data for both series, the best I can manage at the moment
is the chart on sheet OwnAndComparatorAdjCols in the attached workbook
- but I think you will agree it is visually not too great.

If you were able to say if it is possible to achieve the chart rather
hopefully mocked up using floating lines on top of real columns - I
would be very grateful if you could point me to any resources that
describe this.

Yours gratefully,
Matthew Tippett