can i do this type of stacking column chart? (and how?)

[annie]

as a non-mathematically-minded designer type, i could really use some
help!

i need to create some stacking column charts where:

- the value axis represents money, in a particular scale, in
particular increments

- the x axis represents different companies

- each bar represents the MINIMUM, MIDPOINT and MAXIMUM amounts of
money spent by each company, graphed against the value scale of money

For example, say the scale is $1 to $100 in $10 increments. Company A
spends a minimum of $5, a midpoint of $50 and a maximum of $70. I need
the bar in the graph for company A to have three color segments, one
of which goes from $0 to $5, the next from $5 to $50 and the last from
$50 to $70, and then it stops. Same for Companies B, C, etc.

Now, I know I cannot use the type of stacked bar chart where the
values have to add up to 100%, as that is not the case in this
scenario. Thus, I have used the OTHER stacked column chart type
offered by Excel, which is just called Stacked Column.

The problem is that it stacks the FULL AMOUNTS for each category (min,
max, mid) on top of one another, instead of letting one pick up where
the other leaves off.

So, I get a chart, where, following the example I gave above, Company
A has a column segment from $0-5, then from $5-55 (adding 50 to 5) and
then from $55-75 (adding 20 to 55). So, looking at the chart, it looks
like Company A's maximum is $75. At least, I'm pretty sure that's
what's going on.

Is there some way I can make these charts the first way I described
instead? It's odd, because the first way described is the default for
this chart type in Illustrator... but Illustrator is too much of a
pain for this project. Hoping someone will reveal Excel's secrets to
me... thanks!

 

[Del Cotter]

as a non-mathematically-minded designer type, i could really use some
help!

- each bar represents the MINIMUM, MIDPOINT and MAXIMUM amounts of
money spent by each company, graphed against the value scale of money

The problem is that it stacks the FULL AMOUNTS for each category (min,
max, mid) on top of one another, instead of letting one pick up where
the other leaves off.

Is there some way I can make these charts the first way I described
instead? It's odd, because the first way described is the default for
this chart type in Illustrator... but Illustrator is too much of a
pain for this project. Hoping someone will reveal Excel's secrets to
me... thanks!

Excel has no secrets. It's just a series of cells with numbers or
formulas in them, and you instruct the cells to use the formulas that
will calculate the numbers you want.

So Company A spends a minimum of $5, a midpoint of $50 and a maximum of
$70. You type in the three numbers:

[A] [C]
[1] A Co. B Co.
[2] min $5 $7
[3] mid $50 $49
[4] max $70 $71

Then below those three numbers, you type the following formulae:

[A] [C]
[1] A Co. B Co.
[2] min $5 $7
[3] mid $50 $49
[4] max $70 $71
[5]
[6] A Co. B Co.
[7] min =B2 =C2
[8] mid =B3-B2 =C3-C2
[9] max =B4-B3 =C4-C3

Now rows [6] to [9] look like this

[A] [C]
[1] A Co. B Co.
[2] min $5 $7
[3] mid $50 $49
[4] max $70 $71
[5]
[6] A Co. B Co.
[7] min $5 $7
[8] mid $45 $42
[9] max $20 $22

If you stack the numbers 5, 45, and 20 on top of each other, they'll
show the bar you want.

However, for you there is an even simpler way. I'm always lecturing
people to use the spreadsheet and stop trying to do everything in the
chart, but as long as all you want is three colors, you could simply use
a *NON*-stacked bar chart, order the numbers from highest to lowest
(instead of lowest to highest, as I showed above)

[A] [C]
[1] A Co. B Co.
[2] max $70 $71
[3] mid $50 $49
[4] min $5 $7

Now create your bar chart, then select one of the bars, Format Selected
Object.. Options, then set the Overlap value to 100%. This turns the
bars from side-by-side (overlap is zero) to right on top of each other
(overlap is complete), but it does *not* stack them. So the lowest
value hides the low part of higher values (this was why you had to type
the columns upside down, otherwise the biggest column would be in front
and would hide everything).

But generally speaking, whenever you find yourself puzzled as to how to
do something, don't look for special commands in the charting section,
just calculate the numbers you want and let the spreadsheet do the
arithmetic.

 

[annie]

Brilliant.That makes complete sense. I will employ your method.

I did think, too, that the second chart type you describe would be
simpler to implement, but this is part of a design project for a
client and they specifically requested the former type of chart.

Thank you very much for your help.

annie

 

[Del Cotter]

I did think, too, that the second chart type you describe would be
simpler to implement, but this is part of a design project for a
client and they specifically requested the former type of chart.

They'll look exactly the same. It's two routes to the same output, the
only difference is in the input. Your clients may just have wanted the
first way because they want to enter the differences and not the stacked
totals in. In which case all my talk of subtracting formulas is
redundant anyway.