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

 

[bhorvati]

Version 2003

 

[Domenic]

Convert your data into a list...

Data > List > Create List

The ranges will automatically adjust as data is added/removed.