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
|
|||
|
|||
Is it possible to plot 3 surfaces in a Pivot Chart? (Repost)
Hello
May I please ask for your kind help? I have 5 variables - a, b, c, d, and Fitness. When I create a Pivot Chart, with Fitness=height, I obtain one chart with ONE surface. It is very useful to plot everything on one chart, however I would like to plot several surfaces on the chart. In other words, to me a surface is a 3D plot of variable Fitness vs variables a and b, holding values for variables c and d constant. Example 1. Suppose variable c can take on 3 values, and variable d can take on 4 values. This means that there can be 3*4=12 combinations of variables c and d. For this case, I would like to display 12 surfaces on one chart. Is this possible? Example 2. Here is another way to look at this problem. This problem shows up in 2D too. For instance, below we have 3 Downward-Sloping lines, representing the behaviour of Fitness for different values of variable b: Fitness |\ \ | \ \ \ | \ \ \ | \ | | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 But, unfortunately, in Excel Pivot Charts only ONE surface/line is displayed [instead of 3 lines], as illustrated below: Fitness |\ \ | \ /\ / \ | \/ \ / \ | \ | | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 This makes the plot confusing and hard to understand. When one sees a zig-zag line like the one on the figure above, it is difficult to understand that the true pattern is really downward sloping [as variable a increases, for all values of variable b]. So, again my question is - how can we show separate surfaces [on one Pivot chart], instead of one confusing surface? This is my first time using Pivot tables. I have searched Excel help as well as the archived messages in this newsgroup, but I couldn't find any mention of this problem. I am sure that the reason for this is that I don't know the right keywords to use... Thank you! Sincerely Alison p.s. when I posted this message in August, I got a reply: "Looks to me like you have 'a' and 'b' both in the row section of the pivot table. I think you'd be better off with 'a' in the row section and 'b' in the column section (or vice versa). Then you can make a chart with three series (b1, b2, b3) that span the levels of a (a1, a2, a3)." In this message I gave a 2-D example that can be fixed in the way the reply to my message had suggested - my real problem is a 3-D problem, and it cannot be resolved by using this suggestion. Is it Possible to plot 3 surfaces in a Pivot chart? I had postponed doing these charts since August, but now I finally have to complete my project. I would be truly grateful for any suggestions. |
#2
|
|||
|
|||
Is it possible to plot 3 surfaces in a Pivot Chart? (Repost)
Alison -
I set up a table with my data like this: A B Dummy Value a1 b1 b1 8 a2 b1 b1 7 a3 b1 b1 6 a1 b2 b2 7 a2 b2 b2 6 a3 b2 b2 5 a1 b3 b3 6 a2 b3 b3 5 a3 b3 b3 4 My pivot table has A and B in the Row area and Dummy in the Column area, and it looks like this: Sum of Value Dummy B A b1 b2 b3 b1 a1 8 a2 7 a3 6 b2 a1 7 a2 6 a3 5 b3 a1 6 a2 5 a3 4 If you make a chart now, by columns, you will have three different series, one for each column (i.e., one for each B value), which looks like this for my sample data: Fitness |* | * + | * + o | + o | o | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Alison wrote: Hello May I please ask for your kind help? I have 5 variables - a, b, c, d, and Fitness. When I create a Pivot Chart, with Fitness=height, I obtain one chart with ONE surface. It is very useful to plot everything on one chart, however I would like to plot several surfaces on the chart. In other words, to me a surface is a 3D plot of variable Fitness vs variables a and b, holding values for variables c and d constant. Example 1. Suppose variable c can take on 3 values, and variable d can take on 4 values. This means that there can be 3*4=12 combinations of variables c and d. For this case, I would like to display 12 surfaces on one chart. Is this possible? Example 2. Here is another way to look at this problem. This problem shows up in 2D too. For instance, below we have 3 Downward-Sloping lines, representing the behaviour of Fitness for different values of variable b: Fitness |\ \ | \ \ \ | \ \ \ | \ | | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 But, unfortunately, in Excel Pivot Charts only ONE surface/line is displayed [instead of 3 lines], as illustrated below: Fitness |\ \ | \ /\ / \ | \/ \ / \ | \ | | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 This makes the plot confusing and hard to understand. When one sees a zig-zag line like the one on the figure above, it is difficult to understand that the true pattern is really downward sloping [as variable a increases, for all values of variable b]. So, again my question is - how can we show separate surfaces [on one Pivot chart], instead of one confusing surface? This is my first time using Pivot tables. I have searched Excel help as well as the archived messages in this newsgroup, but I couldn't find any mention of this problem. I am sure that the reason for this is that I don't know the right keywords to use... Thank you! Sincerely Alison p.s. when I posted this message in August, I got a reply: "Looks to me like you have 'a' and 'b' both in the row section of the pivot table. I think you'd be better off with 'a' in the row section and 'b' in the column section (or vice versa). Then you can make a chart with three series (b1, b2, b3) that span the levels of a (a1, a2, a3)." In this message I gave a 2-D example that can be fixed in the way the reply to my message had suggested - my real problem is a 3-D problem, and it cannot be resolved by using this suggestion. Is it Possible to plot 3 surfaces in a Pivot chart? I had postponed doing these charts since August, but now I finally have to complete my project. I would be truly grateful for any suggestions. |
#3
|
|||
|
|||
Is it possible to plot 3 surfaces in a Pivot Chart? (Repost)
Hello Jon!
Thank you very much for your reply. Your suggestion works perfectly for the problem that I had asked. When I tried it for my more complicated problem, I have encountered several issues. It works when the chart type is set to "column". It seems not to work when the chart type is set to "surface" [instead of 3 separate surfaces, I again see 1 big surface]. Also, when I tried plotting this example, I had removed 2 of my 4 variables. This lead to problems, as I then had identical points with different "column height" - i.e. (a1=0, b1=1, fitness=column height = 10) AND (a1=0, b1=1, fitness=column height = 0) This happened because for the first point, variable C=c1=0 and for the second point variable C=c2=5... Jon - my original problem is the following - given 4 variables and a variable called fitness, present how "fitness" varies for different combinations of the 4 variables. I decided to present this information by fixing variables 3 and 4 to a certain value and then plotting a Surface that describes how "fitness" varies with variable 1 and 2. Keeping variable 4 fixed, I wanted to change variable 3 to the next value and create a new surface describing how "fitness" behaves for different values of variable 1 and 2 [this surface would be located on the same Chart, to the right of the first surface]. Thus, if variable 3 takes on 10 values, I wanted to have 10 separate surfaces on one chart (I posted my original message, in order to find out how to do this). This would complete Chart 1 that I was going to paste into Word. I then wanted to have a new Chart, for the next value of variable 4, and I was going to paste it below Chart 1. Jon, may I please ask you a question? Is this there another/better way to do this? If this is an efficient way, then would you know how it is done. Currently I seem not to be able to do this, because when Chart Type=Surface, Excel 2000 shows one big surface even when I use your suggestion below to create a Pivot Chart. I sincerely appreciate your help, Jon! Thank you, Alison I set up a table with my data like this: A B Dummy Value a1 b1 b1 8 a2 b1 b1 7 a3 b1 b1 6 a1 b2 b2 7 a2 b2 b2 6 a3 b2 b2 5 a1 b3 b3 6 a2 b3 b3 5 a3 b3 b3 4 My pivot table has A and B in the Row area and Dummy in the Column area, and it looks like this: Sum of Value Dummy B A b1 b2 b3 b1 a1 8 a2 7 a3 6 b2 a1 7 a2 6 a3 5 b3 a1 6 a2 5 a3 4 If you make a chart now, by columns, you will have three different series, one for each column (i.e., one for each B value), which looks like this for my sample data: Fitness |* | * + | * + o | + o | o | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Alison wrote: Hello May I please ask for your kind help? I have 5 variables - a, b, c, d, and Fitness. When I create a Pivot Chart, with Fitness=height, I obtain one chart with ONE surface. It is very useful to plot everything on one chart, however I would like to plot several surfaces on the chart. In other words, to me a surface is a 3D plot of variable Fitness vs variables a and b, holding values for variables c and d constant. Example 1. Suppose variable c can take on 3 values, and variable d can take on 4 values. This means that there can be 3*4=12 combinations of variables c and d. For this case, I would like to display 12 surfaces on one chart. Is this possible? Example 2. Here is another way to look at this problem. This problem shows up in 2D too. For instance, below we have 3 Downward-Sloping lines, representing the behaviour of Fitness for different values of variable b: Fitness |\ \ | \ \ \ | \ \ \ | \ | | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 But, unfortunately, in Excel Pivot Charts only ONE surface/line is displayed [instead of 3 lines], as illustrated below: Fitness |\ \ | \ /\ / \ | \/ \ / \ | \ | | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 This makes the plot confusing and hard to understand. When one sees a zig-zag line like the one on the figure above, it is difficult to understand that the true pattern is really downward sloping [as variable a increases, for all values of variable b]. So, again my question is - how can we show separate surfaces [on one Pivot chart], instead of one confusing surface? This is my first time using Pivot tables. I have searched Excel help as well as the archived messages in this newsgroup, but I couldn't find any mention of this problem. I am sure that the reason for this is that I don't know the right keywords to use... Thank you! Sincerely Alison p.s. when I posted this message in August, I got a reply: "Looks to me like you have 'a' and 'b' both in the row section of the pivot table. I think you'd be better off with 'a' in the row section and 'b' in the column section (or vice versa). Then you can make a chart with three series (b1, b2, b3) that span the levels of a (a1, a2, a3)." In this message I gave a 2-D example that can be fixed in the way the reply to my message had suggested - my real problem is a 3-D problem, and it cannot be resolved by using this suggestion. Is it Possible to plot 3 surfaces in a Pivot chart? I had postponed doing these charts since August, but now I finally have to complete my project. I would be truly grateful for any suggestions. . |
#5
|
|||
|
|||
Is it possible to plot 3 surfaces in a Pivot Chart? (Repost)
Alison -
As Tushar points out, surface charts in Excel are not very flexible. You can only plot one surface per chart, and you are limited to how you can format things. I had done my analysis below for a line chart. Even if it were possible to put everything into a highly detailed chart, it might not be the smart thing to do. I usually make a lot of smaller 2D charts, because too many variables are difficult to understand in a chart, and 3D charts (surface charts and 2D charts with 3D effects) tend to distort the data. It can be effective to put four or eight smaller charts in a worksheet, shrunk so they will print out on a single page. Arrange the charts in rows and columns according to the factors (row 1 is A1, row 2 is A2, column 1 is B1, column 2 is B2, etc.). In the title of the chart, mention the levels of the factors. Make sure the scales are the same for all of them, and that way, the user only has to figure out only one chart. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Alison wrote: Hello Jon! Thank you very much for your reply. Your suggestion works perfectly for the problem that I had asked. When I tried it for my more complicated problem, I have encountered several issues. It works when the chart type is set to "column". It seems not to work when the chart type is set to "surface" [instead of 3 separate surfaces, I again see 1 big surface]. Also, when I tried plotting this example, I had removed 2 of my 4 variables. This lead to problems, as I then had identical points with different "column height" - i.e. (a1=0, b1=1, fitness=column height = 10) AND (a1=0, b1=1, fitness=column height = 0) This happened because for the first point, variable C=c1=0 and for the second point variable C=c2=5... Jon - my original problem is the following - given 4 variables and a variable called fitness, present how "fitness" varies for different combinations of the 4 variables. I decided to present this information by fixing variables 3 and 4 to a certain value and then plotting a Surface that describes how "fitness" varies with variable 1 and 2. Keeping variable 4 fixed, I wanted to change variable 3 to the next value and create a new surface describing how "fitness" behaves for different values of variable 1 and 2 [this surface would be located on the same Chart, to the right of the first surface]. Thus, if variable 3 takes on 10 values, I wanted to have 10 separate surfaces on one chart (I posted my original message, in order to find out how to do this). This would complete Chart 1 that I was going to paste into Word. I then wanted to have a new Chart, for the next value of variable 4, and I was going to paste it below Chart 1. Jon, may I please ask you a question? Is this there another/better way to do this? If this is an efficient way, then would you know how it is done. Currently I seem not to be able to do this, because when Chart Type=Surface, Excel 2000 shows one big surface even when I use your suggestion below to create a Pivot Chart. I sincerely appreciate your help, Jon! Thank you, Alison I set up a table with my data like this: A B Dummy Value a1 b1 b1 8 a2 b1 b1 7 a3 b1 b1 6 a1 b2 b2 7 a2 b2 b2 6 a3 b2 b2 5 a1 b3 b3 6 a2 b3 b3 5 a3 b3 b3 4 My pivot table has A and B in the Row area and Dummy in the Column area, and it looks like this: Sum of Value Dummy B A b1 b2 b3 b1 a1 8 a2 7 a3 6 b2 a1 7 a2 6 a3 5 b3 a1 6 a2 5 a3 4 If you make a chart now, by columns, you will have three different series, one for each column (i.e., one for each B value), which looks like this for my sample data: Fitness |* | * + | * + o | + o | o | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Alison wrote: Hello May I please ask for your kind help? I have 5 variables - a, b, c, d, and Fitness. When I create a Pivot Chart, with Fitness=height, I obtain one chart with ONE surface. It is very useful to plot everything on one chart, however I would like to plot several surfaces on the chart. In other words, to me a surface is a 3D plot of variable Fitness vs variables a and b, holding values for variables c and d constant. Example 1. Suppose variable c can take on 3 values, and variable d can take on 4 values. This means that there can be 3*4=12 combinations of variables c and d. For this case, I would like to display 12 surfaces on one chart. Is this possible? Example 2. Here is another way to look at this problem. This problem shows up in 2D too. For instance, below we have 3 Downward-Sloping lines, representing the behaviour of Fitness for different values of variable b: Fitness |\ \ | \ \ \ | \ \ \ | \ | | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 But, unfortunately, in Excel Pivot Charts only ONE surface/line is displayed [instead of 3 lines], as illustrated below: Fitness |\ \ | \ /\ / \ | \/ \ / \ | \ | | |__________________________ a1 a2 a3 a1 a2 a3 a1 a2 a3 b1 b2 b3 This makes the plot confusing and hard to understand. When one sees a zig-zag line like the one on the figure above, it is difficult to understand that the true pattern is really downward sloping [as variable a increases, for all values of variable b]. So, again my question is - how can we show separate surfaces [on one Pivot chart], instead of one confusing surface? This is my first time using Pivot tables. I have searched Excel help as well as the archived messages in this newsgroup, but I couldn't find any mention of this problem. I am sure that the reason for this is that I don't know the right keywords to use... Thank you! Sincerely Alison p.s. when I posted this message in August, I got a reply: "Looks to me like you have 'a' and 'b' both in the row section of the pivot table. I think you'd be better off with 'a' in the row section and 'b' in the column section (or vice versa). Then you can make a chart with three series (b1, b2, b3) that span the levels of a (a1, a2, a3)." In this message I gave a 2-D example that can be fixed in the way the reply to my message had suggested - my real problem is a 3-D problem, and it cannot be resolved by using this suggestion. Is it Possible to plot 3 surfaces in a Pivot chart? I had postponed doing these charts since August, but now I finally have to complete my project. I would be truly grateful for any suggestions. . |
#6
|
|||
|
|||
Is it possible to plot 3 surfaces in a Pivot Chart? (Repost)
Thank you, Jon!
It is a pity that this functionality is not available, but at least now I will know that I am not doing it because it is impossible to do, not because I don't know how to do it. Also, your help with the 2D case will be helpful to me and hopefully to other Excel users who read your post. All the Best Alison As Tushar points out, surface charts in Excel are not very flexible. You can only plot one surface per chart, and you are limited to how you can format things. I had done my analysis below for a line chart. Even if it were possible to put everything into a highly detailed chart, it might not be the smart thing to do. I usually make a lot of smaller 2D charts, because too many variables are difficult to understand in a chart, and 3D charts (surface charts and 2D charts with 3D effects) tend to distort the data. It can be effective to put four or eight smaller charts in a worksheet, shrunk so they will print out on a single page. Arrange the charts in rows and columns according to the factors (row 1 is A1, row 2 is A2, column 1 is B1, column 2 is B2, etc.). In the title of the chart, mention the levels of the factors. Make sure the scales are the same for all of them, and that way, the user only has to figure out only one chart. |
Thread Tools | |
Display Modes | |
|
|