'OR' formula error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pmzipko@bloomu.edu - 27 Jun 2005 14:01 GMT

I have this formula that produces an error in a column:
=IF(OR((ROUND(N3,2)=ROUND(H3,2)), TRIM(H3)="$-"),"OK","ERROR!")
Columns H and N are both currency. If they're equal it's supposed to
display ok. If there is no value in H it's also supposed to show ok,
otherwise create an error.

The auditing steps look like this:
=IF(OR((26.8=ROUND("$- ",2)), TRIM(H3)="$-"),"OK","ERROR!")
=IF(OR((26.8=#VALUE!), TRIM(H3)="$-"),"OK","ERROR!")
=IF(OR((#VALUE!), TRIM(H3)="$-"),"OK","ERROR!")
=IF(OR(#VALUE!, TRIM(H3)="$-"),"OK","ERROR!")
=IF(OR(#VALUE!, TRIM("$- ")="$-"),"OK","ERROR!")
=IF(OR(#VALUE!, "$-"="$-"),"OK","ERROR!")
=IF(OR(#VALUE!, TRUE),"OK","ERROR!")
=IF(#VALUE!,"OK","ERROR!")
=#VALUE!

Why would the or statement return an error? Even though there is an
error in one part, the other returns true. It makes sense that as long
as one part is true the statement should be true, but that error seems
to make the whole thing false. Anyone know why this happens?

Reply to this Message

Bernie Deitrick - 27 Jun 2005 15:48 GMT

Prevent the error thrown up by "$-" being passed to ROUND:

=IF(OR(TRIM(H3)="$-",(ROUND(N3,2)=ROUND(IF(H3<>"$-",H3,0),2))),"OK","ERROR!"
)

HTH,
Bernie
MS Excel MVP

> I have this formula that produces an error in a column:
> =IF(OR((ROUND(N3,2)=ROUND(H3,2)), TRIM(H3)="$-"),"OK","ERROR!")
[quoted text clipped - 17 lines]
> as one part is true the statement should be true, but that error seems
> to make the whole thing false. Anyone know why this happens?

Reply to this Message

JE McGimpsey - 27 Jun 2005 15:54 GMT

XL Functions generally pass through errors, so if one argument to the
OR() function is #VALUE!, then OR() will return #VALUE! That's just the
way things work.

If column H is currency, why do you have Text in H3? Is it supposed to
represent zero? If so, return zero in H3 and format it to display $-,
e.g.:

$#,##0.00_);$(#,##0.00);$\-_);@_)

Reply to this Message