Number changes in number field

I manually entered numbers in a Single Line field, then I changed the field type to Number, and the last two digits of the numbers changed before my eyes for no apparent reason. The change is in the last two digits (14 changed to 20, and 05 changed to 00). Note that I didn’t make a typo - I watched the digits change when I changed the field type to number.

I tried making a new number field and copy pasted the values in there, but the same thing happened there. I tried the same with a formula field with the same results.

It’s not a rounding error - the number field format is set to integer, and so is the formula field.

The screenshot SHOULD show the same number in every field - the “Group ID” field has the correct number.

Is there a ghost in the machine?

Sigurður,

I get the same result — but only with very long numbers like the ones in your {Group ID} field. Your text string “number” is 17 characters long. If its length drops below 16 characters, this problem doesn’t occur.

With a 16-character number I had an interesting experience: I entered “9999999999999999” in the text column (your primary column). Copied, pasted into a second text column. Then I converted that column to a Number field (integer). When I did that, the value displayed changed from

9999999999999999

to

10000000000000000!

I suspect it’s got something to do with floating-point numbers. I don’t understand floating-point math well at all. What I do know is that, you may not be rounding anything, but Airtable with those large numbers definitely is.

Worth noting that this problem occurs ONLY with numbers on this magnitude. It’s not the conversion from text to number that matters: it’s the length (size) of the number. If I type 9999999999999999 (sixteen nines in a row) into a number field, as soon as I exit the field, it gets converted to 10000000000000000. Does NOT happen with “99999999999999” (FIFTEEN nines).

William

That’s fascinating. Thankfully I can accomplish what I need to do here with a Single Line field, so I don’t urgently need a solve, but this is very curious indeed.

Actually it’s curious but not uncommon. More or less standard for very long numbers, I think, at least has been in the data systems I’ve worked with.

It’s never been a problem because I’ve only used these very long numbers as keys (primary/foreign) and I can store them as text. Never needed a number value that large. I don’t build databases that, say, track the US national debt, which gets up into numbers that make astronomers nervous.

number US English max #digits
999,999 thousands 6
999,999,999 millions 9
999,999,999,999 billions 12
999,999,999,999,999 trillions 15

Which is more than enough for the work I’ve done. I’ve built databases that handle hundreds of millions of dollars (although not yet in Airtable). If I am ever asked to build a database that handles something larger than trillions, I’m going to raise my rates.

Very interesting case. According to JS manual,

the “number” type cannot safely represent integer values larger than (2^53-1) (that’s 9007199254740991 (15 digits)), or less than -(2^53-1) for negatives.

that explains such ‘rounding’.

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.