I've made hundreds of runs with a computer model. With every run, two of
the variables are changed, resulting in a new number (third variable). I
want to make a chart, with the first variable on the x-axis and the
second variable on the y-axis. The point in the chart needs to have,
besides the x and y-value, the value of the third variable. Through the
points with the same third variable, a trendline has to be drawn.
Because there are several hundreds of model runs, I don't want to sort
the data manually on the third variable and make a seperate serie of
all data with the same third variable. Is there an easier way to create
such a chart?


--
glasbergenm
------------------------------------------------------------------------
glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001
View this thread: http://www.excelforum.com/showthread...hreadid=506690

Yes, there is an easier way to create such a chart. You can use Excel's built-in PivotChart feature to create a chart with three variables.

Here are the steps to create the chart:

1. Select all of your data, including the headers.
2. Go to the Insert tab and click on PivotChart.
3. In the Create PivotChart dialog box, make sure "New Worksheet" is selected and click "OK".
4. In the PivotTable Field List, drag the first variable to the "Columns" area, the second variable to the "Rows" area, and the third variable to the "Values" area.
5. By default, Excel will create a sum of the third variable. To change this, click on the drop-down arrow next to the third variable in the "Values" area and select "Value Field Settings". In the dialog box that appears, select "Average" (or another appropriate calculation) and click "OK".
6. Your PivotChart should now be created. To add a trendline, right-click on any data point in the chart and select "Add Trendline". In the "Format Trendline" dialog box, select the type of trendline you want and customize it as desired.

That's it! Your chart should now have all three variables plotted, with a trendline for each value of the third variable.

Depending on the data, you might be able to turn the data into a pivot
table, with your X values in the row area, the third variable in the column
area, and the Y values in your data area. If the third variable is a
continuous variable, you may get reasonable results if you group the values
in this field.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"glasbergenm"
wrote in message
...

I've made hundreds of runs with a computer model. With every run, two of
the variables are changed, resulting in a new number (third variable). I
want to make a chart, with the first variable on the x-axis and the
second variable on the y-axis. The point in the chart needs to have,
besides the x and y-value, the value of the third variable. Through the
points with the same third variable, a trendline has to be drawn.
Because there are several hundreds of model runs, I don't want to sort
the data manually on the third variable and make a seperate serie of
all data with the same third variable. Is there an easier way to create
such a chart?


--
glasbergenm

Dear Jon,

I don't see how I can use the pivot table to create the graph. I've put
the data and a hand-made example for a part of the file as an attachment
to this message. I hope this gives a better understanding of what I
need.


+-------------------------------------------------------------------+
|Filename: Clipboard.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4288 |
+-------------------------------------------------------------------+

--
glasbergenm
------------------------------------------------------------------------
glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001
View this thread: http://www.excelforum.com/showthread...hreadid=506690

The download link didn't work. ("Invalid Attachment specified", whatever
that means.)

You have three columns, X, Y, and Z. I described the arrangement of the
pivot table in my previous post in this thread.

To create a regular chart from a pivot table, select a blank cell outside
the pivot table, and start the chart wizard. In step 1, select the chart
type. In step 2, click on the Series tab, then add each series individually,
defining the Name (column header in the PT), X Values (left column in the
PT), and Y Values (data column in the PT).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"glasbergenm"
wrote in message
...

Dear Jon,

I don't see how I can use the pivot table to create the graph. I've put
the data and a hand-made example for a part of the file as an attachment
to this message. I hope this gives a better understanding of what I
need.


+-------------------------------------------------------------------+
|Filename: Clipboard.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4288 |
+-------------------------------------------------------------------+

--
glasbergenm

Hi,

I posted a response to your post about 6 hours ago, but it hasn't gone
through yet; so I am posting it again.

For convenience, place your X-values in Column B (e.g., B2:B101), Y-values
in Column C (i.e., C2:C101), and Z-values in Column A (i.e., A2:A101).
Select the entire range (A2:C101) and sort by Column A. Make an XY-Scatter
Plot of Y-values vs X-values (Don't join the data points on the plot!).

Place one of the possible Z-values in D2.
Select E2:E101, and in the Formula Bar below the Tool Bar enter the
following array-formula and confirm with CTRL-SHIFT-ENTER.

=INDIRECT("B"&LARGE(ROW($A$2:$A$101)*($A$2:$A$101= $D$2),COUNTIF($A$2:$A$101,$D$2))&":B"&MAX(ROW($A$2 :$A$101)*($A$2:$A$101=$D$2)))

Similarly, select F2:F101, and enter the following array-formula.

=INDIRECT("C"&LARGE(ROW($A$2:$A$101)*($A$2:$A$101= $D$2),COUNTIF($A$2:$A$101,$D$2))&":C"&MAX(ROW($A$2 :$A$101)*($A$2:$A$101=$D$2)))

Add a series (Series 2) to the graph you have already created, using E2:E101
as the X-range and F2:F101 as the Y-range. This plot would correspond to the
subset of your X,Y-data that is relevant to the Z-value you have entered in
D2, and hence will overlap a part of the first plot. Add a trendline to
Series 2 (and set it up for the equation to show).

Now you can manually change the Z-value, and the Series 2 plot (and the
trendline and the trendline equation) will update accordingly.

Regards,
B. R. Ramachandran

"glasbergenm" wrote:


I've made hundreds of runs with a computer model. With every run, two of
the variables are changed, resulting in a new number (third variable). I
want to make a chart, with the first variable on the x-axis and the
second variable on the y-axis. The point in the chart needs to have,
besides the x and y-value, the value of the third variable. Through the
points with the same third variable, a trendline has to be drawn.
Because there are several hundreds of model runs, I don't want to sort
the data manually on the third variable and make a seperate serie of
all data with the same third variable. Is there an easier way to create
such a chart?


--
glasbergenm
------------------------------------------------------------------------
glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001
View this thread: http://www.excelforum.com/showthread...hreadid=506690

The Array-formulas work perfectly, but are not the (complete) solution
to my problem: it still means a lot of manual labour to create the
trendlines for all of the data (the Z-value can have about 135
different values). I've tried to make a row with all possible Z-values
and copy the array formula below that row. Instead of cell D2, I've
made the formula depend on the Z-values in the above row. Copying of
the array formula, however, doesn't seem as easy as copying an
ordinairy formula. When I've found a solution for this, I could use the
TREND function (asuming a linear dependency), but this formula has
difficulties with blank cells, error values and zero's (every z-value
has a different amount of x and y-values).

