OR function problem

[luvnrocs]

I'm trying o figure out why this formula isn't working as expected.

I need to check in a column to see if any cells have the number zero
or #NA. If either condition is true, OR should return "True" and then
make the destination cell blank. If neither condition is met then OR
returns "False" and the value from F11 is copied to G11.

This formula is in cell G11:

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

 

[Tyro]

=IF(ISNA(F12),"",IF(F12=0,"",F12))

The OR function evaluates both contitions. If the cell F12 contains an error
#N/A, the comparison F11=0 produces an error and the formula evaluates as
false, so you get #N/A as an answer. You can see this using formula
evaluator.

Tyro

 

[T. Valko]

The problem is that if the cell contains #N/A then:

ISNA(F11) = TRUE

But:

F11=0 = #N/A

Which causes OR to return #N/A

I'm assuming from your description that F11 is supposed to be a *numeric
value* but might be #N/A.

Try this:

=IF(COUNT(1/F11),F11,"")

 

[Tyro]

Correction all references to F12 should be F11

Tyro

 

[T. Valko]

the formula evaluates as false, so you get #N/A as an answer

Not exactly. If OR evaluated to FALSE then the result of the formula would
be the IF_value_if_false argument.

 

[Tyro]

Yes the false path produces the N/A

Tyro

Not exactly. If OR evaluated to FALSE then the result of the formula would
be the IF_value_if_false argument.

 

[Tyro]

What I'm trying to say is this =IF(OR(ISNA(F11),F11=0),"",F11) That formula
evaluates as an error because the error condition in F11 creates an error
when F11=0 is evaluated because F11 holds an error. I understand the basics
of formula evaluation after 45 years as a systems programmer. Thank you.

Tyro

 

[T. Valko]

Not exactly. The formula *never* gets to the value_if_false argument.

Once an error is generated that's where the formula stops and the result of
the formula is the error (unless you've written the formula to account for
that). For example, the formula I used intentionally generates an error
under certain conditions but accounts for it.

 

[T. Valko]

I understand the basics of formula evaluation after 45 years as a systems

programmer.

You and I might know that but the OP and others reading this post might not
know that so we should do our best to provide accurate explanations so that
others may benefit.

 

[luvnrocs]

Actually F11 is a text value. Is there another formula that might work
besides =IF(COUNT(1/F11),F11,"")??

 

[T. Valko]

If you want to trap only #N/A errors but let other errors propagate then you
can use Tyro's formula.

This will trap all errors:

=IF(ISTEXT(F11),F11,"")

 

[Tyro]

I was simply addressing the OP's request, not presenting a panacea.

Tyro

 

[Sandy Mann]

Biff,

May I ask why you used =IF(COUNT(1/F11),F11,"") when
=IF(COUNT(F11),F11,"") seems to work with numeric or #N/A returns in F11?

I assumne that you had a good reason.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

No I was wrong there - if the return in F11 is 0, which may be a legal
return, your formula returns an empty string but
=IF(COUNT(F11),F11,"") rteuns the zero

--
Regads,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

I need to check in a column to see if any cells have the number zero

or #NA. If either condition is true....make the destination cell blank.

My interpretation of that is the value of F11 is either numeric or it may be
#N/A. The OP specifically says if numeric 0 is present then leave the cell
blank.

=IF(COUNT(1/F11),F11,"")

If F11 is numeric 0 (or empty), 1/F11 = #DIV/0!. COUNT returns 0, the
logical_test evaluates to FALSE and leaves the cell blank.

If F11 is #N/A, 1/F11 = #N/A. COUNT returns 0, the logical_test evaluates to
FALSE and leaves the cell blank.

If F11 is any number other than 0, 1/F11 = some number, COUNT returns 1, the
logical_test evaluates to TRUE and returns the value of F11.

Based on my interpretation that F11 is supposed to be a number, 1/F11 also
acts as an error trap if F11 contains a TEXT entry. 1/text = #VALUE!, COUNT
returns 0, the logical_test evaluates to FALSE and leaves the cell blank.

 

[Sandy Mann]

My Apologies Biff. I never went back to the OP's first post to see what he
had said, which of course I should have before saying anything.

Thank you for your tolerance and very full explanation.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

Actually F11 is a text value.

So much for my interpretation!

Oh, well!

I actually like it when folks ask for explanations.