Is this Excel behaviour with a large hex number expected?

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?

Solution 1:

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

Solution 2:

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.