3D Stacked column - scale question
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. -- If u change the way u look @ things, the things u look at change. |
3D Stacked column - scale question
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "sahafi" wrote in message ... 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. -- If u change the way u look @ things, the things u look at change. |
3D Stacked column - scale question
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. -- If u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "sahafi" wrote in message ... 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. -- If u change the way u look @ things, the things u look at change. |
3D Stacked column - scale question
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "sahafi" wrote in message ... 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. -- If u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "sahafi" wrote in message ... 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. -- If u change the way u look @ things, the things u look at change. |
3D Stacked column - scale question
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. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "sahafi" wrote in message ... 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. -- If u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "sahafi" wrote in message ... 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. -- If u change the way u look @ things, the things u look at change. |
3D Stacked column - scale question
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "sahafi" wrote in message ... 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. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "sahafi" wrote in message ... 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. -- If u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "sahafi" wrote in message ... 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. -- If u change the way u look @ things, the things u look at change. |
All times are GMT +1. The time now is 04:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com