# Excel Import - Wierd Decimal or Precision Conversion

## Recommended Posts

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 by Guest

##### Share on other sites

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.

##### Share on other sites

...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)

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

How about if you export to tab-delimited text?

##### Share on other sites

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

##### Share on other sites

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.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...