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

#### rogerdavid

##### New Member
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### rogerdavid

##### New Member
***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

Last edited:

##### MrExcel MVP
***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:
Rich (BB code):
``````=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

##### New Member
Thanks, this solved my problem!

##### MrExcel MVP
Thanks, this solved my problem!

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

Replies
6
Views
42
Replies
4
Views
76
Replies
3
Views
69
Replies
1
Views
305
Replies
22
Views
2K