MOD function computation error

[Guest]

Excel 2003. Input in B1:
=MOD(A1*100,10)

Input in A1:
622.2 -->

The result is something very small but not 0 as it's supposed to be.
Input 622.8 and there it comes all right. For some strange reason, it would
seem like numbers less than 512 with one decimal - they compute correctly.

Interesting still:
655.1 goes wrong and 655.4 goes ok.

Remark: if I try with pre-multiplied numbers everything's fine. So if I say:
=MOD(A1,10) where A1=65510
it gives clean 0.

If I'm wrong somewhere in operator precedence or something, please relieve
me of this pain. I have created a ton'of page report and everywhere's this
issue.

Didn't know where to write to Microsoft bug-report or something. Thank you
in advance.

 

[Biff]

Hi!

It's rounding issue of the MOD function

Try this:

=MOD(ROUND(A1*100,0),10)

Biff

 

[Andy Wiggins]

There have been issues with this function for years:

http://www.bygsoftware.com/issues/modbug.html

 

[Jerry W. Lewis]

MOD is working exactly as it should. The "problem" is that most
floating point numbers (including .2) have no exact binary
representation (just as 1/3 has no exact decimal representation). When
you have to approximate your inputs, that the output is only approximate
should be no surprise.

100 times the binary approximation to 622.2 is
62220.0000000000072759576141834259033203125
....

If x has a fractional part but x*100 is supposed to be an integer, then
you need to use =MOD(ROUND(x*100,0),10) to ensure that you get rid of
any residues of the original binary approximation to x.

Jerry

 

[Bruno Campanini]

MOD is working exactly as it should. The "problem" is that most floating
point numbers (including .2) have no exact binary representation (just as
1/3 has no exact decimal representation). When you have to approximate
your inputs, that the output is only approximate should be no surprise.

100 times the binary approximation to 622.2 is
62220.0000000000072759576141834259033203125
...

If x has a fractional part but x*100 is supposed to be an integer, then
you need to use =MOD(ROUND(x*100,0),10) to ensure that you get rid of any
residues of the original binary approximation to x.

Jerry

Or, you can use VatType Currency (only four decimals) or Decimal.
They are not affected by floating point bugs.

Bruno

 

[Jerry W. Lewis]

Or, you can use VatType Currency (only four decimals) or Decimal.

Clarification: these require use of VBA. Also, note that the VBA Mod
operator only works with integers (and rounds floating point inputs to
integers before doing the calculation). If the OP had rounded to
integers then the worksheet MOD function would have given the expected
answer, as I previously noted.

They are not affected by floating point bugs.

To call this a "bug" stretches the definition of "bug". Is it a "bug"
that you cannot write 1/3 exactly as a decimal fraction? If so, a bug
in what? The field of mathematics? The paper? The pencil? You for
eventually stopping?

Decimal data types are not a panacea, they still only offer finite
precision, and so merely change the particular numbers where these kind
of issues occur.

Jerry

 

[Bruno Campanini]

[...]

To call this a "bug" stretches the definition of "bug". Is it a "bug"
that you cannot write 1/3 exactly as a decimal fraction?

No, but there is somwhere a "bug" when adding 100 times
0.01 to 0 I can't get 1.
If you don't want to use the word "bug" suggest me what is
the proper word.

Excited?
Don't have too many coffees!

Bruno

 

[Guest]

No, but there is somwhere a "bug" when adding 100 times
0.01 to 0 I can't get 1.

As I pointed out in my original post to this thread, "most decimal fractions
have no exact binary representation" and so must be approximated. The
approximation to 0.01 is
0.1000000000000000020816681711721685132943093776702880859375, so it should
not be surprising that when you add 100 values which are each slightly larger
than you expected, that the total will also be slightly larger than you
expected. Taking account of the intermediate roundings (also covered by the
IEEE standard) that will occur, the sum of 100 such approximate values will be
1 + 6.661338147750939242541790008544921875E-16
The Excel formula =(total-1) will return 6.66133814775094E-16 (Excel's
documented 15-digit limit) indicating that the arithmetic is working exactly
as it should.

As I also said in my original post "When you have to approximate your
inputs, that the output is only approximate should be no surprise."

You can see the same phenomenon in decimal (where it may have more
intuition). If you use the VBA Currency data type (4 decimal places)
x = CCur(1 / 3)
total = x + x + x
The value of total will be 0.9999, not 1.0000. Surely you will agree that
there is no bug there.

If you don't want to use the word "bug" suggest me what is
the proper word.

I am open to suggestions, but calling correct math based on necessary
approximations to inputs a "bug" is rather like complaining that it shouldn't
rain. It seems a pointless waste of effort that would be better directed at
learning to predict when it will rain and how to protect yourself when it
does.

If you cannot tolerate slight approximations beyond the 15th significant
figure, then you should stick to integer calculations where those
approximations can be avoided. If you continue to work with decimal
fractions, you should be aware that digits beyond the 15th may not be what
you expect, and subtractions (including the MOD function) may remove some or
even all of the leading digits that originally prevented you from seeing the
approximation. Rounding calculated values to the number of places that you
can be sure of, will usually help you avoid surprises.

These are issues that have been around as long as computers have been doing
finite precision mathematics (long before there was a Microsoft), and are not
unique to Excel.

Jerry