Harlan Grove wrote:
Jerry W. Lewis wrote...
...
No version of LINEST permits missing values. For simple linear
regression, use SLOPE and INTERCEPT. If you need the statistics
from
LINEST (or need more numerical stability in pre-2003 versions), see
...

Picky: XL97 at least permits missing 1-D X values, but not missing Y
values or missing 2-D X values.

For multiple independent variables, it gets ugly, but it IS possible
to
handle missing values. Given Excel's limitation on nested function
calls, it's necessary to use a defined name to determine which rows
of
the X and Y variables to include. Something like the defiend name
Include referring to

=--(MMULT(ISNUMBER(X)*ISNUMBER(Y),TRANSPOSE(COLUMN(X) )^0)=COLUMNS(X))

Then try the array formula

=LINEST(

N(OFFSET(Y,SMALL(IF(Include,ROW(X)-CELL("Row",X)),ROW(INDIRECT("1:"&SUM(Include)))),0 ,1,1)),

N(OFFSET(X,SMALL(IF(Include,ROW(X)-CELL("Row",X)),ROW(INDIRECT("1:"&SUM(Include)))),0 ,1,1))
*{1,0}

+N(OFFSET(X,SMALL(IF(Include,ROW(X)-CELL("Row",X)),ROW(INDIRECT("1:"&SUM(Include)))),1 ,1,1))
*{0,1})

for two independent X variables, or

=LINEST(

N(OFFSET(Y,SMALL(IF(Include,ROW(X)-CELL("Row",X)),ROW(INDIRECT("1:"&SUM(Include)))),0 ,1,1)),

N(OFFSET(X,SMALL(IF(Include,ROW(X)-CELL("Row",X)),ROW(INDIRECT("1:"&SUM(Include)))),0 ,1,1))
*{1,0,0}

+N(OFFSET(X,SMALL(IF(Include,ROW(X)-CELL("Row",X)),ROW(INDIRECT("1:"&SUM(Include)))),1 ,1,1))
*{0,1,0}

+N(OFFSET(X,SMALL(IF(Include,ROW(X)-CELL("Row",X)),ROW(INDIRECT("1:"&SUM(Include)))),2 ,1,1))
*{0,0,1})

for three independent X variables, etc.