100% Stacked Column

[Doria/Warris]

Hi,

I've been using this type of chart many times to create a sort of "filling
the glass" effect.
Now, I've a situation that I'd like to solve.
Let's take this example:

TARGET
A1 = 1000
A2 = 2000
A3 = 3000

ACTUAL
B1 = -200
B2 = 1600
B3 = 1800

DIFFERENCE = TARGET - ACTUAL
C1 = 800
C2 = 400
C3 = 1200

If I input this data in the 100% stacked column chart, Series in Column, the
Targets on the value axis go from 0% to 100%, basically in our examples what
the chart shows is:

category axis 1
from 0% to 80% = 800 Difference
from 0% to -20% = 200 Actual

category axis 2
from 90% to 100% = 1600 Difference
from 0% to 90% = 400 Actual

category axis 3
from 60% to 100% = 1200 Difference
from 0% to 60% = 1800 Actual

Now, my problem is that to create a "filling" effect, the situation in
category 2 & 3 are perfect because the sum of the act + difference = TARGET
100% and it is visible on the value axis.
In category 1 I have instead a problem because it looks like the TARGET is
800 (80%) because of the negative value - 200, but in reality it is 1000.
I realise that this is logical because it is maths, but maybe you have an
alternative and best way to show this picture??

I do apologize for being so long, but I'd rather prefer to be clear.

Thank you
Alex

 

[Brad White]

Hi,

I've been using this type of chart many times to create a sort of "filling
the glass" effect.
Now, I've a situation that I'd like to solve.
Let's take this example:

TARGET
A1 = 1000

ACTUAL
B1 = -200

DIFFERENCE = TARGET - ACTUAL
C1 = 800

If I input this data in the 100% stacked column chart, Series in Column, the
Targets on the value axis go from 0% to 100%, basically in our examples what
the chart shows is:

category axis 1
from 0% to 80% = 800 Difference
from 0% to -20% = 200 Actual

It think your problem is that the difference is NOT 800, but 1200.
Does that fix it?

Brad.

 

[Guest]

Brad,

Yes, you are right, the difference is obviously 1200 but this doesn't change the visual aspect of the chart.
My question is really to understand if there's a better way to represent this situation, maybe another type of chart or so on.
As it is now, at a first glance, it looks that the Target is 80% and this is misleading even if it is right from the calculation point of view.
Therefore, I was thinking that there might be an alternative way to represent this situation.

Thank you
Alex

 

[Guest]

sHi,

It's getting more complicated now...

Suppose that B1 become 1200, which means the target has been exceded, I need to find a way to instruct the formula that calculate differences that in case the Actual is better than the Target (1000), it overimpose the Target column and goes to 120%.

This is probably a difficult request.

Thanks
Alex

 

[Jon Peltier]

Here's an alternative approach. Put your Target and Actuals in A and B,
and percentages in C and D:

1000 -200 100% -20%
2000 1600 100% 80%
3000 1800 100% 60%
2000 2200 100% 110%

The formula in C1 is =A1/$A1, which I copied and pasted in the rest of
the data range in columns C and D. Make a line chart using the data in C
and D. Double click on one of the series, and on the Options tab, select
Up/Down bars. Then format each of the line series to be invisible (no
markers, no lines). This only shows the gaps between target and actual,
but it might be enough.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______