WTF is Wrong With Excel? It Changes My Numbers!

[Justinian]

In my spreadsheet, I am trying to write out my sixteen-digit account number of 8448200017875626 but Excel is chnaging that last 6 to a 0 and I cannot figure out why. I cleared all formating but it still does it.

What the heck is Excel doing to me?!

 

[stefankemp]

The 16 digit number is too big for Excel to handle. Change the format of the cell to Text then enter it.

 

[Gerald Higgins]

If it's just an account number, format the cell to text.

Excel won't let you input numeric data with that many significant digits, so it converts anything after the 15th digit to zero.

 

[texasalynn]

usually a number that large you need to format the cell to text. I can't remember the why - I might see if I can found that for you

 

[texasalynn]

look at this

 

[MrKowz]

usually a number that large you need to format the cell to text. I can't remember the why - I might see if I can found that for you

It is because Excel uses a floating-point decimal. Chip Pearson's Article on this topic explains a LOT: from why it can only be precice to 15 digits, and also why some simple addition/subtractions can seem to error.

 

[Justinian]

But I can format as a number and it accepts all the digits, but only chnages the last one.

 

[texasalynn]

look at the MrKowz & my links

 

[Gerald Higgins]

Yes, that's the point.

If it's formated as number, you can key in as many digits as you like, but it will convert anything after the 15th digit to zero.

You're entering a 16 digit number, so it converts the 16th one to zero.

To get round this, format the cell as text.

 

[Justinian]

I can't believe I did not know that already!

Thanks everyone!!