Ignore #N/A in formula

[archanapatelwhite]

Hi I have the following formula

=((A2*A3)+(B2*B3)+(C2*C3))/A1


how can I get the result ignoring #N/A for example if A2 = #N/A


Please help


Thanks
Archana

 

[Jason Lepack]

Hi I have the following formula

=((A2*A3)+(B2*B3)+(C2*C3))/A1

how can I get the result ignoring #N/A for example if A2 = #N/A

Please help

Thanks
Archana

if(isna(formula), "", formula)

Cheers,
Jason Lepack

 

[archanapatelwhite]

Thanks for getting back to me.
The formula you posted only displays a result if there isn't an #N/A
anywhere in the formula, I want the result excluding the n/a ie if A2
= #N/A ; A3 = 1 ; B2 = 3 ; B3 = 2 and A1 = 2 i want the result ((#N/
A*1)+(3*2))/2 to equal 3 not a blank, so in essence I want to treat
the #N/A like a 0
Does that make sense?

 

[Tom Ogilvy]

=SUM(IF(ISERROR(A2:C2),0,A2:C2)*IF(ISERROR(A3:C3),0,A3:C3))/A1

Entered with Ctrl+shift+Enter rather than just Enter since this is an array
formula.

an alternate approach is to insure the cells do not display #N/A:
if you are using lookup formulas in your cell, you could handle the error
there

Example in A2
=if(iserror(formula),0,formula)

Repeat for the other five cells.

 

[Jason Lepack]

Thanks for getting back to me.
The formula you posted only displays a result if there isn't an #N/A
anywhere in the formula, I want the result excluding the n/a ie if A2
= #N/A ; A3 = 1 ; B2 = 3 ; B3 = 2 and A1 = 2 i want the result ((#N/
A*1)+(3*2))/2 to equal 3 not a blank, so in essence I want to treat
the #N/A like a 0
Does that make sense?

=if(isna(A1),0,((if(isna(A2),0,A2)*if(isna(A3),0,A3))+(if(isna(B2),
0,B2)*if(isna(B3),0,B3))+(if(isna(C2),0,C2)*if(isna(C3),0,C3)))/A1)

I specifically isolated the check of A1 outside the formula so as to
prevent a div/0 error.