There appears to be an error in the "Fixed number of decimal digits" option under "Format as decimal" on FM's Format > Number... setting.

Here's the scenario. I use FM to generate price grids that MUST be accurate to the penny. As an example, here are three side-by-side calculations that produce different results when the above setting is invoke. (The "x 12 / 26" portion of each calculation is used to change "per month" figures to "biweekly" figures, or 26 times per year.)

A. 9.01 x 12 / 26 x 3.00 displays 12.475385 when "Fixed number of decimal digits" is set to 6, and it displays 12.48 when it is set to 2. Normal rounding dictates that the 5385 forces the 12.475358 to round up to 12.48.

B. 9.01 x 12 / 26 x 3.50 displays 14.554615 when "Fixed number of decimal digits" is set to 6, and it displays 14.55 when it is set to 2. Normal rounding dictates that the 4615 forces the 14.554615 to round down to 14.55.

Those are both correct. Here's where the problem occurs:

C. 9.01 x 12 / 26 x 3.25 displays 13.515000 when "Fixed number of decimal digits" is set to 6, and it displays 13.51 when it is set to 2. Normal rounding dictates that the 5000 forces the 13.515000 to round up to 13.52. But it does not - it displays 13.51. This is apparently the case any time the unformatted number being displayed ends in a decimal with a 5 and a string of zeroes at the end.

Is anyone familiar with this issue? Or, can someone point out the error in my thinking? Bear in mind, there is no rounding involved, outside of the internal rounding FM does to display long decimal numbers out to less places.

C. 9.01 x 12 / 26 x 3.25 displays 13.515000 when "Fixed number of decimal digits" is set to 6, and it displays 13.51 when it is set to 2.

Can you post a file showing this?

Unfortunately, it involves several calculations that draw on multiple database files, ending up in one primary database which is over 400MB in size.

I have discovered some additional information that might shed some light on this, however.

If it was merely a formatting issue as I suspected, I should be able to create a plain number field, enter 13.515000, set the Fixed number of decimal digits to 2, and have it incorrectly display 13.51. It does not. It correctly displays 13.52. Therefore, it must be somewhere in the calculation.

Here is a cleaned up version of the calculation used to display the desired results:

Extend ( Flag A ) * Extend ( Base Rate ) * Extend ( Rate Factor ) * Benefit List

And these are the fields used in the calculation:

- Flag is simply a calculation that, in this scenario, returns a 1.

- Base Rate is a calculation that, in this scenario, returns the value from a number field containing 9.01.

- Rate Factor is a calculation that, in this scenario, returns 12/26.

- Benefit List is repeating field stored globally that contains the values 3, 3.25 and 3.5 in the repetitions relevant to the examples above.

So, the formula becomes this:

Extend ( 1 ) * Extend ( 9.01 ) * Extend ( 12/26 ) * ..., 3, 3.25, 3.5, ...

And that results in the following repeating field values appearing in my layout:

Formatted to 6 decimal digits: 12.475385 .. 13.515000 .. 14.554615

Formatted to 2 decimal digits: 12.48 ......... 13.51 ......... 14.55

Again, there is no rounding - only formatting to a set number of decimal digits - and the Extend function applied to calculations other than the repeating field values - and however FM treats the 12/26 passed on in the Rate Factor calculation. It would now seem that the issue is in one of those areas.

The formula is rather meaningless, since we don't know the actual data being used. If the data were EXACTLY what you say it is, you wouldn't see this problem. I suggest you find out what the real result of the calculation is - by clicking into the field, for example.

Note that:

Round ( 13.5149995 ; 3 )

returns 13.515, while:

Round ( 13.5149995 ; 2 )

returns 13.51

Both results are correct.

Thank you. I was coming to a similar conclusion from a different direction.

The offending value taken out to 14, 15, 16, and 17 places displays different values:

13.51500000000000

13.514999999999999

13.5149999999999989

13.51499999999999890

Your suggestion to click into the field showed 13.5149999999999989. So, normal rounding to 2 places would be 13.51.

Unfortunately, my math is being check in Excel - which says the answer is 13.515000000000000, or 13.52 out to 2 places.

Any thoughts?

Unfortunately, my math is being check in Excel

You can either round (and I mean round, not format) to the same precision that Excel does, or stick to your guns.

Sorry it took so long to get back to you.

You're exactly right. Excel only calculates out to 15 places, so apparently it will never see the accuracy that FileMaker can show at the 16th decimal place - which in this case causes normal rounding to shift from round up to round down.

Thanks for your help.

:angry: Okay, forget everything else. FileMaker is wrong after all (or at least the way I am using it is).

Integral to the whole formula is the factor 12 / 26 which is contained in a calculation field which converts an amount from per month to biweekly in this example. A scientific calculator shows the quotient to be a repeating decimal: .461538461538461538...

FM truncates this to 16 places to .4615384615384615.

So, using this field in the main calculation results in a smaller number than running the calculation out as 9.01 * 12 / 26 * 3.25. Therefore, FM is providing an incorrect answer the way it is being used. It looks like my task is to rewrite the formula to avoid this issue.

Have a look at the attached.

precision.zip

Okay. So, it looks like I can add the SetPrecision function to my monthly-biweekly calculation and everything will be right with the world.

Thank you!

I believe it would be better to make the entire calculation in a single field. After all, 9.01 * 12 / 26 * 3.25 is EXACTLY 13.515.

It probably would.

Unfortunately, different scenarios result in different values or calculations being applied to each of the parts of the main formula: Flag A, Base Rate, Rate Factor & Benefit List. I think the SetPrecision function will do the trick - once I decide how many places I need to go out to.

different scenarios result in different values or calculations being applied to each of the parts of the main formula:

That's where the Let() function can be very handy.

Thanks. I'm learning, albeit slowly...