Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to draw out a bar chart backed by a 3-dimension database
Hello everyone:
I've a problem: I'm drawing a population pyramid bolstered by a 3-dimensional database, with the population as the only figure to display, and break up by gender, age group, and record year; e.g. Year Gender Agegroup Population 2000 Male 10-14 200000 1999 Female 5-9 1340000 I've got a total 2 years in year break up, 2 gender types, male & female, and about 10 age groups; What I wanna to do is to generate a bar chart, with age break displayed on the vertical axis, population displayed on the horizontal axis, and break up by gender, shown on alternative quadrants of horizontal axis devided by vertical null point (treat female figure as negative), and stack the year data on the vertical axis, show an example: ++++++|++++++++ Year 2000,Age group 15-19 *****|****** Year 2005,Age group 15-19 +++++++++|++++++++++ Year 2000,Age group 10-14 *******|******* Year 2005,Age group 10-14 ++++++|+++++++ Year 2000,Age group 5-9 *******|********* Year 2005,Age group 5-9 Female _____________|_______________Male Where * and + represents different years' break, left and right quadrants represent gender break, and vertical axis represent age break, the length of + and * represent the population figure; I've been doing this graph using a statistical software and I would like to move it into excel file to link the datapoints to other files, enabling this chart manipulatable by altering the figures to obtain dynamic effects, how can this be achieved? Thanks & appreciates! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to draw out a bar chart backed by a 3-dimension database
It is all explained he
http://peltiertech.com/Excel/Charts/tornadochart.html best wishes -- Bernard Liengme http://people.stfx.ca/bliengme "Aaron" wrote in message ... Hello everyone: I've a problem: I'm drawing a population pyramid bolstered by a 3-dimensional database, with the population as the only figure to display, and break up by gender, age group, and record year; e.g. Year Gender Agegroup Population 2000 Male 10-14 200000 1999 Female 5-9 1340000 I've got a total 2 years in year break up, 2 gender types, male & female, and about 10 age groups; What I wanna to do is to generate a bar chart, with age break displayed on the vertical axis, population displayed on the horizontal axis, and break up by gender, shown on alternative quadrants of horizontal axis devided by vertical null point (treat female figure as negative), and stack the year data on the vertical axis, show an example: ++++++|++++++++ Year 2000,Age group 15-19 *****|****** Year 2005,Age group 15-19 +++++++++|++++++++++ Year 2000,Age group 10-14 *******|******* Year 2005,Age group 10-14 ++++++|+++++++ Year 2000,Age group 5-9 *******|********* Year 2005,Age group 5-9 Female _____________|_______________Male Where * and + represents different years' break, left and right quadrants represent gender break, and vertical axis represent age break, the length of + and * represent the population figure; I've been doing this graph using a statistical software and I would like to move it into excel file to link the datapoints to other files, enabling this chart manipulatable by altering the figures to obtain dynamic effects, how can this be achieved? Thanks & appreciates! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to draw out a bar chart backed by a 3-dimension database
Bernard has pointed you in the direction of creating a tornado chart by hand.
But, it doesn't quite address your requirement. What you need to do is the following: Extract the data to Excel (use either a PivotTable (Data | PivotTable or PivotChart Report...) or MS Query (Data | Import External Data New data query...). Now, add a new column to this extracted data with a formula to convert the population to -ve if the gender is F. [This intermediate step would not be necessary if one could use a PT Calculated Field that with a formula like =if(gender="f",-popn, popn) but I cannot get a Calculated Field with that formula to work.] Now, use this table as the source for a PivotChart. The age group is the first row field, the year the 2nd. The Gender is the only column field, and the new column is the data field. Convert the chart to a Bar chart, and format the series to have a 100% overlap and adjust the gap width to taste. -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 2007 double-click to format may not work; right click and select from the menu "Aaron" wrote: Hello everyone: I've a problem: I'm drawing a population pyramid bolstered by a 3-dimensional database, with the population as the only figure to display, and break up by gender, age group, and record year; e.g. Year Gender Agegroup Population 2000 Male 10-14 200000 1999 Female 5-9 1340000 I've got a total 2 years in year break up, 2 gender types, male & female, and about 10 age groups; What I wanna to do is to generate a bar chart, with age break displayed on the vertical axis, population displayed on the horizontal axis, and break up by gender, shown on alternative quadrants of horizontal axis devided by vertical null point (treat female figure as negative), and stack the year data on the vertical axis, show an example: ++++++|++++++++ Year 2000,Age group 15-19 *****|****** Year 2005,Age group 15-19 +++++++++|++++++++++ Year 2000,Age group 10-14 *******|******* Year 2005,Age group 10-14 ++++++|+++++++ Year 2000,Age group 5-9 *******|********* Year 2005,Age group 5-9 Female _____________|_______________Male Where * and + represents different years' break, left and right quadrants represent gender break, and vertical axis represent age break, the length of + and * represent the population figure; I've been doing this graph using a statistical software and I would like to move it into excel file to link the datapoints to other files, enabling this chart manipulatable by altering the figures to obtain dynamic effects, how can this be achieved? Thanks & appreciates! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to draw out a bar chart backed by a 3-dimension database
Thanks so much!
Got your idea! "Tushar Mehta" wrote: Bernard has pointed you in the direction of creating a tornado chart by hand. But, it doesn't quite address your requirement. What you need to do is the following: Extract the data to Excel (use either a PivotTable (Data | PivotTable or PivotChart Report...) or MS Query (Data | Import External Data New data query...). Now, add a new column to this extracted data with a formula to convert the population to -ve if the gender is F. [This intermediate step would not be necessary if one could use a PT Calculated Field that with a formula like =if(gender="f",-popn, popn) but I cannot get a Calculated Field with that formula to work.] Now, use this table as the source for a PivotChart. The age group is the first row field, the year the 2nd. The Gender is the only column field, and the new column is the data field. Convert the chart to a Bar chart, and format the series to have a 100% overlap and adjust the gap width to taste. -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 2007 double-click to format may not work; right click and select from the menu "Aaron" wrote: Hello everyone: I've a problem: I'm drawing a population pyramid bolstered by a 3-dimensional database, with the population as the only figure to display, and break up by gender, age group, and record year; e.g. Year Gender Agegroup Population 2000 Male 10-14 200000 1999 Female 5-9 1340000 I've got a total 2 years in year break up, 2 gender types, male & female, and about 10 age groups; What I wanna to do is to generate a bar chart, with age break displayed on the vertical axis, population displayed on the horizontal axis, and break up by gender, shown on alternative quadrants of horizontal axis devided by vertical null point (treat female figure as negative), and stack the year data on the vertical axis, show an example: ++++++|++++++++ Year 2000,Age group 15-19 *****|****** Year 2005,Age group 15-19 +++++++++|++++++++++ Year 2000,Age group 10-14 *******|******* Year 2005,Age group 10-14 ++++++|+++++++ Year 2000,Age group 5-9 *******|********* Year 2005,Age group 5-9 Female _____________|_______________Male Where * and + represents different years' break, left and right quadrants represent gender break, and vertical axis represent age break, the length of + and * represent the population figure; I've been doing this graph using a statistical software and I would like to move it into excel file to link the datapoints to other files, enabling this chart manipulatable by altering the figures to obtain dynamic effects, how can this be achieved? Thanks & appreciates! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to draw a line on a chart, and have it move with the chart? | Charts and Charting in Excel | |||
How do I draw chart like car counter? | Charts and Charting in Excel | |||
Does anyone have a 12-dimension spider chart template in Visio? | Charts and Charting in Excel | |||
Can you draw more than one pie in a chart? | Charts and Charting in Excel | |||
how do i draw a chart with excel | Excel Discussion (Misc queries) |