Excel can’t add

I just read through a 20 or 30 comment thread/rant on a newsgroup about how Excel can’t add up because .29 – .28 doesn’t equal .01. (when you expand it out beyond Excel’s published accuracy limits (which comply with the IEEE standards)).

The original poster asserts repeatedly that even kids know .29 – .28 = .01000000.

That statement is so grossly wrong it alarms me. So much so that I was going to comment on the thread – except its a few days old and no one would notice probably.

Never mind the floating point approximation issue that flares up regularly (which was the cause of his initial rant), this is much more fundamental.

Its basic maths, accuracy and significant digits, and approximation.

.29 is the way of writing any number from .285 to less than .295

.28 is anything from .275 to less than .285

which means one minus the other can fairly be anything between (but not including) 0 and .02.

I don’t think you can justifiably have an answer to a greater precision than the inputs, can you?

I see lots of multi year projections down to the pence, it just seems total false security to me, better to be approximately right than precisely wrong (IMO). That’s why I like using =round() with a negative second argument to get round thousands, millions or billions.

Do you see much of this fantasy accuracy?

cheers

Simon

(and yes Excel can add, and take away – within its documented accuracy limitations – which should be adequate for most general purposes)

Advertisements

15 Responses to “Excel can’t add”

  1. Adam Vero Says:

    I see forecasts and estimates calculated to ridiculous precision all the time (even beyond the level of pence – how exactly do you propose to earn that last £0.0016 in your sales forecast?).

    Really daft when these things are usually based on a couple of gut feelings and a bunch of approximations (or even outright guesses) rather than a set of carefully measured metrics.

    Even when figures are based on measured events, people quickly forget to take account of the margin of error in the measurements taken (basic high school science lessons easily forgotten).

    Don’t get me started on people that can’t be bothered to read documentation about limits of calculation, let alone try to properly understand why floating point maths is inherently flawed in this sense (while perfectly adequate for most daily work).
    It was this sort of confusion and FUD which made it really hard to get people to understand the issues when Excel 2007 had a real bug with certain calculations, as newsgroups filled with idiots waffling on about how to make Excel miscalculate 3 * 1/3 or somesuch irrelevant nonsense.
    Similar comments turned up on my blog posts about the subject (almost two years ago now, doesn’t time fly!) http://wp.me/p2I5L-23

    When I posted that the bug was fixed even more people jumped in to say there were still problems (sadly nothing to do with a bug, just documented limits) http://wp.me/p2I5L-24

  2. Simon Says:

    woo hoo youre using them new wp.me link thingies – I’d better investigate – don’t want to be accused of being a fossil!
    spot on on all the other points as usual Adam, especially the distracting noise around that calc/presentation bug.

  3. Harlan Grove Says:

    Don’t belabor arguments like this unless you want to wallow in the ugly details.

    Scientists and engineers may understand 0.01 and 0.010000 aren’t the same thing, but mathematicians take them (along with 0.0099999…, 1/100, 1%, etc) as different representations of the same real number. Most people complaining about Excel use the mathematician’s perspective failing to realize that decimal representations that look like numbers in spreadsheets aren’t real numbers but ordered identifiers for varying width intervals of real numbers. These ordered identifiers share some but not all of the arithmetic rules than real numbers follow.

    The problem is that numbers are presented in decimal but stored in binary. In Excel, 0.29 does NOT represent the range [0.285,0.295). It represents

    2^-2 + (2^-4 + 2^-6 + 2^-10 + 2^-11 + 2^-12 +2^-13 +2^-15 + 2^-17
    + 2^-18 + 2^-19) * (2^0 + 2^-20) + 2^-44 + 2^-46 + 2^-50 + 2^-51
    + 2^-52 +/- 2^-53

    so Excel represents 29/100 as 0.29 because it’s either 0.28999… or 0.29000… with either ‘long enough’ string of 9s or 0s before getting to the next non-0 or non-9 decimal digit. 0.29 in Excel carries true significant decimal digits out to about 18 places to the right of the decimal place, but after the second they’re all either 9 or 0.

    Simon, you yourself have fallen for the other great fallacy in the arguments typical of rounding error: assuming decimal representations of binary values have anything to do with standard notions of decimal significant figures. They don’t. They’re a consequence of using different radices and arbitrary cut-offs for displaying long strings of 9s or 0s on the right end of the display radix representation.

    That said, in an ideal world precision as displayed would be a workSHEET-level setting, and Excel would prompt users whether to use it or not whenever users add worksheets, and would display in the status bar (next to the useless CAPS indicator) whether or not it applied in the active worksheet.

  4. Timon Says:

    This is a great personal bugaboo of mine too — I can’t believe how rarely error is taken into account when doing calculations. Look at US Federal contracting if you want the firehose-in-the-face of innumeracy, for example contract # C2011091628 on fbo.gov, for rail renovations, awarded at $1,849,177. Supposing the cost is half precise immutable pre-ordered materials and half skilled labor at $40/hr., this is roughly 11-12 man-years of labor. Saying that the millionth part of that figure is meaningful is literally saying that you have labor costs controlled down to 1.2 minute intervals. No unexpected traffic, no unpredictable illnesses, no, we run a tight ship. They just shamelessly make this shit up, and yet it is universal, it is as if saying $14,000,302,111.23 means that you really buckled down and dug into the numbers and are much more professional than the schlubs who said $12-15 billion. The IRS is actually very suspicious of round numbers — say you buy lunch for a 5-man crew twice a week for a six month job at about $10 a head — $2400. This will be red-flagged, but taking one receipt from one instance, $10.41, and extrapolating $2,498.40, you’re solid. It is Orwellian — the fairest possible real measurement is suspicious and the obvious lie is mandatory. (On second thought it is obvious to barely anyone, people do not intuitively grasp the meaninglessness of a measurement without an error rate.)

    There should be some sort of intellisense for spreadsheets — when numbers start getting big there should be a graph and a dialogue popping up that says “Do you realize that if this number were a car trip across the USA, you are committing to what minute you arrive at exit 41 in Tennessee?”. I could say more, but I am getting worked up — I actually legally have to hire third parties for some of my company’s budget work, and as far as I can tell these ridiculous numbers are mandated — my failure to come up with them being part of why regulators require me to hire specialists!

  5. Andy Says:

    Wow,

    Great post Simon. This is an awesome answer for this argument. Of course Excel can add and subtract! Thanks for the explanation.

    Share your knowledge and expertise with the Office community on Facebook at http://www.facebook.com/office

    Cheers,
    Andy
    MSFT Office Outreach Team

  6. Bob Phillips Says:

    Wow Simon, what have you unleashed?

    Isn’t the big point being made that companies are frun by accountants. And accountants understand a bill of £25.37, but c annot comprehend a bil of £25,000,000. That is why my expense claims were always challenged, and yet we bought crap products for £100M.

  7. Simon Says:

    Harlan – I was just talking about the numbers, not particularly representing them on a computer. Actually I was thinking about my joinery training – where 8,000 mm is a lot different to 8 metres.
    I had thought it was a maths point, but I take your point its more an engineering issue.
    Andy – what is the office outreach team? (its not drugs rehab is it?

    Bob – you put in an expenses claim for 25M? fair play – was it to a bank for a meal for 2?

    Since you make the point though, I guess you are right – in most orgs the rounding issues I brought up are insignificant to the money they piss away on inappropriate crap.

  8. My Name’s Tucker – Not Sucker ! « Dick Moffat's Excel and Access Blog Says:

    […] 25, 2009 This is NOT a discussion of “Precision” of Excel Formulae –  for that see https://smurfonspreadsheets.wordpress.com/2009/09/25/excel-cant-add/ …..    This is about the Business Math of Excel and/or Access development.  (I know you […]

  9. Rob Bruce Says:

    Early Weddoes fan, Bob?

  10. Bob Phillips Says:

    @Simon, no, it was for four, but we had to fly to New York for it.

    @Rob, indeed I am, and I like their re-incarnation (and of course, I am a huge Geoerg fan).

  11. Bob Phillips Says:

    @Simon, I meant to say on a more seriously note that yes the small amounts are insignificant, but the efforft expended on them is not. Because it is far easier to comprehend £1 than £1m, small amounts get far more scrutiny, and far more effort goes into justifying them. Back in the 80s my company was trying to create ‘complete banking’ system and there ware reams of proposal documents (I kid you not, they were feet high when stacked) which of course no-one properly read, the project had no proper supervision, and we spent £65M (totally unofficial n umber, no-one would dare admit the real n umber) before they called it a day and realised it couldn’t work. At the same time, I struggled to get £2,000 PCs that could start to pay bavck immediately.

  12. Simon Says:

    Sad but true Bob.
    I’ve seen it called the bike shed syndrome. I though I had blogged about it, but can’t find it – maybe it in one of the unpublished drafts.
    http://en.wikipedia.org/wiki/Parkinson%27s_Law_of_Triviality

  13. Simon Says:

    I thought it was GB, but was trying to zoom to check

  14. Patrick O'Beirne Says:

    Issue 287 of PC Plus Magazine covers it too
    “Why the PC fails at maths”

  15. Adam Vero Says:

    There’s a draft whitepaper out now about the improvements to many functions in Excel for the 2010 release, notably statistics and financials, but also little things like MOD and RAND.
    Read more here: http://wp.me/p2I5L-44

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: