Decimal error in calculated value - Bug?

[Jason Lane]

In one of my solutions, I came across an error occurring in some of my calculation fields. I thought they must have been caused by something I was doing wrong. However, as a test I created a sample db just to see if I could easily replicate the problem, and it turns out I can.

This is an extremely simple calculation, but the result returned by FM is not 100% accurate. That is to say, the result is accurate to maybe 16 decimal places, but that's not quite the same as being 100% accurate.

Here's how I replicate the situation:

A number field called Value1

A number field called Value2

A calc field called Result, = ( 1 / Value1 ) * Value2

Now, if you enter in the same number for both Value1 and Value2, the end result should be 1, no matter what the number is that you've entered. However, depending on the number you enter (seemingly dependent on the number of digits), FM sometimes returns values such as 0.9999999999999995

Is anybody familiar with this problem? Is it a known bug?

Although the error is clearly very tiny in numerical terms, it can add up to a significant error when a lot of such values are added together, or used in other calculations.

Attached is the sample db I created to replicate the problem.

DecimalError.fp7.zip

[comment]

This is quite common in applications that use the IEEE floating-point standard (and practically all applications do). See, for example:

http://www.cpearson.com/excel/rounding.htm

Luckily, in your case there is a simple remedy. Change your formula to:

Value2 / Value1

[Jason Lane]

The calculation used in my example file was really just to illustrate the kind of results that I had noticed. It may or may not be quite so simple to get around the errors in the calculations in my actual db. At least now I know it's a limitation that can't be helped, and I'll just have to program around it.

Thanks for clearing this up.

[The Shadow]

The calculation math normally computes things with 16 digits of precision, but you can request more if you like. As comment points out, it is always better to multiply in your numbers (which is lossless) before dividing, that's a standard rule to help reduce numerical error.

In FileMaker, you can also control the precision desired with SetPrecision, such as:

   Result, = SetPrecision( ( 1 / Value1 ) * Value2; 300 )

Will use 300 digits to the right of the decimal point during the computation, including all intermediate results.

[comment]

SetPrecision() can help when the error is due to rounding very small numbers. But it can only reduce an error caused by converting very small numbers between bases. Even:

SetPrecision ( 1 / 1234 * 1234 ; 400 )

will not return a perfect 1.

I wonder if something like this couldn't be used with the more problematic formulae:

Round ( SetPrecision ( ; 17 ) ; 16 )

The assumption here being that the error will never be larger than 0.5 * 10^-17.

[Jason Lane]

I did a bit of experimenting in my sample db, and I found that a precision of 17 with rounding to 16 did not reliably produce a perfect 1 in each case. However, using a large precision with a relatively small rounding seems to work.

Eg.

Round ( SetPrecision ( ( 1 / Value1 ) * Value2 ) ; 300 ) ; 10 )

gives me a perfect 1 with every value I've tried so far.

[comment]

I could be easily wrong about this, but in any case my assumption was regarding cases where the error was due to base conversion - not due to quantizing very small numbers (to ANY grid, decimal or binary).

Using brute force is very likely to succeed, but I would suggest optimizing your formulae nevertheless.