Conditional formatting producing inconsistant results?

[Craig Sink]

Hello,
I am attempting to format a cell and the results are inconsistant. The
formula looks like this:

=IF(MOD(C3,0.0005)>0,TRUE(),FALSE())

The results I get are inconstant. If the value of C3 is .2810 it treats it
as a true statement, which is not the case. However, most other values are
correctly analyzed. I have discovered the reason which is an error that
shows up in about the 17th decimal place. I corrected the problem by
changeing the formula to look like this:

=IF(ROUND(MOD(C3,0.0005),4)>0,TRUE(),FALSE())

Why the error, and is there a way to avoid this? The odd thing is that some
of the values which were analyzed correctly also had error, but it was in
the 18th decimal place.

Anyway, I've resolved the problem for now, just wondering where else the
error is going to cause problems.

Craig S.
FDI, Inc.

[Dave O]

Craig-
I've noticed this type of error in the past, too, and I'm curious to
see the responses that come from the Excel MVPs.

In the meantime, can I suggest a minor change to your formula: the way
it's currently set, it rounds the modulus to 4 decimal places, and I
think your intent was to round C3 to 4 decimal places:
=IF(MOD(ROUND(C3,4),0.0005)>0,TRUE,FALSE)

[StinkeyPete]

In excel the Number precision is only 15 digits

See the "Excel specifications and limits" help file.

[swatsp0p]

Craig, I'm not sure why you say Excel is inconsistant in its results.
The MOD of your example IS greater than zero
(0.0000000000000000216840434497101).
ANY value of which .0005 is not a factor WILL have a MOD>0.

So, to answer your question, this is not an error. To avoid this
outcome and if you only want accuracy to 4 decimal places, try your
formula as such:

Code:
--------------------
=IF(MOD(C3,0.0005)>0.0001,TRUE(),FALSE())
--------------------

or similar (using ROUND as you did accomplishes the same thing).

HTH

Bruce

--
swatsp0p

------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101
View this thread: http://www.excelforum.com/showthread.php?threadid=396109