Excel 2010 calculation bug

Question

I have found a calculation bug in Excel 2010 (32-bit) v14.0.5128.5000 and v14.0.6023.1000, and likely others. To replicate the error, enter a number and increase the decimal places to around 20 places, for example: 7.11000000000000000000 in one cell and 7.10000000000000000000 in another, and then enter a formula to subtract one from the other. The result I get is 0.01000000000000070000. Where does the 7 come from? I found this because a conditional format wasn't reporting the correct results, telling me that 7.11-7.10 was > 0.01. Has anyone else seen this error or know how to correct it? I'm running WIN7 Enterprise v6.1 (Build 7600) on a Dell Optiplex 760.

Answer 1

Hi,

You haven't found a bug, what you have discovered is a result of the way Excel and most other applications do Floating-point arithmetic. When you entered those decimal numbers Excel converted them to Binary and often there is no precise binary representation of a decimal number and that's where the 7 came from.

have a look at the link below

http://support.microsoft.com/kb/78113

The link discusses rounding and in your case =ROUND(A1-A2,2) would eliminate the 7

Answer 2

Computers work with binary representations of numbers, but humans work with decimal numbers. There are some decimal numbers that cannot be exactly represented in binary. It's the same kind of issue that prevents the fraction 1/3 from being exactly represented as a decimal number.

Microsoft has this website on "How to correct rounding errors in floating-point arithmetic":

http://support.microsoft.com/kb/214118

The explanations are very technical and you can read them if you're inclined to:

Tutorial to Understand IEEE Floating-Point Errors:

http://support.microsoft.com/kb/42980

Floating-point arithmetic may give inaccurate results in Excel:

http://support.microsoft.com/kb/78113/en-us

Answer 3

Thanks for the quick reply. That should take care of it. I would add though, that from an end-user's point of view it is still a bug when an "incorrect" answer is returned by any program.

It's difficult to argue that logic so i won't All I would add is that Excel works like very other affordable package and adheres to IEEE754...Well it nearly does but that's another subject.

Thanks for the feedback.

Answer 4

Thanks for the quick reply. That should take care of it. I would add though, that from an end-user's point of view it is still a bug when an "incorrect" answer is returned by any program.