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