Bug adding numbers in Excel?

Question

If I paste these 11 numbers to Excel, then find the sum, then expand the decimals, the sum is incorrect.

-10643.40

-3662.93

-463.13

-412.11

-411.72

-379.10

-370.66

-338.10

-296.92

-258.28

12149.10

The sum should be -5087.25, but with 11 decimals the spreadsheet looks like this:

-10643.40000000000

-3662.93000000000

-463.13000000000

-412.11000000000

-411.72000000000

-379.10000000000

-370.66000000000

-338.10000000000

-296.92000000000

-258.28000000000

12149.10000000000

-5087.24999999999   <--- The sum is incorrect

Office Version 1609 (Build 7369.2118)

Windows Version 10.0.17134 Build 17134

Answer 1

This is simply yet-another example of arithmetic anomalies that commonly arise because Excel uses 64-bit binary floating-point to represent numeric values internal.

For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!) because 10.1 - 10 is 0.0999999999999996.

The work-around is:  whenever you expect a calculation to be accurate to some number of decimal places (2, in your example), explicitly round to that number of decimal places (and not to an arbitrary number of decimal places like 10, as some people suggest).

Your sum should be =ROUND(SUM(A1:A11),2) .

These "anomalies" are not considered to be bugs per se, but a limitation of the internal representation of numeric values.  Most computer applications have the same limitations.  (Although some do not because they use a different internal representation.)

In general, the problem is:  most decimal fractions cannot be represented exactly in 64-bit binary floating-point.  And the approximation of a particular decimal fraction depends on the magnitude of the value.

For example, notice the different approximation of 0.1 in the following numbers:

10.1         10.0999999999999,996447286321199499070644378662109375

0.1              0.100000000000000,0055511151231257827021181583404541015625

I use period for the decimal point and comma to demarcate the first 15 significant digits, which is the most that Excel formats, rounded.

Answer 2

Hi nagornyi,

I can repro the issue by my side in the latest 1906 version of Office.

If I change any number in the list, the issue disappear, it seems like the issue only associated with the specific number, and only happen with 11 or more decimals.

Thanks for reporting it in our community, I will ask for more resource to engage in the case.

Please also report it via the smiley icon in the upper-right corner of your app to send feedback directly. 

Regards,

Eric