Want to use trend() and linest() but some cells are empty, #N/A, o

[ACcompressor]

I want to use trend() and linest() on a range of data but it returns
"#Value!" if there are empty cells or cells with #N/A, or "" in the "known x"
or "known y" 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.

 

[Harlan Grove]

ACcompressor wrote...

I want to use trend() and linest() on a range of data but it returns
"#Value!" if there are empty cells or cells with #N/A, or "" in the "known x"
or "known y" ranges.

....

It's large & ugly, but try the array formula

=TREND(N(OFFSET(Y,SMALL(IF(ISNUMBER(X)*ISNUMBER(Y),ROW(X)-MIN(ROW(X))),
ROW(INDIRECT("1:"&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("1:"&COUNT(1/ISNUMBER(X)/ISNUMBER(Y))))),0,1,1))...,...)

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