Sorry for the vague question title, but I'm not sure how best to couch this.
When I paste the following number into an Excel cell:
5000097208326954
it shows up as
5E+15
That's fine. I get that. However, when I look in the function bar, it appears as
5000097208326950
That I do not understand. The behaviour seems to be independant of any prior Formatting I might put on the cell.
This is on Excel 2003 SP3.
Am I missing some subtle expected behaviour, or is this a bug?
Somebody will probably come along with a better explanation and a fix, but basically computers only store a specific amount of significant digits when representing floating point numbers and/or large integers. Excel (at least by default), only gives you the first ~15 digits of the number and then discards the rest. You can verify that by adding extra digits to the end of your number, f.ex. 5000097208326954123, and those digits will be rounded to 5000097208326950000.
As a sidenote, you probably have the Hexadecimal numeral system confused with Scientific notation
Excel does all it's math as double precision floating point numbers. (64 binary bits in total)
that gives you about significant 14 to 15 digits in decimal.
5000 097 208 326 954
is 16 digits. If you do this in excel...
A1 = 5000097208326954
A2 = A1 +1
A3 = A2-A1
A3 will display 0.
Full disclosure: I'm a member of the ACM and I'm obliged to tell the public about risks from computing. It's in our code of conduct.
Please Don't use Excel for anything critical.
You'll get more accurate answers with a pocket scientific calculator.
Excel is written in C/C++ and uses 64 bit numbers, It also uses the Microsoft floating point libraries to calculate functions like sin, cos and exp. These libraries are pretty inaccurate compared to scientific ones, even operating on the same size numbers.
Floating point is fine for simple stuff but awful for scientific work or serious engineering they are too inaccurate. Numerical methods where you add and multiply numbers over and over again get ugly fast in floating point as the 15 digits of precision go away, one digit every 4 multiplications.
Floating point is bad for Financial work (0.2 is an infinite recurring number in a binary floating point.) So if you add 0.2 dollars 1000 times, some money goes missing. Programmers in the financial world use infinite precision numbers where they have to (just like writing numbers on paper) And then use special functions for doing money math that when all the rounding adds up, no money goes missing.
Old school programmers used FORTRAN for maths, which does the math in decimal, like you or I. The libraries are very accurate in FORTRAN, as the users expect to do numerical methods; that is what it is for. Calculators use decimal internally too.
I have used Excel to reproduce maths rounding bugs in C programs before.
You can get pretty graphs of your calculation going hopelessly wrong this way.
You are asking why the last digit shows as a 0 instead of a 4, yes?
I believe that this link holds the answer. if you look under Calculation specifications the maximum number precision is 15 digits. Looking at the other specifications it looks like excel is probably still holding the full value of the number you entered, so if you use the number in a formula it should still calculate correctly.
To be honest, the whole significant numbers thing eluded my grasp in college, so i couldn't tell you why excel was set up this way.
This forum may give you pointers to exceeding the built in limitations if necessary.
Also, how do you format excel to recognize the value as being in HEX rather than DEC?
As already pointed out, this is a limitation of 15 significant digits when Excel stores numbers. If this is not a number you need to use for calculating anything according to its value, but rather a reference like a serial number or a product code, then your best option would be to format the cell as text (rather than the default of general) before pasting it in, so it will keep all the digits. This will also prevent it trying to be clever and showing it in scientific notation to be 'helpful'.
Of course, if this really is a hex number (but you happened to pick an examle with no digit greater than 9) then you definitely don't want Excel treating it as a number at all. a) because it will assume it is decimal, and this will fail when there are letters in it. b) this would mean the number is even larger in magnitude, and if you do convert it to decimal you would be way over the 15 digits.
Right click on a cell or in all cells > Format cells > Choose one of these formats:
Well, I couldn't see that solution here - only some discussion about scientific notation or an possible error about your hex numbers.
