software

I want to use trend() and linest() on a range of data but it returns
quot;#Value!quot; if there are empty cells or cells with #N/A, or quot;quot; in the quot;known xquot;
or quot;known yquot; ranges.
I have a template which I paste data into but it is never the same number
of observed points, usually about 30. X is in B84:Bxxx , Y is in C84:Cxxx.
I am trying to lookup the y on the curve fit at x observed. In F84 I have
the following formula =TREND(C$84:C$133,B$84:B$133^{1,2,3},B84^{1,2,3}). I
don't want to have to manual change the formula in F84:F133 because the data
doesn't go all the way to row133. I have written a complicated macro to
write the correct formula into each cell but I want a simpler way.

ACcompressor wrote...
gt;I want to use trend() and linest() on a range of data but it returns
gt;quot;#Value!quot; if there are empty cells or cells with #N/A, or quot;quot; in the quot;known xquot;
gt;or quot;known yquot; ranges.
....

It's large amp; ugly, but try the array formula

=TREND(N(OFFSET(Y,SMALL(IF(ISNUMBER(X)*ISNUMBER(Y) ,ROW(X)-MIN(ROW(X))),
ROW(INDIRECT(quot;1:quot;amp;COUNT(1/ISNUMBER(X)/ISNUMBER(Y))))),0,1,1)),
N(OFFSET(X,SMALL(IF(ISNUMBER(X)*ISNUMBER(Y),ROW(X)-MIN(ROW(X))),
ROW(INDIRECT(quot;1:quot;amp;COUNT(1/ISNUMBER(X)/ISNUMBER(Y))))),0,1,1))...,...)

replacing X and Y with your known X and known Y range addresses,
respectively.