Excel Import - Wierd Decimal or Precision Conversion

[CCBtx]

I am Importing an Excel Sheet.

I have a calculated column in Excel called "Amount Financed".

In Excel, a particular cell is 17,720.8800000000000

That imports into FileMaker as

17720.880000000001

Another one is:

Excel Source: 20,139.010000000000

Filemaker Import: 20139.009999999998

It seems as if the excel cell has .00 cents, it imports correctly. However, if there are cents, it performs this funky precision function.

Any ideas?

Edited April 26, 2007 by Guest

[Ender]

Aaah, floating point math.

You can clean up the data post-import by adding a round() or truncate() to the field's auto-enter calc, or simply change the field's number format on the layout to show only two decimals.

[CCBtx]

...so (not to sound stupid)

is that an error/bug/feature?

Where is the predictability? It does it on some and not on others (after further review, I was incorrect about the 0 cents)

[Ender]

It's all about how the particular processor (FPU?) handles floating point computations, and how the results are interpreted by the different programs. Usually those odd remainders occur as a result of division or square roots.

In your Excel data, the numbers that appear are rounded to the nearest cent, but that rounding appears to be lost when that data gets exported. It's not a bug necessarily, it's just how remainders are interpreted.

Like I said, just enforce the rounding in FileMaker. I suppose you could do the rounding with a function in Excel, then the data that's exported would match exactly.

[CCBtx]

Ok.... but as a point of reference, I am doing the rounding in excel... even truncating... and fm is still importing as an unrounded number

[Ender]

How about if you export to tab-delimited text?

[CCBtx]

I think that would certainly work... however, it would add and extra step and a level of complexity that I don't want to code for

[Ender]

Sorry, I meant what do you see when you export to tab-delimited text? What's in the text file? Is the data in the non-rounded format? If so, then it's an Excel issue.