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.