Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore #N/A in formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore #N/A in formula
On Feb 13, 10:00 pm, wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore #N/A in formula
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore #N/A in formula
=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. -- Regards, Tom Ogilvy "Jason Lepack" wrote in message oups.com... On Feb 13, 10:00 pm, wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore #N/A in formula
On Feb 13, 10:18 pm, wrote:
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,A 3))+(if(isna(B2), 0,B2)*if(isna(B3),0,B3))+(if(isna(C2),0,C2)*if(isn a(C3),0,C3)))/A1) I specifically isolated the check of A1 outside the formula so as to prevent a div/0 error. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore #N/A in formula | Excel Discussion (Misc queries) | |||
Ignore #N/A in a formula | Excel Discussion (Misc queries) | |||
Ignore #N/A in a formula | Excel Discussion (Misc queries) | |||
how can i ignore an error in a formula | Excel Worksheet Functions | |||
How can I ignore an #N/A value in a =SUM() formula? | Excel Worksheet Functions |