100% Stacked Column Chart with Negative Numbers?

[Kelvin Stott]

I'm trying to create a 100% stacked column chart which includes negative numbers, e.g., 2, 4, 5, -1 and -2

The problem is, the chart scales the total *absolute* value (=14) to 100%, rather than scaling the total *net* value (=8) to 100%.

Showing the negative values below the axis is not a problem. The problem is the overall scale.

Is there any option I can check to plot the chart the *right* way (by scaling to the total net value rather than the total absolute value)?

Or is this just a fixed "feature" (i.e., bug) of Excel?

Thanks for any help.

 

[oldbrewer]

maybe you will find a way forward on jon peltier's website, he is THE charts expert

 

[MuchToLearn]

I believe I encountered the similar problem. I created a Stacked Column chart for set of values including 1 negative value. After carefully examination, I found the lower section of bars where the next value stack on the negative value does not display correctly. Here is extract of set of Data I'm trying to plot: -1.08125, 2.83125, 0.925, 0.9625, 2.83125. The lowest bar should end at 1.75(= -1.08125+ 2.83125), but it end instead closer to 3(I think it's in fact 2.83125). I suspect Excel Stacked Column chart somehow always jump to 0 when there are negative values, instead of using subtotals values..
I purposely create another set of values: -14, 2, 2.5, 1, 0.75, 1.5, 2.6, 3.2, the full column should end up at -0.45, but end up somewhere near 14.

Would be really nice if there is any way around this. e.g. Making the lowest bar correctly ending at 1.75.

 

[oldbrewer]

MuchtoLearn - I sorted your data into ascending order ie -14 to +3.2 and it plots correctly the auto scale is from -20 to +15 but you can manually set that to anything you desire..............

 

[oldbrewer]

I then plotted in your data order and it still plots perfectly - cannot paste the chart to show you - HOW DO I DO THAT - ANYBODY ????

 

[MuchToLearn]

Thanks oldbrewer, I did test it myself.
I placed the values in ascending order temporarily without saving the change, the plot does seem to come out correctly. But unfortunately, the original order is important. The thing is, if all values are positive the plot works correctly, regardless if they’re sorted in ascending order or not.

 

[oldbrewer]

see my post #5 ( I use excel 2000)

 

[MuchToLearn]

Hi,

I just tried with the same set of #s: -14, 2, 2.5, 1, 0.75, 1.5, 2.6, 3.2, unsorted & sorted again. Did not work as I expected. Looks like I was mistaken saying sorting makes the issue disappear (put values in ascending order still makes the plot end up somewhere nearly 14. I expect top end of plot for this set of values to be at 0.45). Did you get 0.45 as top end of plot?
The Excel version I’m using at the moment is 2019.

 

[oldbrewer]

as we use different versions I feel I can help no more, sugggest you seek help from Jon Peltier....