Simple Math

April 13th 2014

If you thought simple math was hard, you're not alone. Indeed, you might be surprised to know that Excel agrees with you.

Let's try a simple subtraction: what is 2.2 minus 2.3? If you guessed -0.1, you'd be right. It's a little tricky because the answer is negative, but not that tricky.

Now let's try the same subtraction in Excel. The formula is: =2.2-2.3. The result is -0.1.

So what's my point?

Take that cell, and change the number of decimal points so there are 16 decimal points. Did you notice the answer changed? Excel now thinks the answer is -0.0999999999999996. Close, but not entirely accurate. Excel doesn't know that the answer is -0.1 at all.

The formula says 2.2 - 2.3 but the answer isn't -0.1

This is a well-documented problem with computer arithmetic that extends far beyond Excel. See this support article for more information: Floating-point arithmetic may give inaccurate results in Excel.

Why does this matter? For the most part, it does not. However, these tiny fractions of inaccuracy can become important once you start doing calculations on numbers that aren't 100% accurate. For example, I saw this on an SSRS report that showed other bike sales were up 4 trillion percent from last year. That's a pretty good growth rate for any company, made more unusual because we had only sold $250 this year and $0 last year. So, what happened? As far as the computer was concerned, we had not sold $0 last year, but $0.00000000006. A ridiculously low number that is for all intents and purposes $0, except when you're trying to calculate a growth rate based on it.

What to do if you start seeing crazy numbers on your reports? Use the ROUND function and round your numbers to fewer decimal places. Generally you're going to know if you need 16 decimal places of accuracy and for those times when you don't, it's not falsifying the numbers to round.

Go back...

Visitor Comments

Post Your Own Comment

Note that comments may not appear immediately on this site.

Commenting is not available in this channel entry.