Excel SUM anomaly

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Excel can’t add up — can you find other examples?

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

but according to Excel 2002 and Excel 2007 the total isn’t exactly zero when you expand the number of digits to the right of the decimal point:






0.000000000008640199666843

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.

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 2007 includes a IF statement test:

609 Excel SUM anomaly - Excel SUM anomaly

 

The test formula is simple: =IF(A9=0,”Yes”,”No”)

It’s revealing to look at progressive SUM results starting with adding all cells A1:A8 as above then removing one cell each time down to A1:A2.

609 Excel SUM anomaly   progressive SUM results - Excel SUM anomaly

 

As you can see, the problem starts when adding A1:A5 and beyond.

Excel has all sorts of strange behaviors like this and it’s about time they were stomped out. 

We’d like to hear from other Office Watch readers who have confirmed simple maths anomalies.

Email us with a sample worksheet, version of Excel and explanation  [email protected]

We’d like to compile some examples and submit them to Microsoft — maybe they’ll consider a revamp of Excel’s basic maths ability.  Microsoft has been too complacent about this for too long – they appear to assume the core Excel code is perfect, clearly it isn’t.

subs profile e1563205311409 - Excel SUM anomaly
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address