How do I Ignore #N/A with LINEST ? I have Partial Solution already...

[rogerdavid]

Hi,

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

 

[rogerdavid]

***EDIT - MORE CORRECT INFORMATION ON CONDITIONS FOR FAILURE BELOW ***

Disregard the previously listed failure conditions above, this is a more correct diagnosis:

CONDITION FOR FAILURE:
- A cell Column A value is #N/A and it has a corresponding NUMBERICAL VALUE in Column B (example that will lead to failure: A4 = #N/A, B4 = 4)

This will work...
A,B
1,1
2,2
3,3
4,4
5,5
6,6
7,7

This will work...
A,B
1,1
2,2
3,3
4,#N/A
5,5
6,6
7,7

This will work...
A,B
1,1
2,2
3,3
#N/A,#N/A
5,5
6,6
7,7

This will work...
A,B
1,#N/A
2,2
3,3
#N/A,#N/A
5,5
6,6
#N/A,#N/A




THIS WILL FAIL
A,B
1,1
2,2
3,3
#N/A,4
5,5
6,6
7,7


Hope that all made sense haha, i tried to be as clear as possible... first post, woohoo

 

[Aladin Akyurek]

***EDIT - MORE CORRECT INFORMATION ON CONDITIONS FOR FAILURE BELOW ***

Disregard the previously listed failure conditions above, this is a more correct diagnosis:

CONDITION FOR FAILURE:
- A cell Column A value is #N/A and it has a corresponding NUMBERICAL VALUE in Column B (example that will lead to failure: A4 = #N/A, B4 = 4)

This will work...
A,B
1,1
2,2
3,3
4,4
5,5
6,6
7,7

This will work...
A,B
1,1
2,2
3,3
4,#N/A
5,5
6,6
7,7

This will work...
A,B
1,1
2,2
3,3
#N/A,#N/A
5,5
6,6
7,7

This will work...
A,B
1,#N/A
2,2
3,3
#N/A,#N/A
5,5
6,6
#N/A,#N/A




THIS WILL FAIL
A,B
1,1
2,2
3,3
#N/A,4
5,5
6,6
7,7


Hope that all made sense haha, i tried to be as clear as possible... first post, woohoo

Control+shift+enter, not just enter:

=LINEST(
  N(OFFSET(A2:A8,SMALL(IF(ISNUMBER(A2:A8*B2:B8),ROW(B2:B8)-ROW(B2)),
    ROW(INDIRECT("1:"&MIN(COUNT(A2:A8),COUNT(B2:B8))))),0,1)),
  N(OFFSET(B2:B8,SMALL(IF(ISNUMBER(A2:A8*B2:B8),ROW(B2:B8)-ROW(B2)),
    ROW(INDIRECT("1:"&MIN(COUNT(A2:A8),COUNT(B2:B8))))),0,1)),
  TRUE,TRUE)

 

[rogerdavid]

Thanks, this solved my problem!

 

[Aladin Akyurek]

Thanks, this solved my problem!

You are welcome. Domenic will be pleased too. Thanks for providing feedback.