Conditional Format and OR

  • Thread starter Thread starter Dallman Ross
  • Start date Start date
D

Dallman Ross

In Excel 2002, I'm trying to color a cell based on this formula:

=pctRange<=1/8

That works fine in most cases. "pctRange" is a named expression and
evaluates correctly to a calculated number. However, sometimes
the the expression's denominator can be zero. In that case,
I'd still like the condition to work.

So I tried this:

=OR(ISERROR(pctRange),pctRange<=1/8)

But the condition still doesn't evaluate as true, even though
the ISERROR(pctRange) expression does evaluate as true.

How can I do what I want? That is, if pctRange <= 1/8 or
pctRange gives a #DIV/0! error, apply the conditional formatting.

Thanks,
Dallman
 
Hate to suggest a CF with IF, but try this

=IF(ISERROR(pctRange),TRUE,IF(pctRange<=1/8,TRUE))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob Phillips said:
Hate to suggest a CF with IF, but try this

=IF(ISERROR(pctRange),TRUE,IF(pctRange<=1/8,TRUE))
Click to expand...

Gack! It works, though. This also works:

=IF(ISERROR(pctRange),TRUE,pctRange<=1/8)

Too bad the OR thing didn't work -- it seems like it ought to.
Does anybody have any thought on why it doesn't?

Thanks, Bob. Much obliged.

=======================================================
 
Because both parts get evaluated in an OR, even when the first succeeds. So
when there is an error, the pctRange<=1/8 still evaluates, and this returns
the same error, thus the CF fails.

--
HTH

Bob

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