OR function?

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I am trying to have '0' and '#N/A' if they occur replaced with "".

If I place a '0' in A1 and use the formula =IF(A1=0,"",A1) then fine I get
"".

If I place a '#N/A' in A1 and use the formula =IF(ISERROR(A1),"",A1) then
fine again I get "".

If I place a '0' in cell A1 and use the formula
=IF(OR(ISERROR(A1),A1=0),"",A1) then fine again I get ""

However if I place a '#N/A' in cell A1 and use the formula
=IF(OR(ISERROR(A1),A1=0),"",A1) then I get '#N/A'.

I have also tried =IF(OR(A1="#N/A",A1=0),"",A1) but still get '#N/A'.
Where am I messing up?
Sandy
 
Try:

=IF(OR(ISERROR(A1=0),ISERROR(A1)),"",A1)

or if you only want to trap #N/A! errors:

=IF(OR(ISNA(A1=0),ISNA(A1)),"",A1)



HTH,
JP
 
Try it this way:

=IF(ISNA(A1),"",IF(OR(A1=0,A1=""),"",A1))

Slightly longer, but seems to do what you want.

Hope this helps.

Pete
 
The reason you're getting the #N/A is because the OR function evaluates all
conditions. You have an error in A1. When the OR evaluates ISERROR(A), this
evaluates as TRUE. When the OR evaluates A1=0 this results in the error #N/A
being compared to 0 an thus causes an error in the OR statement, so the OR
does not evaluate as true and you get the false return, that is A1 and not
the TRUE return of "". You can see this if you step through the formula
with formula evaluation.

Try =IF(ISERROR(A1),"",IF(A1=0,"",A1))

Tyro
 
I checked this using a vlookup in A1 and changed what was looked for.
when the results were 0, '#N/A' it shows ""


=IF(ISERROR(OR(A1=0,ISERROR(A1))),"","")

Scott
 
Hi Guys

JP - if there is a '0' in A1 then 0 is the result. I was looking for both 0
and #N/A replaced with "".

Pete_UK and Tyro - both solutions work fine.

Thanks to you all.
Sandy
 
Back
Top