Stacked Column - change positioning

  • Thread starter Thread starter Camelean
  • Start date Start date
C

Camelean

Hi,

I have the following data set

Apr May Jun Jul Aug Sep
2002/03 491 233 2039 2146 292 2716
2003/04 2523 2261 1054 784 2267 41

What i want to do is have the largest value(with corresponding colour)
as the bottom of a stacked column and smaller value on the top.
what I'm getting now is 2003/04 data is always on the bottom and
2002/03 is on top.


Your help is greatly appreciated.

thanks,
Jason
 
Jason -

Start with your original data in A1:G3,

Apr May Jun Jul Aug Sep
2002/03 491 233 2039 2146 292 2716
2003/04 2523 2261 1054 784 2267 41

set up A5:G9 as follows:

Apr May Jun Jul Aug Sep
2002/03
2003/04
2002/03
2003/04

In B6:B9, enter these formulas:

=IF(A2>A3,A2,0)
=IF(A2>A3,0,A3)
=IF(A2>A3,0,A2)
=IF(A2>A3,A3,0)

Select B6:G9, and press Ctrl-R to fill these formulas to the right. It
looks like this:

Apr May Jun Jul Aug Sep
2002/03 0 0 2039 2146 0 2716
2003/04 2523 2261 0 0 2267 0
2002/03 491 233 0 0 292 0
2003/04 0 0 1054 784 0 41

Make your stacked column chart, and format both pairs of series the same
(so the 2002/03 are both blue, say, and the 2003/04 are both green).
Now there are duplicate legend entries. Select the legend, then select
one of the 2002/03 entries (the text, not the colored square), and press
the Delete key. Do the same with one of the 2003/04 entries.

- Jon
 
thanks Jon,

That worked perfectly.

jason

Jon Peltier said:
Jason -

Start with your original data in A1:G3,

Apr May Jun Jul Aug Sep
2002/03 491 233 2039 2146 292 2716
2003/04 2523 2261 1054 784 2267 41

set up A5:G9 as follows:

Apr May Jun Jul Aug Sep
2002/03
2003/04
2002/03
2003/04

In B6:B9, enter these formulas:

=IF(A2>A3,A2,0)
=IF(A2>A3,0,A3)
=IF(A2>A3,0,A2)
=IF(A2>A3,A3,0)

Select B6:G9, and press Ctrl-R to fill these formulas to the right. It
looks like this:

Apr May Jun Jul Aug Sep
2002/03 0 0 2039 2146 0 2716
2003/04 2523 2261 0 0 2267 0
2002/03 491 233 0 0 292 0
2003/04 0 0 1054 784 0 41

Make your stacked column chart, and format both pairs of series the same
(so the 2002/03 are both blue, say, and the 2003/04 are both green).
Now there are duplicate legend entries. Select the legend, then select
one of the 2002/03 entries (the text, not the colored square), and press
the Delete key. Do the same with one of the 2003/04 entries.

- Jon
Click to expand...
 
Back
Top