16 digit number, last number changes to 0

[pcar]

I work for a financial institution, and we are creating a worksheet that lists card numbers. The card numbers, like most other card numbers, are a 16-digit mod-10 number. We're not doing any of the calculations in excel, we're just simply entering in the card number as it appears on the card. The problem is that the last digit is changing to a "0". What is going on and how can we stop it?

 

[Jonmo1]

Does anyone know why this happens, or is it like the mystical force that removes socks from the dryer and will never be explained.....

<!-- / message -->
Did you miss post # 4 by schielrn ? That points to an article at Microsoft that explains it.

 

[Richard Schollar]

Schielrn's already given you Microsoft's explanation (or a link to it) - Excel abides by IEEE754 standard on precision for floating point accuracy which means only 15 digits' worth. 16 digit credit card numbers exceed this hence they drop the final digit (which becomes zero).

 

[pcar]

Oops! I missed the link. Thanks a bunch!

 

[Johnny C]

MS work to IEE standards for numerical data types. Their standard is 'upto 15 digits'. It's due to the way numbers are stored as 1 byte in 16/32bit processors (can't remember which)

It's the same with additions, if you add up a lot of fractions it may add up incorrectly when you add more than 255 numbers. If you've ever used a pivot table to add up debits and credits that should zero each other out, it's a pain in the proverbial as it will add up to 0.0000002, clearly wrong as none of the numbers have more than 2d.p. Just something we have to live with I'm afraid.

VBA is better as it has better number types which can accomodate far larger and far more precise numbers with accuracy, as it uses several bytes for a number if you so specify.

 

[Richard Schollar]

Hmm, I was always of the opinion that VBA and Excel were equivalent in their precision (both Double data type at their max), with the exception that VBA also has a 64-bit data type in the Currency (I think, anyhow).