100% Stacked Column

  • Thread starter Thread starter Doria/Warris
  • Start date Start date
D

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
 
Doria/Warris said:
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
Click to expand...
ACTUAL
B1 = -200
Click to expand...
DIFFERENCE = TARGET - ACTUAL
C1 = 800
Click to expand...
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
Click to expand...

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

Brad.
 
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
 
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
 
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/
_______
 
Back
Top