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




More Excel addition strangeness

Office Watch readers come up with some truly strange arithmetic - Excel style. The conclusion: Excel definitely can’t add up - in fact it would fail an elementary school maths test.

by Office Watch

Bookmark and Share

  | Mobile | click for more article services     


Does Excel have an addition bug? You'd think that was a simple question to answer, you just insert the numbers and it either adds up correctly or not. Sadly it's not that simple, even with simple numbers - but we think it's a bug and a curious one at that.

We'll try to present both points of view so that you can make your own judgement.

We're indebted to all the readers who've written about the Excel addition bug. As you'll see many readers had fascinating insights into the problem.

The Basic Problem

Phil N. came to us with the example of 8 two decimal place numbers which should add up to zero. Instead Excel shows a result with stray digits in the 12 decimal place and beyond.

That's a problem if you try to test the result (eg does the sum of the values equal zero) because Excel thinks it isn't exactly zero. It can be confusing if you're only displaying the cell to two decimal places because it looks like the result is zero and you can't work out why your test formula ( IF() ) isn't working.

Check out our article Excel SUM anomaly for more details.

Readers found there own examples of similar problems, most involved addition where one of the numbers is negative. Possibly the simplest came from Stephen who got the error to show up in just three cells:

-1.23

1.12

0.11

0.00000000000000012490009027033000000

The problem has been around for a while with the same of similar issues appearing in Excel 2003 and 2007. We got varying reports of similar problems in spreadsheet programs from other manufacturers.

The Floating Point excuse

Many readers wrote (with varying level of politeness) to tell us how wrong we were. The fault lies with the processor chip, not Excel, because of the way the computer stores numbers and how the processor handles them.

The core problem is that decimal numbers can't be recorded exactly in a computers binary code.

Reader Mike H explains

" 15 (the integer) is 00001111 in binary format, but the floating point number 15.0 is represented by the sequence of bits 0 10000010 11100000000000000000000. 15.1 is represented as 0 10000010 11100011001100110011010 "

Because the computer doesn't store the exact number you expect, small errors will creep into calculations. Normally those errors show up at far greater precision than humans need so the difference doesn't become apparent. But sometimes the problem does show up and affect the way Excel works.

At Office Watch we're coming to the view that excuse isn't good enough in the 21st Century but we'll leave that debate for another time.

For this specific situation we can say that while floating point matters may be part of the problem it's not the complete situation. As many readers said "It's surprising to see these errors showing up in such low numbers and with a stray result in the 12th decimal place."

Order should not matter

The main reason we think it's an Excel bug is the discovery of a few clever Office Watch readers (Arna P. was the first then Paul S. and Stephen W.) who tested for something that should not matter - the order of the numbers being added.

If you sort the numbers in order (high-low or low-high) Excel changes the result to exactly zero!

Excel SUM anomaly - number order fixes the problem image from More Excel addition strangeness at Office-Watch.com
 

At primary/elementary school you learnt that order doesn't matter in addition - ie 2 + 3 is the same as 3 + 2 . We all know that but sometimes Excel doesn't.

Grouping should not matter

Just like the order of addition should not matter to the result, it also should not matter if you group the numbers into two groups and add them separately. But that's exactly what happens in Excel.

The result of the addition can vary just by re-arranging the SUM into two interim steps then adding those SUM's together.

Excel SUM anomaly - split SUMs gives varying results image from More Excel addition strangeness at Office-Watch.com
 

Notice how the split SUM's added together in cell A14 works out correctly but adding five numbers in the first SUM makes Excel go wrong again.

The bad result in A19 is not only wrong but a different incorrect result from the direct addition in A9.

To return to elementary school for a moment … your teacher will have taught you that ' 2 + 3 added to 4 + 5' is the same as ' 2 added to 3 + 4 + 5 ' . Yet again, Excel can't always cope with simple arithmetic.

The Threshold theory

We're indebted to Michael W for this analysis of the Excel addition bug which might explain both the sorting and grouping anomalies mentioned above.

"When taking the numbers and inserting them into the worksheet one at a time, and noting where the errors occurred, I found that the 5th and 8th numbers produced errors. Working with this, I found that the errors occurred where two conditions were met:

1. The sum crossed a threshold of any number equalling 1 followed by all zeros (1, 10, 100, 1000, 10000, etc).

2. The sum was at least a power of ten less than the original number.

For the original number, which was in the hundred thousands (-127551.73), the first error was produced when the sum crossed -10000. This was the 5th number. The 8th number also introduced an error to the sum because it crossed the thresholds of -1000, -100, -10, and -1 all at once.

Note: altering the list of numbers so that these thresholds are crossed individually produces an error in the sum at each threshold.

"

Article posted: Thursday, 08 May 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
» Office Online update
» Deleting Holidays
» Office Online in Google Chrome
» Windows 8.1 Update – secret changes list?
» Missing Easter from Outlook
» Ignore or Mute emails in Outlook


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).