Horizontal Stack Bar Chart with Scatter Values Poblems

[NoCanDo]

Hi folks

I’m using Excel 2003 and I’m trying to generate a combined HORIZONTAL stacked bar chart with a scatter chart.

I have been able to do this with a VERTICAL stacked bar chart with a scatter chart (thanks to Andy Pope - took his idea from another post and used it) but Excel states some chart types cannot be combined with other chart types and am unable to create the horizontal version I need.

The data below should be First, Second and Third "quartiles" in a 0 to 100 scale horizontal stacked bar chart with the Previous and Current values (possibly using a secondary 0 to 100 scale) as a scatter chart on the relevant stacked bar


First Second Third Previous Current
A 76.5 80 83.5 50 85.7
B 77.5 99 120.5 19.8 48.8
C 55.75 62.5 69.25 77.8 96.3



Does anyone know of a solution, even if it means using a macro and VBA to generate the chart?

Here's hopeing someone out there can help ...

NoCanDo

[Andy Pope]

You can mix stacked bar and xy-scatter, assuming not the 3d version of chart.

I'm not sure which series you want plotted as lines so assumed Previous/Current. You will see I had to calculate Current's X values in order to mimic the stacking of values.

You need to provide the Y values to align the lines with the bar centers.

The re-adjust the x/y values for the series as the category labels will be used by default, which results in x values of 1,2,3.

[NoCanDo]

Hi Andy

Many thanks for the reply. Will check out how it all works, as I had a similar idea myself and couldn't get it to work correctly, but at first glance your solution fits the bill.

Will check to make sure it works as I hope with a 100% Stacked Bar - which I think it does.

Many thanks for a speedy response.

NoCanDo

[NoCanDo]

Hi Andy

Works a treat - thank you very much!

I've amended the formats slightly to suit my needs, but I have a favour to ask, how do I add a median value on the 100% stacked bar at say 50% for example.

I've attached my updated sheet - just in case.

Hope you can help me just one little bit more.

Here's hoping .....

NoCanDo

[Andy Pope]

Add another xy-scatter series with a X value of 50 for each Y value.

I have enabled the secondary x axis so you can see that it has had it's scale fixed at 100, in order to align with the 100% scale

[NoCanDo]

Hi Andy

Excellent - I must be on the right wavelength as that's exactly what I did - except it took me loads longer!!

I've uploaded my solution in case anyone needs it.

Many thanks Andy.

Kind regards

NoCanDo