Mod () rounding error?

[QuinTech]

Wow. I can't imagine i'm the first person to notice this, but i haven't found a reference to it here or anywhere else and i hope someone has a solution.

FM version 6.04. I have a calc field, set to text, equal to:

NumToText ( Mod ( theNumberField , 1 ) )

If theNumberField = .1, the calc comes out right. If theNumberField = 1.1, it comes out right. BUT! If theNumberField = 11.1, then the result of the calc field is 0.099999....

WTF?

Make the calc a number field, and it's fine. Make it text but take out the NumToText part, and it's fine. Make it number and take out the NumToText part, and it's fine. But with all the conditions above, it comes out wrong, as do 11.3, 11.6, and 11.8.

Am i being a doofus? Or is this really a bug?

J

[Lee Smith]

Hi Jerry,

I'm not sure what the results should be, but I created a file and there is a difference between 4 and 6. The attachment is a Screen Shot of how they look in 4, 5 and 6.

HTH

Lee

[QuinTech]

Thanks for the confirmation that i'm not crazy, Lee. (Well, i guess you didn't say that, but i'll just assume. )

FWIW, i tested it in v7 and was not able to reproduce the error, probably because the NumToText function is no longer there, which seems to have been the crux of the problem.

[Lee Smith]

Hi Jerry,

i'm not crazy

a Okie Dokie.

the NumToText function is no longer there, which seems to have been the crux of the problem.

That was my thoughts too. Now all we need is for one of the geniuses like Ray, JT or Ugo to come along, and explain it too us in laymen's terms, why this is doing this.

BTW, I took this one step further and separated the calculation into separate fields and got the same result.

Lee

[QuinTech]

Pursuing this thread further is probably foolhardy, but it's like my white whale (at least for today).

This page and others seem to suggest that the issue has to do with the fact that certain numbers cannot be represented accurately in base 2, which makes a certain amount of sense to me. Filemaker is built in C, right? I'm pretty sure C uses floating-point decimals, so that could be the issue. I don't exactly follow the pattern, though: It looks like the error only kicks in when there are at least three total digits, and then only in decimals ending in 1, 3, 6, and 8.

http://weblogs.asp.net/ericlippert/archive/2003/09/15/53000.aspx

http://www.camcode.com/discussion/_disc1/0000000d.htm

[RalphL]

The problem is round off error whne converting a binnary number (base 2) to a decimal number (base 10). This problem becomes more servere when the calculation used multiplation or division. The Mod fuction is a form of division. The Mod function is an integar function and should not be used as it is in this problem. I prefer using theNumberField - Int ( theNumberFiel ).

You can also use the Round Function to improve your calculation.

[Ugo DI LUCA]

TheNumberField-Int(TheNumberField) still gives incorrect behaviour actually when used in a calculation, while the same process, scripted, works...

12,1 will turn to 0.0999999999999996 instead of 0.1 while SetField [ ] will lead to 0.1

As we already somewhere faced the problem, I had kept this extract of a dsicussion on the Expert list.

Here's the quotes from another list about quite the same. Might be of interest.

The Question

" I am no mathematician but this seems peculiar...

I have a test database with two fields:

1. Number, Type: Number

2. Calc, Type: Calculation = NumToText(Mod(Number,1))

a) When I type 1.7 into Number the calculation field displays ".7"

: When I type 43.7 into Number the calculation field displays

".700000000000003"

c) When I type 47.3 into Number the calculation field displays

".299999999999997"

I would expect .7 for : and .3 for c)

Why do I get these results??? "

The Answer , by Charles Durrwachter.

"The short answer is that numbers have a 14 place limit in FMPro.

Numbers under .x5 round down, numbers > .5 and < 1 round up. [Where x is any number of digits up to 13] In the grand scheme of things as far as computers are concerned they average out. [Actually it was a limit of the 16 bit and the 32 bit emulators OS systems]

All computers really do is add and subtract at their basic level; and a

decimal value is a calculated value for a computer. Any calculated value

will show the typical compound error accumulation of

[-Queue-]

Substitute( " " & NumberField, " " & Left( NumberField, Position( NumberField, ".", 0, 1 ) - 1 ), "" )

It's cheesy, but it works.