Excluding 0s and blanks from a LINEST function

[Disco]

Hey Everyone,

I'm trying to get a linear regression function to work and am having a
lot of difficulty. I need the function to do a linear regression
(LINEST) of 2 columns of data, but I need it to exclude 0s and blanks
in the data(this is where I'm stuck). I've tried some of the
suggestions I've seen here for the SUM and AVERAGE functions but it
doesn't seem to be working with LINEST. Help

- Disco

 

[Harlan Grove]

Disco wrote...

I'm trying to get a linear regression function to work and am having a
lot of difficulty. I need the function to do a linear regression
(LINEST) of 2 columns of data, but I need it to exclude 0s and blanks
in the data(this is where I'm stuck). I've tried some of the
suggestions I've seen here for the SUM and AVERAGE functions but it
doesn't seem to be working with LINEST. Help

Which Excel version?

Maybe the following archived thread will help.

http://groups-beta.google.com/group...31fff22c235/c58cdd63033f6a2f#c58cdd63033f6a2f
(or http://makeashorterlink.com/?Q3E52236A ).

 

[Jerry W. Lewis]

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

http://groups-beta.google.com/group/microsoft.public.excel.worksheet.functions/msg/d6a03470e7a1c650

The formula for seb should be
seb = steyx*SQRT(...

For missing values, you can replace x arrays in the formulas with
IF(ISNUMBER(x)*ISNUMBER(y),x) and y arrays with
IF(ISNUMBER(x)*ISNUMBER(y),y) and array enter (Ctrl-Shift-Enter) the
formulas.

Jerry

 

[Harlan Grove]

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.

 

[fjmorales]

Jerry W. Lewis wrote...
...
...

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))
+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))
+N(OFFSET(X,SMALL(IF(Include,ROW(X)-CELL("Row",X)),ROW(INDIRECT("1:"&SUM(Include)))),1,1,1))
+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.