I'm importing a simple Excel spreadsheet and have found a strange issue I can't work out - there's a column of numbers in the spreadsheet like this:

43.77

39.30

39.30

39.30

37.59

43.77

after importing this into FileMaker these same numbers appear as:

43.77

39.299999999999997

39.299999999999997

39.299999999999997

37.590000000000003

43.77

I've copy/pasted the spreadsheet into a new value making sure I just get the values and there's no hidden numbers that are rounding as far as I can see - I'm stumped as to how 39.30 in Excel becomes 39.299999999999997 in FileMaker?

This is an old problem caused by a difference in how the two applications store floating point numbers.

Both applications store them in a binary form of scientific notation, but the number of "significant digits" used by each are not the same and this will result in what you are encountering after importing your data.

The fix is to round the data after import to a very large number of decimal places using the round function in either a looping script of via Replace Field Contents using the calculation option.

see here for more info: