View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.charting
B. R.Ramachandran
 
Posts: n/a
Default Chart with three variables

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