Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Disruption of order of data series in 100% stacked charts
I have built a 100% stacked chart based on 4 yearly series.
My problem is the series are not displayed in the same order in every stack. In one of the stacks, one data point jumps up from the bottom to the center, like so: a a a b d b c b c d c d 2001 2002 2003 (d jumps up in year 2002) I would like the series to be aligned and so to appear in the same order in every stack. Tweaking the series order setting only serves to displace the problem. Thanks for any suggestion. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Disruption of order of data series in 100% stacked charts
Could you also post some typical data?
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sylvain" wrote in message ... I have built a 100% stacked chart based on 4 yearly series. My problem is the series are not displayed in the same order in every stack. In one of the stacks, one data point jumps up from the bottom to the center, like so: a a a b d b c b c d c d 2001 2002 2003 (d jumps up in year 2002) I would like the series to be aligned and so to appear in the same order in every stack. Tweaking the series order setting only serves to displace the problem. Thanks for any suggestion. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Disruption of order of data series in 100% stacked charts
here is the data :
2003 2004 2005 Final Consumption Exp. : Private -22,2% -3,4% 40,7% Final Consumption Exp. : Gov. 15,1% 9,1% 13,2% Gross Fixed Capital Formation 38,7% 12,4% 16,9% Changes in Inventories -14,8% 15,5% -6,8% Balance of Goods and Services 83,3% 66,4% 36,0% thanks for your time and effort Sylvain "Jon Peltier" wrote: Could you also post some typical data? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sylvain" wrote in message ... I have built a 100% stacked chart based on 4 yearly series. My problem is the series are not displayed in the same order in every stack. In one of the stacks, one data point jumps up from the bottom to the center, like so: a a a b d b c b c d c d 2001 2002 2003 (d jumps up in year 2002) I would like the series to be aligned and so to appear in the same order in every stack. Tweaking the series order setting only serves to displace the problem. Thanks for any suggestion. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Disruption of order of data series in 100% stacked charts
Must be a 100% stacked line chart. Do you notice the negative numbers in the
data? Excel is trying to stack them under the preceding values, since they reduce the totals. What is Excel supposed to take as 100%? The net of the positives and negatives? No, it takes the sum of the positives plus the sum of the absolute values of the negatives (do a stacked 100% column to show this clearly). So if my values were +$10 and -$10, the total would be $20! If I could increase my income by my expenditures, I'd be rich. For your data, a 100% stacked chart is going to lead to much more confusion than explanation, and a stacked line chart is probably the worst way to display the data. Hmm, no, maybe a pie... Try a stacked but not 100% column chart. Negatives will display below the axis, and positives above, so you can tell more clearly which items are positive and which are negative. In fact, a clustered column chart is better, because the relative values of each point is not confused by being stacked on other points. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sylvain" wrote in message ... here is the data : 2003 2004 2005 Final Consumption Exp. : Private -22,2% -3,4% 40,7% Final Consumption Exp. : Gov. 15,1% 9,1% 13,2% Gross Fixed Capital Formation 38,7% 12,4% 16,9% Changes in Inventories -14,8% 15,5% -6,8% Balance of Goods and Services 83,3% 66,4% 36,0% thanks for your time and effort Sylvain "Jon Peltier" wrote: Could you also post some typical data? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sylvain" wrote in message ... I have built a 100% stacked chart based on 4 yearly series. My problem is the series are not displayed in the same order in every stack. In one of the stacks, one data point jumps up from the bottom to the center, like so: a a a b d b c b c d c d 2001 2002 2003 (d jumps up in year 2002) I would like the series to be aligned and so to appear in the same order in every stack. Tweaking the series order setting only serves to displace the problem. Thanks for any suggestion. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Disruption of order of data series in 100% stacked charts
Dear John
Thanks for the very helpful suggestions. This was indeed a 100% stacked chart as the too long title of my post mentions. The numbers add up to 100%. I tried a regular stacked chart, but the result is exactly the same. As you say, the negative values end up on the bottom, so the series cannot be aligned unless alla the values are negative or positive. I followed your advice and switched to a clustered column chart. I tried dotted lines as well, but the values become jumbled when they are too close together. Again thanks for your help Sylvain "Jon Peltier" wrote: Must be a 100% stacked line chart. Do you notice the negative numbers in the data? Excel is trying to stack them under the preceding values, since they reduce the totals. What is Excel supposed to take as 100%? The net of the positives and negatives? No, it takes the sum of the positives plus the sum of the absolute values of the negatives (do a stacked 100% column to show this clearly). So if my values were +$10 and -$10, the total would be $20! If I could increase my income by my expenditures, I'd be rich. For your data, a 100% stacked chart is going to lead to much more confusion than explanation, and a stacked line chart is probably the worst way to display the data. Hmm, no, maybe a pie... Try a stacked but not 100% column chart. Negatives will display below the axis, and positives above, so you can tell more clearly which items are positive and which are negative. In fact, a clustered column chart is better, because the relative values of each point is not confused by being stacked on other points. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sylvain" wrote in message ... here is the data : 2003 2004 2005 Final Consumption Exp. : Private -22,2% -3,4% 40,7% Final Consumption Exp. : Gov. 15,1% 9,1% 13,2% Gross Fixed Capital Formation 38,7% 12,4% 16,9% Changes in Inventories -14,8% 15,5% -6,8% Balance of Goods and Services 83,3% 66,4% 36,0% thanks for your time and effort Sylvain "Jon Peltier" wrote: Could you also post some typical data? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sylvain" wrote in message ... I have built a 100% stacked chart based on 4 yearly series. My problem is the series are not displayed in the same order in every stack. In one of the stacks, one data point jumps up from the bottom to the center, like so: a a a b d b c b c d c d 2001 2002 2003 (d jumps up in year 2002) I would like the series to be aligned and so to appear in the same order in every stack. Tweaking the series order setting only serves to displace the problem. Thanks for any suggestion. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting charts to automatically pick data from a series | Charts and Charting in Excel | |||
Add a data series dynamically to a named range? | Charts and Charting in Excel | |||
Chart -- Source Data... -- Series dialog window | Charts and Charting in Excel | |||
Charts Line Types for Multiple Data Series not Printing Properly | Charts and Charting in Excel | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel |