Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Disco
 
Posts: n/a
Default 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

  #2   Report Post  
Harlan Grove
 
Posts: n/a
Default

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 ).

  #3   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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


  #4   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #5   Report Post  
 
Posts: n/a
Default


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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel needs feature to delete blanks when autofilter is used. bobz666 Excel Discussion (Misc queries) 7 February 9th 05 06:33 AM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM
How to get lowest value excluding blanks JohnT Excel Worksheet Functions 5 December 4th 04 11:57 AM
Filling in blanks. S. Kissing Excel Worksheet Functions 2 November 24th 04 10:29 PM


All times are GMT +1. The time now is 03:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"