Excel SUM anomaly

Office WatchLast updated: 29 December 2023

Microsoft 365,Microsoft Excel,Microsoft Office,Microsoft Office for Mac,Office 2003,Office 2007,Office 2010,Office 2013,Office 2016,Office 2019,Office 2021 / Office LTSC

Excel can’t add up reliably and it never has. It’s an important limitation of computer software that everyone needs to be wary of.

Phil N sent us an interesting Excel worksheet. He’s simply adding a column of numbers starting with a negative number then positive numbers – the total should be ‘a wash’ – that is add up to zero.

-127551.73
103130.41
1807.75
7390.11
9028.59
2831.26
1568.90
1794.71

According to Excel initially the total is zero but that cell display is misleading.

When you expand the number of digits to the right of the decimal point you see that Excel has come up with a screwy result.

With General cell formatting, that’s really obvious.

If you’re just looking at a few decimal places the problem won’t be apparent.

You might think that these sorts of issues don’t matter but it becomes a problem when you do tests on the result – for example a simple IF test to see if the result is zero will return a False answer.

There’s also an argument that the responsibility is on users to put proper tests into their worksheets to avoid these issues – that’s true to some extent but the primary responsibility is with Microsoft. Excel should be able to handle simple addition as well as my handheld calculator or an elemetary schoolchild.

The surprise in Phil’s example is that it involves simple addition – often there’s hassles with multipy or divide which have long remainders. But it’s hard to understand why the addition of numbers to 2 decimal places gives a result with a remainder in the 9th decimal place and beyond.

Here’s the worksheet in Excel with a IF statement test: