#Div/0 and Function OR

[dfuribe]

Hi, I hope someone can help me with this.

I'm using Excel 2003 and I have the following function:

=IF(A1>=0,A1/IF(OR(B1=0,ISERROR(B1)),AVERAGE($C$1:C4),B1),-A1/
IF(OR(ISERROR(B1),B1=0),AVERAGE($C$1:C4),B1))

The important thing here is that B1 is a standard deviation, and it
may take either real values (when more than 1 value is avalaible), 0
(when no values are available) or #DIV/0 (when only 1 value is
available).

By using the evaluate function option I can see that in cases where B1
is 0 the OR function basically compares within a TRUE and a FALSE
value, giving a TRUE as the answer (what I hope for), but when it
checks a #DIV/0 value it considers ISERROR(B1) as TRUE and B1=0 as
#DIV/0, and instead of generating TRUE as the result of the OR it
generates a #DIV/0 error.

Considering that the OR function should be TRUE as lonas any of the
values is TRUE, that should not happened. I've tried changing the
order of the arguments so that it does not need to continue checking
the other arguments as soon a TRUE is found, but Excel continue
checking the other arguments.

Thank in advance for any help you can provide me.

DF

 

[Bob Phillips]

The OR will evaluate both criteria and if one throws an error it throws an
error. It does not pass just because one is true.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[joeu2004]

I'm using Excel 2003 and I have the following function:
=IF(A1>=0,A1/IF(OR(B1=0,ISERROR(B1)),AVERAGE($C$1:C4),B1),-A1/
IF(OR(ISERROR(B1),B1=0),AVERAGE($C$1:C4),B1))

If you have some programming experience (sounds like you might), you
know that when you pass arguments to functions, all arguments must be
evaluated before the function can be called. In Excel, OR() is just
like any other function. It is not an operator, as in most languages.

However, I have learned that in Excel, IF() is treated differently.
The first argument (conditional expression) is evaluated, then
__either__ the second __or__ the third argument, but not both, is
evaluated based the true/false result of the first argument.

IMHO, the best solution is to avoid the #DIV/0 error in B1
altogether. Errors in spreadsheet are never pretty.

Alternatively, I believe the following is equivalent to your formula,
but it will avoid propagating the #DIV/0 error in B1:

=if(iserror(b1), abs(A1)/average($C$1:C4), if(b1=0, abs(A1)/average($C
$1:C4), abs(A1)/b1))

--or--

=if(if(iserror(B1),true,(b1=0)), abs(A1)/average($C$1:C4), abs(A1)/b1)

 

[dfuribe]

TThanks Joe, the second function worked flawlessly,

DF