Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Nov 3 '10 at 17:54

This question came from our site for professional system and network administrators.

    
There is no hexadecimal anywhere in the question, only decimal (albeit scientific nonation). Please edit both question and tags. –  Suma Nov 4 '10 at 10:02
1  
5000097208326954 is a hexadecimal number with the decimal value 5,764,617,908,402,678,100... however, I'll acknowledge that the hexadecimal nature of the value is probably irrelevant –  Dancrumb Nov 4 '10 at 15:18
add comment

5 Answers 5

up vote 6 down vote accepted

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

share|improve this answer
    
Thanks... I don't hex confused with scientific notation... the number I chose is definitely a hex number, it's just Excel assumes that it's decimal. Since I won't be performing any mathematical operations on it, I'll just tell Excel to treat it as text and be done with it. However that final digit was boggling my mind a little, so thanks for clearing that up! –  Dancrumb Nov 4 '10 at 15:21
    
+1 if you change the field to number it will also round off to 5000097208326950 dropping the 4 yet the highest number allowed is 9.99999999999999E+307 office.microsoft.com/en-us/excel-help/… –  N4TKD Apr 25 '11 at 12:45
add comment

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.

share|improve this answer
    
Albeit a little off-topic, could you please calrify your "Please Don't use Excel for anything critical." comment. Numberical calculations inheritly work with finite precision numbers and are suspectable to propagation of round-off errors, but is Excel any worse than other methods, f.ex. working with floating point numbers in your programming language of choice? –  GummiV Nov 5 '10 at 10:37
    
In languages with Bignums (infinite precision) or BCD you will get much better results than in Excel. Smalltalk, FORTRAN, PERL, Java etc. all can do this. Java doesn't use the MS maths libraries, and the ones it uses are closer to FORTRAN in the answers they give. The CPU hardware math is sometimes very good, but Intel make you sign an Non-disclosure agreement to see the CPU errata now, so even if they worked badly, I couldn't say. You might use MAPLE to do some math; it will give very nice answers. Basically, math is something most people assume works, but the computer can easily get wrong. –  Tim Williscroft Nov 8 '10 at 0:22
add comment

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?

share|improve this answer
1  
I beg to differ. I belive Excel does not in any way "hold" the numbers that are rounded off and/or use them for calculations. The non-significant-part data are discarded when Excel stores the number. Please consider editing your answer to avoid misinforming readers. –  GummiV Nov 3 '10 at 22:21
    
thank you for setting me straight. answer edited. –  Xantec Nov 3 '10 at 22:33
add comment

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.

share|improve this answer
add comment

Right click on a cell or in all cells > Format cells > Choose one of these formats:

  • number (or its variants): to recognize as a numeric value
  • text: the best option, because if you can use 0's in left side, they will appear

Well, I couldn't see that solution here - only some discussion about scientific notation or an possible error about your hex numbers.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.