Excluding 0s and blanks from a LINEST function

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

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/...8cdd63033f6a2f
(or http://makeashorterlink.com/?Q3E52236A ).

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/...a03470e7a1c650

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

Disco wrote:

> 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

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.

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.

Hi All,

I know this is an old thread, but I wanted to contribute some code for getting r2 from LINEST using VBA. It's ugly, but it works.

Basically: Count through the two columns to be compared. If any row in either column is blank, skip it, otherwise copy the data for both columns to a temporary worksheet. Run LINEST on that temp worksheet and record the r2 value.

Please Login or Register  to view this content.

I then use conditional formatting to highlight r2 values above a certain threshold.

Also note: this would also be a good reply to this thread, but that one's locked: http://www.ozgrid.com/forum/showthread.php?t=41284

If forcing to zero, the r2 value reported here is correct (in Excel 2003 and later only) and that in the manually created chart is wrong. http://support.microsoft.com/kb/829249/en-us

Enjoy!

Alex

And a follow-up: If all you're after is the "R2" value form Linest, that's exactly teh same as the RSQ function (and much easier to implement!) If you also want to F, dF, etc values, then Linest may still be of use, but there's probably an easier way to get it.

::bangs head on desk::