3D Stacked column - scale question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is a simple question: I'm comparing 6 different products across 3
categories on a 3D stacked column chart. Everything is fine to this point. My
issue, one of the products has very large values in 2 ctegories out of 3 in
comparison to the other 5 products. My data values range from 1 to 30 but the
6th product ranges from 1 to 500. What's the best way to approach this, other
than not to plot the 6th product?


Thanks.
 
I would try a number of other chart types. First, the sooner you get away
from 3D types, the sooner your data is undistorted by false perspective etc.
Second, 2D charts provide numerous ways to deal with things like this.

When one item out of many is well out of range of the others, there are
three commonly suggested workarounds:
1. Plot the outlier on the secondary axis.
2. Use a logarithmic value axis scale.
3. Put a break in the value axis:
http://peltiertech.com/Excel/Charts/BrokenYAxis.html

- Jon
 
Thanks Jon for the detailed explanation on how to plot the outlier on the
column chart. I understood the concept behind it, but my data is not as
simple as yours.
My values for the 6 products for category 1(0.9, 1.6,4.4,2.8,0.6, and 9.8)
for category 2(2.4,5.8,19.2,12.7,3,111.3), and category
3(27.4,82.6,430.9,13.6,3.2,0.1).
I decided to have my brk pt at 28 and max pt at 128 so the difference is
100, but I ended up with negatives points, and the 'Y' axis still overwhelmed
with the values range variation.
The data in your example is organized. You have 2 values out of range, but
not much difference between those two points. While I have most of my values
under 28, then I have 82, 111, and 430 which makes it a little diffecult to
plot. Or may be i'm not following the technique correctly?

Thanks.
 
The data is different than mine, so you need a different conversion for data
above the cutoff. I didn't set up the entire chart, but I did come up with
this reasonable rescaling. You need a cutoff value, an intercept value, and
a slope value. The new value is related to the old value by this formula:

new = if(old<cutoff,old,old*slope+intercept)

Note: slope and intercept are not related to fitting parameters, just
convenient terms.

For your data, I used a cutoff of 50, an intercept of 50, and a slope of
0.1. The original data of

Cat 1 Cat 2 Cat 3
Prod 1 0.9 2.4 27.4
Prod 2 1.6 5.8 82.6
Prod 3 4.4 19.2 430.9
Prod 4 2.8 12.7 13.6
Prod 5 0.6 3.0 3.2
Prod 6 9.8 111.3 0.1

is converted to

Cat 1 Cat 2 Cat 3
Prod 1 0.9 2.4 27.4
Prod 2 1.6 5.8 58.3
Prod 3 4.4 19.2 93.1
Prod 4 2.8 12.7 13.6
Prod 5 0.6 3.0 3.2
Prod 6 9.8 61.1 0.1

Change the true Y axis scale to 0 to 100, put your break at 50, and use
these labels (first column) and y values (second column) for the dummy axis
labels:

500 100
400 90
300 80
200 70
100 60
40 40
30 30
20 20
10 10
0 0

- Jon
 
Thanks Jon. The new data helped getting things a little better. One thing
though, I followed your example all the way until I get to the point where to
add the labels to the 'Y' axis and that where I was unable to show my actual
values above the brake point (Y axis showing 2 sets of values, and the max
value is 50 instead of 500).

I could send you the chart file if you would like to see it.

Thanks.
 
You have to hide the actual Y axis labels (Patterns tab, select None for
tick labels). The labels that appear are the data labels for the points of
the dummy series you add to represent the axis.

- Jon
 
Back
Top