Disruption of order of data series in 100% stacked charts

[Guest]

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.

 

[Jon Peltier]

Could you also post some typical data?

- Jon

 

[Guest]

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]

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

 

[Guest]

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