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

rogerdavid

New Member
Joined
Jan 3, 2013
Messages
15
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 :)
 

Some videos you may like

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
Joined
Jan 3, 2013
Messages
15
***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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
***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)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,474
Messages
5,529,052
Members
409,849
Latest member
J7House1984
Top