LINEST & CELLS w/ ZERO

[bhorvati]

I have instances where my Y variables sometimes contain a zero in the data and i need a formula to skip over that data point in the LINEST calculation.

Y variables are in Row 1, Columns A:E
X variables are in Row 2, Columns A:E

The following formula is returning a #VALUE! error:

=LINEST(N(OFFSET(A1:E1,SMALL(IF(A1:E1<>0,COLUMN(A1:E1)-COLUMN(A1)),COLUMN(INDIRECT("1:"&COUNT(A1:E1)))),,1)),N(OFFSET(A2:E2,SMALL(IF(A1:E1<>0,COLUMN(A1:E1)-COLUMN(A1)),COLUMN(INDIRECT("1:"&COUNT(A1:E1)))),,1)))

 

[Domenic]

Try...

G1:

=COUNT(A1:E1)-COUNTIF(A1:E1,0)

Then, select H1:I1, enter the following formula, and confirm with CONTROL+SHIFT+ENTER...

=LINEST(N(OFFSET(A1:E1,,SMALL(IF(A1:E1<>0,COLUMN(A1:E1)-COLUMN(A1)),ROW(INDIRECT("1:"&G1))),,1)),N(OFFSET(A2:E2,,SMALL(IF(A1:E1<>0,COLUMN(A1:E1)-COLUMN(A1)),ROW(INDIRECT("1:"&G1))),,1)))

 

[bhorvati]

Thanks so much. Worked!!

I also have some data set up in columns and I tried to use same formula, but didn't work. Can you take a quick look? Thanks so much . Much appreciated. Been working on this all day.

Data in Columns now:
X variables in A1:A5
Y variables in B1:B5


This formula in B7: =COUNT(B1:B5)-COUNTIF(B1:B5,0)

=LINEST(N(OFFSET(B1:B5,,SMALL(IF(B1:B5<>0,ROW(B1:B5)-ROW(B1)),COLUMN(INDIRECT("1:"&B7))),,1)),N(OFFSET(B1:B5,,SMALL(IF(B1:B5<>0,ROW(B1:B5)-ROW(B1)),COLUMN(INDIRECT("1:"&B7))),,1)))

 

[Domenic]

Try...

=LINEST(N(OFFSET(B1:B5,SMALL(IF(B1:B5<>0,ROW(B1:B5)-ROW(B1)),ROW(INDIRECT("1:"&B7))),,1)),N(OFFSET(A1:A5,SMALL(IF(B1:B5<>0,ROW(B1:B5)-ROW(B1)),ROW(INDIRECT("1:"&B7))),,1)))

 

[bhorvati]

Thanks Domenic. Much appreciated.

One more question. The equation for LINEST is .... LINEST(known_y's,known_x's,const,stats). For the "const,stats", I usually use TRUE, TRUE. Where in your formula would I enter this so that b is calculated normally and I also get all the stats returned?

 

[Domenic]

Try...

=LINEST(N(OFFSET(.....)),N(OFFSET(.....)),TRUE,TRUE)

 

[bhorvati]

I don't get any error messages with this, but still only returns the X Variable.

=LINEST(N(OFFSET(B1:B5,SMALL(IF(B1:B5<>0,ROW(B1:B5)-ROW(B1)),ROW(INDIRECT("1:"&B7))),,1)),N(OFFSET(A1:A5,SMALL(IF(B1:B5<>0,ROW(B1:B5)-ROW(B1)),ROW(INDIRECT("1:"&B7))),,1)),TRUE,TRUE)

 

[Domenic]

1) Select two cells in a horizontal range, for example D1:E1

2) Press =

3) Type the formula and then confirm with CONTROL+SHIFT+ENTER, not just ENTER.

 

[bhorvati]

Thanks for all your help. Works perfect.

OK, one more hurdle to overcome. The first formula takes into account any Y variables that happen to be zero. I would also like to expand the formula so that I can make it dynamic so as additional data points are added i don't have to update the formula.

=LINEST(N(OFFSET(I4:I34,SMALL(IF(I4:I34<>0,ROW(I4:I34)-ROW(I4)),ROW(INDIRECT("1:"&I50))),,1)),N(OFFSET(H4:H34,SMALL(IF(I4:I34<>0,ROW(I4:I34)-ROW(I4)),ROW(INDIRECT("1:"&I50))),,1)),TRUE,TRUE)


This equation works for add in dyamic data, but I'm just not sure how to incorporate it into the formula above.

=LINEST(I4:INDEX(I4:I34,MATCH(BigNum,I4:I34)),H4:INDEX(H4:H34,MATCH(BigNum,I4:I34),TRUE,TRUE))

 

[Domenic]

Which version of Excel are you using?