#### rogerdavid

##### New Member

- Joined
- Jan 3, 2013

- Messages
- 15

So I have two columns of data that I need to run a LINEST on, but these two columns will inevitabley have #N/A in some of their cells and there isnt anything I can do about this.

That's fine, because I found a partial solution from a previous thread: LINEST question

=LINEST(N(OFFSET(A2:A7,SMALL(IF(ISNUMBER(B2:B7),ROW(B2:B7)-ROW(B2)),ROW(INDIRECT("1:"&COUNT(B2:B7)))),0,1)),N(OFFSET(B2:B7,SMALL(IF(ISNUMBER(B2:B7),ROW(B2:B7)-ROW(B2)),ROW(INDIRECT("1:"&COUNT(B2:B7)))),0,1)),true,true)

This works for the situation where either (A) One column has a #N/A value, or (B) *both* columns have a #NA value.

HOWEVER!!

It

__fails__(returns an array of #VALUE errors) when there is an #N/A value in the

__LAST ROW__of the Data Range (in the above case, A2:B7 - that is, A7 or B7 is #N/A )

Is there anyone out there that might be able to make some alterations to the above formula such that it accounts for the data range having #N/A in either (or both) cells of the last row of the data range?

Cheers