Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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 |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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 |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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 |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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 |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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 |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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 |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
population pyramids using bar charts with a secondary axis
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make Primary axis and Secondary X-axis have the same scale | Excel Discussion (Misc queries) | |||
Secondary axis on 3d charts | Charts and Charting in Excel | |||
Secondary Axis Match Primary Axis Gridlines | Charts and Charting in Excel | |||
Pivot Table, Line Chart with Secondary Y axis, Page Field selection destroys Y2 axis | Charts and Charting in Excel | |||
bar charts with secondary y axis | Charts and Charting in Excel |