Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Excel SUM anomaly

Excel can't add up -- can you find other examples?

by Office Watch

Bookmark and Share

  | Mobile | click for more article services     


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:

Excel SUM anomaly image from Excel SUM anomaly at Office-Watch.com
 

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.

Excel SUM anomaly - progressive SUM results image from Excel SUM anomaly at Office-Watch.com
 

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  ExcelCantAdd@office-watch.com

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.

Article posted: Tuesday, 29 April 2008

there's more ...

If you liked this article you'll LOVE our new ebooks.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8

A focused and unvarnished look at Windows 8, especially written for the many people who use Microsoft Office  Get it today - click here.

ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox

100+ pages of practical tips and help to streamline, automate and search your Inbox.  Get more than you ever thought possible from Outlook.  Read it today - click here.

More from Office Watch:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» Discounts on Office 2013 purchase
» Using Conversations in Outlook
» OneDrive for Business alters files
» About Outlook Conversations
» Keep using your device on the plane
» Questionable Outlook holidays


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).