What was I thinking when I said "of course I can do this..." :(


--
glasbergenm
------------------------------------------------------------------------
glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001
View this thread: http://www.excelforum.com/showthread...hreadid=506690

Hi,

How about the following approach?

In D2:D136, enter all possible Z-values.
In E2 and F2, enter the following array-formulas respectively
(CTRL-SHIFT-ENT), autofill the formulas down to E136 and F136 (Note that
these formulas are somewhat analogous to the ones I had posted in my previous
response, except that I have removed the "INDIRECT" parts and have modified
the "$D$2"s as "$D2"s).

=
"B"&LARGE(ROW($A$2:$A$101)*($A$2:$A$101=$D2),COUNT IF($A$2:$A$101,$D2))&":B"&MAX(ROW($A$2:$A$101)*($A $2:$A$101=$D2))

="C"&LARGE(ROW($A$2:$A$101)*($A$2:$A$101=$D2),COUN TIF($A$2:$A$101,$D2))&":C"&MAX(ROW($A$2:$A$101)*($ A$2:$A$101=$D2))

The above formulas will return the X- and Y- ranges (as strings) for each
Z-value inColumn D.

You can use the strings in Columns E and F as arguments for functions such
as SLOPE, INTERCEPT, TREND, and LINEST.

For example, enter the following formulas in G2 and H2 respectively, and
autofill them down to G136 and H136.

=SLOPE(INDIRECT(F2),INDIRECT(E2))
=INTERCEPT(INDIRECT(F2),INDIRECT(E2))

Regards,
B. R. Ramachandran



"glasbergenm" wrote:


The Array-formulas work perfectly, but are not the (complete) solution
to my problem: it still means a lot of manual labour to create the
trendlines for all of the data (the Z-value can have about 135
different values). I've tried to make a row with all possible Z-values
and copy the array formula below that row. Instead of cell D2, I've
made the formula depend on the Z-values in the above row. Copying of
the array formula, however, doesn't seem as easy as copying an
ordinairy formula. When I've found a solution for this, I could use the
TREND function (asuming a linear dependency), but this formula has
difficulties with blank cells, error values and zero's (every z-value
has a different amount of x and y-values).

What was I thinking when I said "of course I can do this..." :(


--
glasbergenm
------------------------------------------------------------------------
glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001
View this thread: http://www.excelforum.com/showthread...hreadid=506690

On Wed, 1 Feb 2006, in microsoft.public.excel.charting,
Jon Peltier said:

The download link didn't work. ("Invalid Attachment specified", whatever
that means.)

Jon, click on the "view this thread" link instead, and then click on
"Clipboard.jpg" from the page that comes up, or "example.zip" to
download the sample dataset.

I don't see how I can use the pivot table to create the graph. I've put
the data and a hand-made example for a part of the file as an attachment
to this message. I hope this gives a better understanding of what I
need.

It looks to me like what you're describing is a contour, or surface,
chart. Excel does those. You may then have to manually trace the
contours onto separate graphs if you wanted, but I can't see why you'd
want to: the contour chart shows them all elegantly in one.

However, I failed to get the chart wizard to build a surface chart
straight from a pivot chart, because the grey buttons confused the
wizard, so I had to create a whole extra table that just duplicated the
pivot chart without the "B" and "POC" etc. That let me produce
something showing the X and Y axes with realistic scales.

See if this helps:

http://www.branta.demon.co.uk/science/example2.xls

--
Del Cotter
NB Personal replies to this post will
send email to
Please send your email to del2 instead

The download link didn't work. ("Invalid Attachment specified", whatever
that means.)

Jon, click on the "view this thread" link instead, and then click on
"Clipboard.jpg" from the page that comes up, or "example.zip" to download
the sample dataset.

I'm not going to open the data file, but I did look at the chart image.

I don't see how I can use the pivot table to create the graph. I've put
the data and a hand-made example for a part of the file as an attachment
to this message. I hope this gives a better understanding of what I
need.

It looks to me like what you're describing is a contour, or surface,
chart. Excel does those. You may then have to manually trace the contours
onto separate graphs if you wanted, but I can't see why you'd want to: the
contour chart shows them all elegantly in one.

I don't think it's a contour chart that the OP wants, I think it's a chart
with a whole set of trendlines. I can't see any way to do this without
making separate trendlines for each subset of the data. This means doing
what the OP didn't want to do: "sort the data manually on the third variable
and make a seperate serie of all data with the same third variable." I would
take this opportunity to learn how VBA might make one's life easier by
automatic the constructions of so many data series and trendlines.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______