Question relating to the OR function

[FiluDlidu]

Hi all,

I half-noticed a long while ago that whenever there is an error within the
arguments to test in a logical function, the final result will also be an
error.

But it just stroke me today that if I have something like...
A1: 1
A2: =OR(A1=1,A1=2/0)
....A2 will return an error despite the fact the first argument returned
TRUE, which in any case should validate the OR... shouldn't it?

Is there a trick to go around this problem without stacking functions?

Thanks for any input,

Feelu

 

[Ron Rosenfeld]

Hi all,

I half-noticed a long while ago that whenever there is an error within the
arguments to test in a logical function, the final result will also be an
error.

But it just stroke me today that if I have something like...
A1: 1
A2: =OR(A1=1,A1=2/0)
...A2 will return an error despite the fact the first argument returned
TRUE, which in any case should validate the OR... shouldn't it?

Is there a trick to go around this problem without stacking functions?

Thanks for any input,

Feelu

From HELP:

The arguments must evaluate to logical values such as TRUE or FALSE, or in
arrays or references that contain logical values.

If an array or reference argument contains text or empty cells, those values
are ignored.

Seems the arguments can be text, empty, or a logical value. Since your error
is none of the above, it does not fall within the acceptable definition for an
OR argument.

Whether is "should" work is a matter you need to take up with MS :-(
--ron

 

[Tyro]

Nope. Excel is not a sophisticated programming language. It evaluates all
parts of the OR even if one is true. My question to you is what is the value
of 2/0? Mathematically, it's meaningless because as the divisor approaches
0, the quotient approaches infinity. So does 2/0 = 3/0 (infinity = infinity)
and thus 2 = 3?

Tyro

 

[Roger Govier]

Hi Feelu

Because your second test will always result in a #DIV/0 error, then the
result will always be an error.

If you had used 2/1 or 2/3, then the fact that the first part of the
Condition returned True, would give a True result.

You could avoid the error result, but still get a False return to that
particular formula, by using
=NOT(ISERROR(OR(A1=1,A1=2/0)))

=OR(A1=1,A1=2/0) = #DIV/0!
=NOT(ISERROR(OR(A1=1,A1=2/0))) = FALSE
=NOT(ISERROR(OR(A1=1,A1=2/3))) =TRUE

 

[FiluDlidu]

Yes I agree that 2/0 is meaningless, but I wanted to make it simple... The
zero could have been the value of a cell that happened to be equal to 0, but
not necessarily, or the second argument could have been a lookup function
that would not have worked properly or any type of error.

My problem was a little more complex than that, but I didn't want to be
unclear when all I really wanted to know was if there was an easy way around
it.

I'm still using XL2003 and am therefore limited to only three conditions in
conditional formatting, so I want to cram any possibility in any one of them,
but I keep getting falses where it should really be true for at least one of
the argument was true... Oh well! Let's get to work.

Thanks for taking the time to give your thoughts on that.

 

[Ragdyer]

As far as increasing the number of CF conditions in pre XL07 versions,
check these out:


Without using code:
http://www.mcgimpsey.com/excel/conditional6.html


With VBA:
http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

[Tyro]

=OR(TRUE,FALSE,FALSE,FALSE) evaluates as TRUE; =OR(TRUE,FALSE,FALSE,#ERROR)
evaluates as #ERROR. Perhaps you want to enclose your formula in an ISERROR
function (pre-Excel 2007) or IFERROR (Excel 2007)

Tyro

 

[FiluDlidu]

Now that you made me think about it...

=OR(if(iserror([test1]),0,[test1],if(iserror([test2]),0,[test2],...)

That solved my problem, even though not in a very elegant way...

Cheers.

 

[FiluDlidu]

The former I knew about and it won't help my problem.

The latter, however, I didn't know, and it seems pretty interesting.

Thanks for your input,

Feelu

 

[Tyro]

Perhaps =ISERROR(OR(cond_1,cond-2,....cond_N))
This returns TRUE if any of the OR conditions is an error, FALSE if not.

Tyrp

Now that you made me think about it...

=OR(if(iserror([test1]),0,[test1],if(iserror([test2]),0,[test2],...)

That solved my problem, even though not in a very elegant way...

Cheers.

=OR(TRUE,FALSE,FALSE,FALSE) evaluates as TRUE;
=OR(TRUE,FALSE,FALSE,#ERROR)
evaluates as #ERROR. Perhaps you want to enclose your formula in an
ISERROR
function (pre-Excel 2007) or IFERROR (Excel 2007)

 

[FiluDlidu]

Ho! I guess I must have explained something unclearly: I didn't want to know
whether or not there was an argument that was an error, I only wanted the
function to return TRUE if any of its argument were true, regardless of
possible error value of other arguments, for I thought as soon as one of its
arguments is true, the OR should also be true. And for the way I'm using it,
the errors could be treated as false values...

The function I mentioned in my previous post was intended to return
something like the following:

A3: =or(if(iserror(A1=0),0,A1=0),if(iserror(A1=A1/A2),0,A1=A1/A2))

If A1=0 and A2=0, then A3 returns TRUE, because the first test is true (the
truth of the second test is not necessary, and the fact it is an error
doesn't change the final result).
If A1=1 and A2=0, then A3 returns FALSE, because the first test is false and
the second test is an error (treated as FALSE by the 0 value it was assigned
in the second IF-test).

Perhaps =ISERROR(OR(cond_1,cond-2,....cond_N))
This returns TRUE if any of the OR conditions is an error, FALSE if not.

Tyrp

Now that you made me think about it...

=OR(if(iserror([test1]),0,[test1],if(iserror([test2]),0,[test2],...)

That solved my problem, even though not in a very elegant way...

Cheers.

=OR(TRUE,FALSE,FALSE,FALSE) evaluates as TRUE;
=OR(TRUE,FALSE,FALSE,#ERROR)
evaluates as #ERROR. Perhaps you want to enclose your formula in an
ISERROR
function (pre-Excel 2007) or IFERROR (Excel 2007)

 

[Tyro]

You could do me a favor. The formula is can be reduced for Excel 2007. Are
you using that? Or do I have to provide two formulas, Excel 2007 and
previous versions?

Tyro

Ho! I guess I must have explained something unclearly: I didn't want to
know
whether or not there was an argument that was an error, I only wanted the
function to return TRUE if any of its argument were true, regardless of
possible error value of other arguments, for I thought as soon as one of
its
arguments is true, the OR should also be true. And for the way I'm using
it,
the errors could be treated as false values...

The function I mentioned in my previous post was intended to return
something like the following:

A3: =or(if(iserror(A1=0),0,A1=0),if(iserror(A1=A1/A2),0,A1=A1/A2))

If A1=0 and A2=0, then A3 returns TRUE, because the first test is true
(the
truth of the second test is not necessary, and the fact it is an error
doesn't change the final result).
If A1=1 and A2=0, then A3 returns FALSE, because the first test is false
and
the second test is an error (treated as FALSE by the 0 value it was
assigned
in the second IF-test).

Perhaps =ISERROR(OR(cond_1,cond-2,....cond_N))
This returns TRUE if any of the OR conditions is an error, FALSE if not.

Tyrp

Now that you made me think about it...

=OR(if(iserror([test1]),0,[test1],if(iserror([test2]),0,[test2],...)

That solved my problem, even though not in a very elegant way...

Cheers.

:

=OR(TRUE,FALSE,FALSE,FALSE) evaluates as TRUE;
=OR(TRUE,FALSE,FALSE,#ERROR)
evaluates as #ERROR. Perhaps you want to enclose your formula in an
ISERROR
function (pre-Excel 2007) or IFERROR (Excel 2007)

 

[Tyro]

I explained to you earlier, Excel is not a "sophisticated" programming
language. It evaluates "ALL" conditions in IF, AND, OR etc then makes a
choice.
You, in your human mind might terminate the evaluation process earlier, but
Excel does not. Learn to live with it until Bill Gates the Chairman and
Chief Software Architect at Microsoft learns the basics of logic.

Tyro

Perhaps =ISERROR(OR(cond_1,cond-2,....cond_N))
This returns TRUE if any of the OR conditions is an error, FALSE if not.

Tyrp

Now that you made me think about it...

=OR(if(iserror([test1]),0,[test1],if(iserror([test2]),0,[test2],...)

That solved my problem, even though not in a very elegant way...

Cheers.

=OR(TRUE,FALSE,FALSE,FALSE) evaluates as TRUE;
=OR(TRUE,FALSE,FALSE,#ERROR)
evaluates as #ERROR. Perhaps you want to enclose your formula in an
ISERROR
function (pre-Excel 2007) or IFERROR (Excel 2007)

 

[FiluDlidu]

I am using Excel 2003 and I have never worked with the 2007 version, but only
saw it through someone else computer, with a very brief presentation of his
first feelings about it as compared to the 2003 version. Overall, he seemed
to think it had some good new features, but had lost some others that he
liked better in the previous version, and he had a mixed feeling about the
2007.

I am totally unknowledgable about its features regarding compression of
formulas from previous versions.

Did you mean that the formulae you gave me would have worked if I had been
using Excel 2007?

 

[Tyro]

No, Excel 2007 has new things that do not work in previous versions. It is
important to state what version of Excel you are using when you ask a
question in the newsgroups.

Tyro

 

[Dana DeLouis]

A2: =OR(A1=1,A1=2/0)

...A2 will return an error despite the fact the first argument returned

As a side note, I don't believe Excel follows that convention.
Maybe something for reference...

Short-circuit evaluation
http://en.wikipedia.org/wiki/Short-circuit_evaluation

Certain Math programs will bail out at the first statement that guarantees a
valid statement as well, but not Excel.
Most functions that generate an error in Excel usually return an error (ie
=SUM(1+2/0).
My 2 cents is that this is probably a good decision for Excel in a
Spreadsheet environment.
Excel can have many 1,000's of dependent cells, and I think Excel was
designed to flag errors as early as possible.

 

[Ragdyer]

<<<"It evaluates "ALL" conditions in IF, AND, OR etc then makes a
choice.">>>

You should not include If() in your blanket statement.

=If(a1=1,"OK",if(a1=2,"NG",If(a1=2/0,"Neither")))

Try 1 or 2 in A1 and you'll see that If() *stops* evaluating at the first
True it finds.

Also, your "etc." is too encompassing and needs elaboration!

For example, Lookup() goes right by those #Div/0! errors, and completes the
formula calculations:

=Lookup(2,1/(A2:J2<>""),A2:J2)
Leave some blank cells in the A2:J2 range, and Lookup() will bypass those
#Div/0! errors that are in the lookup vector.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I explained to you earlier, Excel is not a "sophisticated" programming
language. It evaluates "ALL" conditions in IF, AND, OR etc then makes a
choice.
You, in your human mind might terminate the evaluation process earlier, but
Excel does not. Learn to live with it until Bill Gates the Chairman and
Chief Software Architect at Microsoft learns the basics of logic.

Tyro

Perhaps =ISERROR(OR(cond_1,cond-2,....cond_N))
This returns TRUE if any of the OR conditions is an error, FALSE if not.

Tyrp

Now that you made me think about it...

=OR(if(iserror([test1]),0,[test1],if(iserror([test2]),0,[test2],...)

That solved my problem, even though not in a very elegant way...

Cheers.

:

=OR(TRUE,FALSE,FALSE,FALSE) evaluates as TRUE;
=OR(TRUE,FALSE,FALSE,#ERROR)
evaluates as #ERROR. Perhaps you want to enclose your formula in an
ISERROR
function (pre-Excel 2007) or IFERROR (Excel 2007)

 

[FiluDlidu]

A2: =OR(A1=1,A1=2/0)

As a side note, I don't believe Excel follows that convention.
Maybe something for reference...

Short-circuit evaluation
http://en.wikipedia.org/wiki/Short-circuit_evaluation

Certain Math programs will bail out at the first statement that guarantees a
valid statement as well, but not Excel.
Most functions that generate an error in Excel usually return an error (ie
=SUM(1+2/0).
My 2 cents is that this is probably a good decision for Excel in a
Spreadsheet environment.
Excel can have many 1,000's of dependent cells, and I think Excel was
designed to flag errors as early as possible.

Interesting

 

[Tyro]

Prove it

Tyro

<<<"It evaluates "ALL" conditions in IF, AND, OR etc then makes a
choice.">>>

You should not include If() in your blanket statement.

=If(a1=1,"OK",if(a1=2,"NG",If(a1=2/0,"Neither")))

Try 1 or 2 in A1 and you'll see that If() *stops* evaluating at the first
True it finds.

Also, your "etc." is too encompassing and needs elaboration!

For example, Lookup() goes right by those #Div/0! errors, and completes
the
formula calculations:

=Lookup(2,1/(A2:J2<>""),A2:J2)
Leave some blank cells in the A2:J2 range, and Lookup() will bypass those
#Div/0! errors that are in the lookup vector.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I explained to you earlier, Excel is not a "sophisticated" programming
language. It evaluates "ALL" conditions in IF, AND, OR etc then makes a
choice.
You, in your human mind might terminate the evaluation process earlier, but
Excel does not. Learn to live with it until Bill Gates the Chairman and
Chief Software Architect at Microsoft learns the basics of logic.

Tyro

Perhaps =ISERROR(OR(cond_1,cond-2,....cond_N))
This returns TRUE if any of the OR conditions is an error, FALSE if
not.

Tyrp

Now that you made me think about it...

=OR(if(iserror([test1]),0,[test1],if(iserror([test2]),0,[test2],...)

That solved my problem, even though not in a very elegant way...

Cheers.

:

=OR(TRUE,FALSE,FALSE,FALSE) evaluates as TRUE;
=OR(TRUE,FALSE,FALSE,#ERROR)
evaluates as #ERROR. Perhaps you want to enclose your formula in an
ISERROR
function (pre-Excel 2007) or IFERROR (Excel 2007)

 

[RagDyeR]

Prove what?

I'm sure you know how to evaluate formulas!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Prove it

Tyro

<<<"It evaluates "ALL" conditions in IF, AND, OR etc then makes a
choice.">>>

You should not include If() in your blanket statement.

=If(a1=1,"OK",if(a1=2,"NG",If(a1=2/0,"Neither")))

Try 1 or 2 in A1 and you'll see that If() *stops* evaluating at the first
True it finds.

Also, your "etc." is too encompassing and needs elaboration!

For example, Lookup() goes right by those #Div/0! errors, and completes
the
formula calculations:

=Lookup(2,1/(A2:J2<>""),A2:J2)
Leave some blank cells in the A2:J2 range, and Lookup() will bypass those
#Div/0! errors that are in the lookup vector.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I explained to you earlier, Excel is not a "sophisticated" programming
language. It evaluates "ALL" conditions in IF, AND, OR etc then makes a
choice.
You, in your human mind might terminate the evaluation process earlier, but
Excel does not. Learn to live with it until Bill Gates the Chairman and
Chief Software Architect at Microsoft learns the basics of logic.

Tyro

Perhaps =ISERROR(OR(cond_1,cond-2,....cond_N))
This returns TRUE if any of the OR conditions is an error, FALSE if
not.

Tyrp

Now that you made me think about it...

=OR(if(iserror([test1]),0,[test1],if(iserror([test2]),0,[test2],...)

That solved my problem, even though not in a very elegant way...

Cheers.

:

=OR(TRUE,FALSE,FALSE,FALSE) evaluates as TRUE;
=OR(TRUE,FALSE,FALSE,#ERROR)
evaluates as #ERROR. Perhaps you want to enclose your formula in an
ISERROR
function (pre-Excel 2007) or IFERROR (Excel 2007)