If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
pie chart areas reflect magnitude
I want to show several pie charts all on the same scale.
Example: chart 1 values total to 10, chart 2 values total to 100. So chart 1 is scaled so its area=10, and chart 2 is scaled beside it so its area=100. That would show not only the values of the parts in each pie chart, but the comparison in magnitude between charts 1 and 2. In this example, chart 2 is a much greater value, and therefore bigger pie chart. How can I do that? I tried it with bar charts, could not find what I wanted there, either. |
#2
|
|||
|
|||
pie chart areas reflect magnitude
At best this will produce a set of cartoons which is semi-qualitative
and non-quantitative. If you post some typical data, perhaps someone could attempt a better chart type. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ twagner wrote: I want to show several pie charts all on the same scale. Example: chart 1 values total to 10, chart 2 values total to 100. So chart 1 is scaled so its area=10, and chart 2 is scaled beside it so its area=100. That would show not only the values of the parts in each pie chart, but the comparison in magnitude between charts 1 and 2. In this example, chart 2 is a much greater value, and therefore bigger pie chart. How can I do that? I tried it with bar charts, could not find what I wanted there, either. |
#3
|
|||
|
|||
pie chart areas reflect magnitude
Here is a typical application with real data.
Consider a system of conveyors to haul dirt out of a mine. The hourly operating cost of each conveyor is a sum of: power used operator labor maintenance labor parts consumed The system in question has 16 seperate conveyors; for simplicity, i will only include 3 of them here. Costs are per operating hour. CONVEYOR 1: power: $343.56 operator: $9.62 maintenance: $0.48 parts: $31.96 total: $385.62 CONVEYOR 2: power: $1062.04 operator: $9.62 maintenance: $0.48 parts: $40.22 total: $1112.36 CONVEYOR 3: power: $434.84 operator: $9.62 maintenance: $0.48 parts: $30.28 total: $475.22 The result I am expect would show 3 pie charts, with #1 being the smallest in total area, #3 being a little larger, and #2 being the largest by far. You would see that the power is the vast majority of the cost for #2. Power is large on #1 and #3, but the parts as a percentage show more influence on #1 and #3 than they do on #2. Excel will give me the actual percentages of each cost component, but I want the 3 charts areas to be proportional to their totals. Short of saving all the graphs as jpegs and manipulating them in photoshop, measuring the diameters with a ruler and compressing/expanding them, etc.,..... is there a way to make Excel do that operation for me? Or think of it this way: a single discrete area of any one chart in any section of a chart repesents $1. By having the area of #2 shown as much larger than the other two, it is intuitively apparent that #2 is the largest cost machine, and power is the largest cost part of that largest machine. Quantitatve I can get from the numbers; qualitative is what I want to show in a quick intuitive format to the client. Thanks in advance for your comments. "Jon Peltier" wrote: At best this will produce a set of cartoons which is semi-qualitative and non-quantitative. If you post some typical data, perhaps someone could attempt a better chart type. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ twagner wrote: I want to show several pie charts all on the same scale. Example: chart 1 values total to 10, chart 2 values total to 100. So chart 1 is scaled so its area=10, and chart 2 is scaled beside it so its area=100. That would show not only the values of the parts in each pie chart, but the comparison in magnitude between charts 1 and 2. In this example, chart 2 is a much greater value, and therefore bigger pie chart. How can I do that? I tried it with bar charts, could not find what I wanted there, either. |
#4
|
|||
|
|||
pie chart areas reflect magnitude
Well, there's nothing wrong with a qualitative view that is also
quantitative. I've made a bar chart which is a good representation of your data: http://peltiertech.com/images/2009-08/ConveyorBars1.png If you want to display percentages and totals, you could add data labels. I've put total dollars on the Total bars, and percentages on the others in this chart: http://peltiertech.com/images/2009-08/ConveyorBars2.png While Conveyors 1 and 3 have a greater influence of parts and operator costs, power still makes up 89% and 92% of the total, compared to 96% for Conveyor 2. Not as significant an effect as your description at first led me to think. It's more a case of all conveyors having small constant or nearly constant costs plus a huge cost of power. If the power cost is lower, then the small items contribute a somewhat greater percentage. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ twagner wrote: Here is a typical application with real data. Consider a system of conveyors to haul dirt out of a mine. The hourly operating cost of each conveyor is a sum of: power used operator labor maintenance labor parts consumed The system in question has 16 seperate conveyors; for simplicity, i will only include 3 of them here. Costs are per operating hour. CONVEYOR 1: power: $343.56 operator: $9.62 maintenance: $0.48 parts: $31.96 total: $385.62 CONVEYOR 2: power: $1062.04 operator: $9.62 maintenance: $0.48 parts: $40.22 total: $1112.36 CONVEYOR 3: power: $434.84 operator: $9.62 maintenance: $0.48 parts: $30.28 total: $475.22 The result I am expect would show 3 pie charts, with #1 being the smallest in total area, #3 being a little larger, and #2 being the largest by far. You would see that the power is the vast majority of the cost for #2. Power is large on #1 and #3, but the parts as a percentage show more influence on #1 and #3 than they do on #2. Excel will give me the actual percentages of each cost component, but I want the 3 charts areas to be proportional to their totals. Short of saving all the graphs as jpegs and manipulating them in photoshop, measuring the diameters with a ruler and compressing/expanding them, etc.,..... is there a way to make Excel do that operation for me? Or think of it this way: a single discrete area of any one chart in any section of a chart repesents $1. By having the area of #2 shown as much larger than the other two, it is intuitively apparent that #2 is the largest cost machine, and power is the largest cost part of that largest machine. Quantitatve I can get from the numbers; qualitative is what I want to show in a quick intuitive format to the client. Thanks in advance for your comments. "Jon Peltier" wrote: At best this will produce a set of cartoons which is semi-qualitative and non-quantitative. If you post some typical data, perhaps someone could attempt a better chart type. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ twagner wrote: I want to show several pie charts all on the same scale. Example: chart 1 values total to 10, chart 2 values total to 100. So chart 1 is scaled so its area=10, and chart 2 is scaled beside it so its area=100. That would show not only the values of the parts in each pie chart, but the comparison in magnitude between charts 1 and 2. In this example, chart 2 is a much greater value, and therefore bigger pie chart. How can I do that? I tried it with bar charts, could not find what I wanted there, either. |
#5
|
|||
|
|||
pie chart areas reflect magnitude
You are right about the power and its influence on costs. The other machines
in the system show similar results. I was just trying to find a good way to present that information. I know from experience with this client that they use pie charts, so I was trying to give them something in a familiar format. My original question asked if there was a way in Excel to make the areas of several pie charts reflect the magnitude of the numbers upon which they are based. Sounds like the answer is "no." Perhaps a stacked bar chart would be even more intuitive, as the total can be easily compared machine to machine. Then the color for any one component will make its contribution to that total easy to see. I appreciate your thoughts. "Jon Peltier" wrote: Well, there's nothing wrong with a qualitative view that is also quantitative. I've made a bar chart which is a good representation of your data: http://peltiertech.com/images/2009-08/ConveyorBars1.png If you want to display percentages and totals, you could add data labels. I've put total dollars on the Total bars, and percentages on the others in this chart: http://peltiertech.com/images/2009-08/ConveyorBars2.png While Conveyors 1 and 3 have a greater influence of parts and operator costs, power still makes up 89% and 92% of the total, compared to 96% for Conveyor 2. Not as significant an effect as your description at first led me to think. It's more a case of all conveyors having small constant or nearly constant costs plus a huge cost of power. If the power cost is lower, then the small items contribute a somewhat greater percentage. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ twagner wrote: Here is a typical application with real data. Consider a system of conveyors to haul dirt out of a mine. The hourly operating cost of each conveyor is a sum of: power used operator labor maintenance labor parts consumed The system in question has 16 seperate conveyors; for simplicity, i will only include 3 of them here. Costs are per operating hour. CONVEYOR 1: power: $343.56 operator: $9.62 maintenance: $0.48 parts: $31.96 total: $385.62 CONVEYOR 2: power: $1062.04 operator: $9.62 maintenance: $0.48 parts: $40.22 total: $1112.36 CONVEYOR 3: power: $434.84 operator: $9.62 maintenance: $0.48 parts: $30.28 total: $475.22 The result I am expect would show 3 pie charts, with #1 being the smallest in total area, #3 being a little larger, and #2 being the largest by far. You would see that the power is the vast majority of the cost for #2. Power is large on #1 and #3, but the parts as a percentage show more influence on #1 and #3 than they do on #2. Excel will give me the actual percentages of each cost component, but I want the 3 charts areas to be proportional to their totals. Short of saving all the graphs as jpegs and manipulating them in photoshop, measuring the diameters with a ruler and compressing/expanding them, etc.,..... is there a way to make Excel do that operation for me? Or think of it this way: a single discrete area of any one chart in any section of a chart repesents $1. By having the area of #2 shown as much larger than the other two, it is intuitively apparent that #2 is the largest cost machine, and power is the largest cost part of that largest machine. Quantitatve I can get from the numbers; qualitative is what I want to show in a quick intuitive format to the client. Thanks in advance for your comments. "Jon Peltier" wrote: At best this will produce a set of cartoons which is semi-qualitative and non-quantitative. If you post some typical data, perhaps someone could attempt a better chart type. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ twagner wrote: I want to show several pie charts all on the same scale. Example: chart 1 values total to 10, chart 2 values total to 100. So chart 1 is scaled so its area=10, and chart 2 is scaled beside it so its area=100. That would show not only the values of the parts in each pie chart, but the comparison in magnitude between charts 1 and 2. In this example, chart 2 is a much greater value, and therefore bigger pie chart. How can I do that? I tried it with bar charts, could not find what I wanted there, either. |
#6
|
|||
|
|||
pie chart areas reflect magnitude
The answer to your original question is actually yes, but it's a pain
and it will not result in a cognitively effective display, no matter how many pie charts the client is used to seeing. A stacked bar chart is nearly as ineffective as a pie. I have actually blogged about this problem: Simple Bar Chart Beats Complex Multiple Sized Pies - http://peltiertech.com/WordPress/sim...le-sized-pies/ In the article and in the comments, a number of ways are shown to display your information. I like the clustered bar chart for its simplicity, or the offset-stacked bar chart (the last chart in my long comment). In this case, where one factor dwarfs the rest, a stacked or offset-stacked chart works. If there were several factors of roughly the same value, then a clustered chart is the way to go. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ twagner wrote: You are right about the power and its influence on costs. The other machines in the system show similar results. I was just trying to find a good way to present that information. I know from experience with this client that they use pie charts, so I was trying to give them something in a familiar format. My original question asked if there was a way in Excel to make the areas of several pie charts reflect the magnitude of the numbers upon which they are based. Sounds like the answer is "no." Perhaps a stacked bar chart would be even more intuitive, as the total can be easily compared machine to machine. Then the color for any one component will make its contribution to that total easy to see. I appreciate your thoughts. "Jon Peltier" wrote: Well, there's nothing wrong with a qualitative view that is also quantitative. I've made a bar chart which is a good representation of your data: http://peltiertech.com/images/2009-08/ConveyorBars1.png If you want to display percentages and totals, you could add data labels. I've put total dollars on the Total bars, and percentages on the others in this chart: http://peltiertech.com/images/2009-08/ConveyorBars2.png While Conveyors 1 and 3 have a greater influence of parts and operator costs, power still makes up 89% and 92% of the total, compared to 96% for Conveyor 2. Not as significant an effect as your description at first led me to think. It's more a case of all conveyors having small constant or nearly constant costs plus a huge cost of power. If the power cost is lower, then the small items contribute a somewhat greater percentage. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ twagner wrote: Here is a typical application with real data. Consider a system of conveyors to haul dirt out of a mine. The hourly operating cost of each conveyor is a sum of: power used operator labor maintenance labor parts consumed The system in question has 16 seperate conveyors; for simplicity, i will only include 3 of them here. Costs are per operating hour. CONVEYOR 1: power: $343.56 operator: $9.62 maintenance: $0.48 parts: $31.96 total: $385.62 CONVEYOR 2: power: $1062.04 operator: $9.62 maintenance: $0.48 parts: $40.22 total: $1112.36 CONVEYOR 3: power: $434.84 operator: $9.62 maintenance: $0.48 parts: $30.28 total: $475.22 The result I am expect would show 3 pie charts, with #1 being the smallest in total area, #3 being a little larger, and #2 being the largest by far. You would see that the power is the vast majority of the cost for #2. Power is large on #1 and #3, but the parts as a percentage show more influence on #1 and #3 than they do on #2. Excel will give me the actual percentages of each cost component, but I want the 3 charts areas to be proportional to their totals. Short of saving all the graphs as jpegs and manipulating them in photoshop, measuring the diameters with a ruler and compressing/expanding them, etc.,..... is there a way to make Excel do that operation for me? Or think of it this way: a single discrete area of any one chart in any section of a chart repesents $1. By having the area of #2 shown as much larger than the other two, it is intuitively apparent that #2 is the largest cost machine, and power is the largest cost part of that largest machine. Quantitatve I can get from the numbers; qualitative is what I want to show in a quick intuitive format to the client. Thanks in advance for your comments. "Jon Peltier" wrote: At best this will produce a set of cartoons which is semi-qualitative and non-quantitative. If you post some typical data, perhaps someone could attempt a better chart type. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ twagner wrote: I want to show several pie charts all on the same scale. Example: chart 1 values total to 10, chart 2 values total to 100. So chart 1 is scaled so its area=10, and chart 2 is scaled beside it so its area=100. That would show not only the values of the parts in each pie chart, but the comparison in magnitude between charts 1 and 2. In this example, chart 2 is a much greater value, and therefore bigger pie chart. How can I do that? I tried it with bar charts, could not find what I wanted there, either. |
Thread Tools | |
Display Modes | |
|
|