Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel needs feature to delete blanks when autofilter is used. | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
How to get lowest value excluding blanks | Excel Worksheet Functions | |||
Filling in blanks. | Excel Worksheet Functions |