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/HumanCapita...ia_curmale.png
http://epdc.org/CPGraphs/HumanCapita..._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?

Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"sarah o." wrote in message
ups.com...
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/HumanCapita...ia_curmale.png
http://epdc.org/CPGraphs/HumanCapita..._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?

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

Sarah


Bernard Liengme wrote:
Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"sarah o." wrote in message
ups.com...
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/HumanCapita...ia_curmale.png
http://epdc.org/CPGraphs/HumanCapita..._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?

Ah, I tried to use the Arbitrary Axis technique described he
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

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

Sarah


Bernard Liengme wrote:
Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"sarah o." wrote in message
ps.com...
Ah, I tried to use the Arbitrary Axis technique described he
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

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

Sarah


Bernard Liengme wrote:
Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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. wrote:
Ah, I tried to use the Arbitrary Axis technique described he
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

sarah o. wrote:

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

Sarah


Bernard Liengme wrote:

Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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


Andy Pope wrote:
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

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

Sarah

Andy Pope wrote:
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. wrote:
Ah, I tried to use the Arbitrary Axis technique described he
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

sarah o. wrote:

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

Sarah


Bernard Liengme wrote:

Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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/
_______

"sarah o." wrote in message
oups.com...
Just in case anyone else finds it useful, here's the result:
http://epdc.org/junk/testpyramid.xls.

Sarah

Andy Pope wrote:
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. wrote:
Ah, I tried to use the Arbitrary Axis technique described he
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

sarah o. wrote:

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

Sarah


Bernard Liengme wrote:

Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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

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

Sarah

Andy Pope wrote:

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. wrote:

Ah, I tried to use the Arbitrary Axis technique described he
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

sarah o. wrote:


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

Sarah


Bernard Liengme wrote:


Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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

Andy Pope wrote:
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

sarah o. wrote:

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

Sarah

Andy Pope wrote:

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. wrote:

Ah, I tried to use the Arbitrary Axis technique described he
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

sarah o. wrote:


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

Sarah


Bernard Liengme wrote:


Have a look at Jon's example
http://peltiertech.com/Excel/Charts/tornadochart.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info