1 2 Previous Next 17 Replies Latest reply on Dec 16, 2016 8:58 AM by jbante

# WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

### Summary

WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

### Product

FileMaker Pro

### Version

13.0v3 / 11.0v4

### Operating system version

Mac OS X 10.9.3 / Window 7

### Description of the issue

Here's a math operation :

1-1,975/6,5833333333333333 = ,7

Now let's multiply that result by 100, so we should get 70 :

(1-1,975/6,5833333333333333) *100 = 69,9999999999999998

So we went from a round result to one with lots of decimals.

So now let's do a Floor on this result :

Floor( (1-1,975/6,5833333333333333) *100) = 69

Doing the same operation in Mac OS X Alfred calculator gives correct result. Perhaps the *100 is correct but the first result is false

Test ran on Mac OS X.9.3, FMPA 13.0v3

*** IMPORTANT UPDATE ***
THIS OCCURS IF DECIMAL SEPARATOR IS COMMA

1-1.975/6.5833333333333333)*10 is ok (of course you've to set your OS with . as decimal separator).

So the bug a appears on Languages that use COMMA as decimal separators

### Steps to reproduce the problem

Do the above math operation

70

### Actual result

69,9999999999999998

### Configuration information

FMPA 13.0v3 / 11.0v4 (didn't try 12)
Mac OS X 10.9.3 / Windows Seven
OS decial separator is a COMMA not dot

### Workaround

None

• ###### 1. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

strange... I copied the formula into a FileMaker textfield and calculated using the 'calculate' function - result: 70

must be a problem with decimal values

• ###### 2. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

Just noticed I posted it in french, which as comma decimal separators.

Here are the calc with us decimal separators

1-1.975/6.5833333333333333 = .7

(1-1.975/6.5833333333333333) *100 = 69.9999999999999998

Floor( (1-1.975/6.5833333333333333) *100) = 69

Markus, you have to test this one (1-1.975/6.5833333333333333) *100 the problem occurs when multiplying
• ###### 3. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

Thanks to your post Markus I noticed that the issue only trigger if the decimal separator is a COMMA

Big thanks

• ###### 4. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

Glitchtracker:

The issue occurs regardless if comma or period is used as the decimal point.  Since FileMaker Pro has greater precision than other applications/utilities, it is less forgiving.  With your example, since 6.58333333... is unending, you are slightly rounding down.  However, if you add one more "3" to the value 6.58333333...., it will then display 70.  That is, 6.58 followed by fifteen 3's.

TSGal
FileMaker, Inc.

• ###### 5. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

Sorry,

1-a/b =c

then (1-a/b) * 100 has to be equal, in all system in the world, to c*100 otherwise that's a BUG.

If Filemaker has greater precision, then c should be higher precision.

• ###### 6. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision
Furthermore, I can't "add a 3", because filemaker it's filemaker that decided to store it that way :

actually user input 7,9 in a dialog then there's a set field that sets a field to 7,9/1,2

so the set fields stores in the filemaker database 6.5833333333333333

If Filemaker is more precise than other tools, then it should have stored another 3. But of course you can't store that much decimal

HOWEVER, and here lies the BUG to me, Filemaker calc engine should be aware of its precision limits and then do the calks accordingly.

It seems to me that there's inconstancy here
• ###### 7. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

Glitchtracker:

Since computers are binary, most decimal numbers cannot be truly represented in accurate binary form, so there is some error.  If you use your calculation of (1 - 1.975 / 6.5833333333333333), the last digit in the rounding sequence will round up to the next value, and 0.7 will display, even though it is slightly lower internally.  If you combine this answer by multiplying by 100 in the same formula, then the precision from the last digit from the first part of the calculation is shifted by two decimal places.  Thereby giving the answer you see.  If you add one more 3 to that initial value, it pushes the edge of the precision to round up to 70.

Additionally, using your initial calculation without multiplying by 100 (leaving 0.7), and then create another calculation field that multiplies 100 by that first value, you will then get the desired result of 70.  That is because the two calculations are acting separately and not as one unit.

If I set the precision on the calculation field to 25 digits, using your calculation, I show 69.9999999999999998000000000.

Interestingly, I launched Excel and created a cell with the same calculation and set the precision to 25 digits, it shows 70.0000000000000000000000000, even though it is incorrect.  In fact, it will show 70 if you reduce the number of 3's by two to 12.

If you want more about converting decimal to binary, there are several articles that can be found.  Doing a quick search on the internet, I found:

http://cs.furman.edu/digitaldomain/more/ch6/dec_frac_to_bin.htm

TSGal
FileMaker, Inc.

• ###### 8. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

Excel does it right

A1=1-1,975/6,58333333333333

100*A1 = 70

So that's a bug.

And this bug practically kills the Floor function (but of course can have even worse consequences)

• ###### 9. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

I don't understand why Filemaker internally computes to whatever precision let's say 25, but decides to store only 14 decimals

So if filemaker internal calc engine pecision is 25, then it should store 25 decimals. If it can only strs 14 decimals, so it must calc with 14 decimals.

You may think Excel behavior is wrong, but actually it's right : because it corrects what's it's fraction to binary limitation introduces

Moreover having 2 fields not giving the same results than one is VERY strange

• ###### 10. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

Glitchtracker:

FileMaker will store 16 decimals.  Using SetPrecision, you can increase this number.  For example, if you create the calculation:

SetPrecision ( Pi ; 100 )

... the resulting calculation will show 100 digits of Pi.

Therefore, if the user enters 7,9 and you divide by 1,2, then use SetPrecision on that calculation to 20 places (as overkill).  Then, when you perform the calculation again, you will see 70.

TSGal
FileMaker, Inc.

• ###### 11. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

Apparently MySQL does it right also

You can't have a system that behaves differently doing computation and storing data. That's a BUG.

And how should I do my floor ?

• ###### 12. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

The only valid way is to store as the same precision that Filemaker computes.

So what's the computational precision of Filemaker ?

Do FMI really thinks that's it good to force all user in the world to use that set precision calc just to do VAT to non VAT price conversion. I'm not launching a spaceship.

• ###### 13. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

I just independently ran into the same issue, and I'm satisfied by TSGal's response. The issue as she describes it is not that storage precision and calculation precision are different. It's that calculation/storage precision is binary, whereas display precision is decimal. It isn't mathematically possible for precisions in those bases to map cleanly to each other.

There's another behavior that may be affecting results that TSGal did not comment on, and can easily lead to the different results you get between FileMaker and other environments. FileMaker uses fixed-point arithmetic. (See: About number fields.) Most other software that doesn't place a high priority on the finer points of numerical computing uses floating point arithmetic, which sometimes leads to different results, and can be similarly "wrong" in different calculations. Both approaches are only approximations of rational- and real-number arithmetic. Only symbolic arithmetic, limited to specialized software, gets it right every time.

1 of 1 people found this helpful
• ###### 14. Re: WRONG MATH : Multiplying by 100 change the result of decimal numbers, loss of precision

Thank Jbante, but I think it's annoying that Filemaker gives different results than Excel, Mysql and what we intuitively expect. If none are absolutely correct, then Filemaker is in the bad, or impractical side of wrong

1 2 Previous Next