Jump to content
Sign in to follow this  
CCBtx

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.