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)

=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

"luvnrocs" wrote in message
...
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)

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,"")


--
Biff
Microsoft Excel MVP


"luvnrocs" wrote in message
...
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)

Correction all references to F12 should be F11

Tyro

"Tyro" wrote in message
et...
=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

"luvnrocs" wrote in message
...
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)

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.


--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
=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

"luvnrocs" wrote in message
...
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)

Yes the false path produces the N/A

Tyro

"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
=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

"luvnrocs" wrote in message
...
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)

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" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
=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

"luvnrocs" wrote in message
...
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)

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.


--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
Yes the false path produces the N/A

Tyro

"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
=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

"luvnrocs" wrote in message
...
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)

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.


--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
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" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
=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

"luvnrocs" wrote in message
...
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)

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



On Mar 6, 10:54 pm, "T. Valko" wrote:
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,"")

--
Biff
Microsoft Excel MVP

"luvnrocs" wrote in message

...

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)

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,"")


--
Biff
Microsoft Excel MVP


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



On Mar 6, 10:54 pm, "T. Valko" wrote:
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,"")

--
Biff
Microsoft Excel MVP

"luvnrocs" wrote in message

...

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)

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

Tyro

"T. Valko" wrote in message
...
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,"")


--
Biff
Microsoft Excel MVP


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



On Mar 6, 10:54 pm, "T. Valko" wrote:
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,"")

--
Biff
Microsoft Excel MVP

"luvnrocs" wrote in message

...

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)

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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
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,"")


--
Biff
Microsoft Excel MVP


"luvnrocs" wrote in message
...
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)

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
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,"")


--
Biff
Microsoft Excel MVP


"luvnrocs" wrote in message
...
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)

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.


--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
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,"")


--
Biff
Microsoft Excel MVP


"luvnrocs" wrote in message
...
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)

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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
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,"")


--
Biff
Microsoft Excel MVP


"luvnrocs" wrote in message
...
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)

Actually F11 is a text value.

So much for my interpretation!

Oh, well!

I actually like it when folks ask for explanations.


--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
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,"")


--
Biff
Microsoft Excel MVP


"luvnrocs" wrote in message
...
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)