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

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