Error in a simple calculation

[h2o]

When I write this simple subtraction

128.28 - 128.27

The result is not 0.01

But 0.0100000000000051

I can indeed round the result to 2 decimals, but I don't understood why FileMaker give this wrong (strange) result.

Has somebody read something about this ?

[printit]

I've sene FM mis calculate before, If my memory is correct

it did involve subtraction with decimals.

[LiveOak]

This is normal and caused by the fact the computers perform calculations not in decimal (where the result would be exact) by in binary. Before subtraction both of the numbers are converted to binary numbers with a limited (but large) number of digits. Except for multiples of two, the binary numbers don't exactly represent the decimal numbers regardless of how many binary digits are used, Just as you can't exactly represent 1/3 in decimal notation (.3333333333333 is still not exact).

This was much more obvious when we all wrote the programs ourselves and maybe even had to write the conversion routines to parse the numbers the user entered and convert them to binary (fixed point or floating point) representations. You're just getting a tiny look "under the hood".

-bd

[Vaughan]

Didn't the US Patriot missiles suffer a problem because the designers performed math in decimal numbers, which are irrational in binary which the cpu worked in. Over time the errors compounded and the targeting was off. Solved by resetting the cpu to zero the error.

[h2o]

Exact.

I receive the answer. It's on

FileMaker Tech Info

Thank's.

[Vaughan]

From the TechInfo web site, Article 104170 (thanks Jean-Marie) :

--

Rounding Problems with Power Macintosh and Windows

Subtracting certain values with a calculation may result in rounding problems on Power Macintosh and Windows machines. For example:

949.8 - 904.2 = 45.5999999999999

116.26 - 118.99 = -2.72999999999999

126.96 - 119.29 = 7.66999999999999

101.99 - 101.98 = .0099999999999

101.91 - 105.90 = -3.99000000000001

71.99 - 70.98 = 1.00999999999999

408.8 - 400.2 = 8.60000000000002

You could try adding a very small number to each value (i.e. 0.0000000001) and then subtract these numbers but that only takes care of the first three situations listed above.

In order to handle every situation you will need to use the Round function. When you specify the precision, make sure you use a very high number but smaller than the largest imprecision. So, your formula might look like this:

Round(Num1 - Num2, 8)

The above examples will result in the correct values like so:

949.8 - 904.2 = 45.6

116.26 - 118.99 = -2.73

126.96 - 119.29 = 7.67

101.99 - 101.98 = .01

101.91 - 105.90 = -3.99

71.99 - 70.98 = 1.01

408.8 - 400.2 = 8.6

Notice that all of the trailing zeros are dropped so the numbers display nicely.

Note: Rounding problems with certain number combinations happen with virtually all processors and operating systems. The problem occurs when converting base 10 numbers to base 2 and back.

THIS DOCUMENT CONTAINS THE INFORMATION CURRENTLY AVAILABLE CONCERNING THE BEHAVIOR OF FILEMAKER'S PRODUCTS AND IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. FILEMAKER, INC. DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL FILEMAKER, INC. OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS, PUNITIVE OR SPECIAL DAMAGES, EVEN IF FILEMAKER, INC. OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY

[LiveOak]

CONGRATS ON POST 1000 !! Your support of the forum is much appreciated.

-bd