OR function?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 22 Feb 2008 17:23 GMT

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

Reply to this Message

JP - 22 Feb 2008 17:41 GMT

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

> I am trying to have '0' and '#N/A' if they occur replaced with "".
>
[quoted text clipped - 13 lines]
> Where am I messing up?
> Sandy

Reply to this Message

Pete_UK - 22 Feb 2008 17:42 GMT

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

> I am trying to have '0' and '#N/A' if they occur replaced with "".
>
[quoted text clipped - 13 lines]
> Where am I messing up?
> Sandy

Reply to this Message

Tyro - 22 Feb 2008 17:49 GMT

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 am trying to have '0' and '#N/A' if they occur replaced with "".
>
[quoted text clipped - 13 lines]
> Where am I messing up?
> Sandy

Reply to this Message

scott - 22 Feb 2008 18:02 GMT

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

>=IF(OR(ISERROR(A1),A1=0),"",A1) then I get '#N/A'.

Reply to this Message

scott - 22 Feb 2008 18:06 GMT

OOPs
I posted wrong

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

>I checked this using a vlookup in A1 and changed what was looked for.
>when the results were 0, '#N/A' it shows ""
[quoted text clipped - 4 lines]
>
>>=IF(OR(ISERROR(A1),A1=0),"",A1) then I get '#N/A'.

Reply to this Message

Sandy - 22 Feb 2008 18:18 GMT

Thanks Scott it works very well too
Sandy

> OOPs
> I posted wrong
[quoted text clipped - 9 lines]
>>
>>>=IF(OR(ISERROR(A1),A1=0),"",A1) then I get '#N/A'.

Reply to this Message

Sandy - 22 Feb 2008 18:09 GMT

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

> I am trying to have '0' and '#N/A' if they occur replaced with "".
>
[quoted text clipped - 13 lines]
> Where am I messing up?
> Sandy

Reply to this Message

Pete_UK - 22 Feb 2008 20:21 GMT

You're welcome, Sandy - thanks for feeding back.

Pete

> Hi Guys
>
[quoted text clipped - 25 lines]
>
> - Show quoted text -

Reply to this Message