Strange Excel test....

[FreshPrince]

those of you using excel, try this:

copy this number into a cell 3417300003257077

format the cell as numbers with no decimals

does it change the last number to a 0?

btw, that's a fake CC number so don't think you've hit the jackpot! 😛

one of my users came to me with this problem and you know us IT folks....we install the programs, we don't actually use it so I have no clue what to tell her... 😉

I tried copying it into a new sheet and it's doing the same thing....weird eh?

 

[dullard]

Numbers have a certain precision in any program. Excel obviously has been set to a certain precision. You are inputting a number with more precision than Excel was designed for.

Note: due to binary math, numbers typically have 2, 4, 8, 16, 32, or 64 digits. One of them can be reserved for the minus sign. Thus, in Excel which is set for 16 digits, with one being the minus sign, you only have 15 digits left. Everything else will be set to zero. Try it with ANY number longer than 15 digits.

 

[MrBond]

That's really weird. It doesn't retain that last seven at all. I tried adding 1 to that number in another cell, and get the same number in return. If you add 1-5, it will round down to ..70, if you add 6-10, it rounds up to ..80.

Excel 2k3 here.

 

[dullard]

Originally posted by: MrBond
That's really weird. It doesn't retain that last seven at all. I tried adding 1 to that number in another cell, and get the same number in return. If you add 1-5, it will round down to ..70, if you add 6-10, it rounds up to ..80.

Excel 2k3 here.

I explained it in my post. Here is what Excel 2000 help file says (I typed precision and got this):

Precision of calculation

15 digit precision Excel stores and calculates with 15 significant digits of precision.

 

[dullard]

Oh, and to fix the problem, the first 4 digits list the type of the credit card. There are only a few types. Thus, you can easilly replace the first 4 digits with just one number.

Or break the number up into chunks. 3417, 3000, 0327, 7077

 

[pontifex]

yeah, i found a weird thing that excel does with certain numbers. it always changes them to a date if the cell is formatted in anything other than general text (i think)

well, it did it before. i was just trying it now and it doesn't seem to work...eh...i fvcking hate excel

 

[JS80]

it does that with any long number

 

[FreshPrince]

problem solved

since these are CC's...there won't be any mathematical computations so I just changed the format to text...that'll work 😀

this is the most retarded thing I've ever seen though....I mean it's a program designed to work with numbers, why would it create such a limitation? 😕

good thing I don't deal with helpdesk on a daily basis anymore....I would go crazy.

this person is the CFO and my boss so I really didn't have a choice 🙁

 

[dullard]

Originally posted by: FreshPrince
this is the most retarded thing I've ever seen though....I mean it's a program designed to work with numbers, why would it create such a limitation? 😕

That is the way computers work. Look up single precision, double precision, etc.

If every number stored 16 digits, it'll use half the memory as if every number stored 32 digits. It'll use 1/4th the memory as 64 digits. Same goes with file size, and to a different extent calculation speed (this latter point is hardware dependant).

When you program, you must make a choice. Clearly, for almost all calculations, the 16th digit is meaningless. There are the few problems where it is significant. Do you distroy your program's performance and computer compatability to allow for these rare situations? Or do you ignore them?