'OR' formula error

  • Thread starter Thread starter pmzipko
  • Start date Start date
P

pmzipko

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?
 
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
 
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);$\-_);@_)
 
Back
Top