population pyramids using bar charts with a secondary axis

[sarah o.]

I have a problem that I had given up as unsolvable, but after recently
learning about secondary axes, I've made encouraging progress. However,
I'm stuck on the last step(s), and I'm hoping that someone will have
some suggestions.

My organization has been producing reports that contain population
pyramids. In the past, all of their reports have been printed, so it
didn't matter that in order to get the desired look, they had to use
two charts slightly overlapping each other. However, we are getting to
the point where we would like our charts to be available online for
download. If we save the charts from Excel, of course each pyramid has
two charts that become GIFs, which is non-ideal. Here's an example of
what we have now:

http://epdc.org/CPGraphs/HumanCapitalPyramid/Bolivia_curmale.png
http://epdc.org/CPGraphs/HumanCapitalPyramid/Bolivia_curfemale.png

and here is the progress that I've made:

http://epdc.org/junk/HCPsecondary.png

The big remaining problem is that I need (or would really really like)
a single visible y-axis (I'm calling the horizontal axis y because
that's what Excel does). It's not as simple as making one axis
invisible and changing the way that negative numbers are displayed.
Since we would like to keep the age group labels in the middle of the
pyramid, the y-axis would actually need to have two zeros, if that
makes sense.

Any ideas?

 

[Bernard Liengme]

Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes

 

[sarah o.]

Thanks--it looks like the "Arbitrary Axis Scale" is the bit that I'm
missing. I'll try this out soon.

Sarah

 

[sarah o.]

Ah, I tried to use the Arbitrary Axis technique described here:
http://peltiertech.com/Excel/Charts/ArbitraryAxis.html, but it seems
that XY charts and bar charts cannot be combined. How frustrating,
since bar charts are so much like column charts, which can be combined
with XY charts.

I appreciate the lead, even if it didn't pan out. Any other ideas?

Thanks,
Sarah

 

[Bernard Liengme]

How about using the same technique to make a column tornado chart. Then you
might be able to add a second data series.
best wishes

 

[Andy Pope]

Hi,

If you just need a single image could you not group the charts before
creating an image file?

How often do you have to produce these charts and much does the data
change? It is possible to construct the chart you want using a single
chart and multiple data series. But, and you knew there was a but coming
, The value scale is not dynamic. Even with VBA code it could be
complex. The hard part positioning the data label holding the scale value.

Cheers
Andy

 

[sarah o.]

I wasn't aware that by grouping charts you could save them as a single
image. If I'd known that I probably wouldn't have kept trying to get it
into one chart.

However, I've gotten it to work, and I'm pretty pleased about that. I
took Bernard's suggestion and followed the instructions for creating a
tornado chart, and then added an arbitrary axis using the Line chart
type. It took some fiddling and calculations to get everything scaling
properly, and with nice whole numbers on the axis, but I'm very happy
with the result.

In answer to your second question, I produce these charts very often as
our projection calculations change, and I produce them for 75
countries. So appropriate scaling is important. However, I think I've
figured out how to do it just using calculations in cells--no VBA.

Thanks for all the help,
Sarah

 

[sarah o.]

Just in case anyone else finds it useful, here's the result:
http://epdc.org/junk/testpyramid.xls.

Sarah

 

[Jon Peltier]

Looks like you did manage to combine the stacked bar chart with an XY
series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Just in case anyone else finds it useful, here's the result:
http://epdc.org/junk/testpyramid.xls.

Sarah

 

[Andy Pope]

Hi Sarah,

Thanks for making your final solution available to view. Nice job on
that chart.

By the way as all your bars are on the primary axis you could change the
line series to an xy-scatter series. remove the primary value axis and
display the secondary value axis instead. You can then format the axis
using built-in dialogs.

Cheers
Andy

Just in case anyone else finds it useful, here's the result:
http://epdc.org/junk/testpyramid.xls.

Sarah

 

[Andy Pope]

Disregard my suggestion of using the secondary axis. Obviously you can't
do that as you have 2 origins on the scale, which is why you had to fake
the scale in the first place.

Cheers
Andy

Hi Sarah,

Thanks for making your final solution available to view. Nice job on
that chart.

By the way as all your bars are on the primary axis you could change the
line series to an xy-scatter series. remove the primary value axis and
display the secondary value axis instead. You can then format the axis
using built-in dialogs.

Cheers
Andy