Is it possible to plot 3 surfaces in a Pivot Chart? (Repost)

  • Thread starter Thread starter Alison
  • Start date Start date
A

Alison

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.
 
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
Click to expand...

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

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.
Click to expand...
 
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
Click to expand...

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

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.
Click to expand...

.
Click to expand...
 
Unfortunately, XL's 3D charting capability is rather limited. What you
want to do is way beyond it.

For a 2D line chart, you can accomplish what you want by adjusting what
is a row field and what is a column field in the corresponding
PivotTable. However, in a 3D chart, XL can show only one surface.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office

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.
Click to expand...
 
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
_______
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
Click to expand...

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),
Click to expand...

which looks
like this for my sample data:

Fitness
|*
| * +
| * + o
| + o
| o
|
|__________________________
a1 a2 a3 a1 a2 a3 a1 a2 a3
b1 b2 b3
Click to expand...

- 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,
Click to expand...
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
Click to expand...
variables
c and d constant.

Example 1. Suppose variable c can take on 3 values,
Click to expand...
and
variable d can take on 4 values. This means that there
can be 3*4=12 combinations of variables c and d. For
Click to expand...
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
Click to expand...
problem.
This problem shows up in 2D too. For instance, below
Click to expand...
we
have 3 Downward-Sloping lines, representing the
Click to expand...
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
Click to expand...
pattern
is really downward sloping [as variable a increases,
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
off
with 'a' in the row section and 'b' in the column
Click to expand...
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
Click to expand...
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
Click to expand...
had
postponed doing these charts since August, but now I
finally have to complete my project. I would be truly
grateful for any suggestions.
Click to expand...

.
Click to expand...
Click to expand...
 
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
 
Back
Top