Did you know that the one of following is not always true in Excel?

A-B-C = A-(B+C)
100.8 - 100.7 = 0.1

It is a trick question: Both of them aren't always true! (For the second one, see links at the end of this question. Particularly if you use VBA code.)

We rely on Excel to perform complicated calculations for use, and expect a high degree of precision. And Excel is very capable of doing that. But numbers in a computer are just representations of numbers, and are limited in their precision. For the most part, we accept that this is the case, and don't think about too much since who cares about anything past the 4td decimal anyway, right?

That works for 99% of the things we do.

Part of the 1% where it doesn't is when we are doing logical checks, e.g. does the sum of the parts add up to the total? Or is the difference between two numbers equal to 0 (i.e. are they the same number)? Fairly routine checks when we do a QA on a spreadsheet.

The short answer is "Not always".

It's more like Gestalt Psychology - "The whole is different than the sum of the parts." (See here and here).

Let's take a simple example, 1/3. In the example below when we subtract or multiply 1/3, we are using a link to the cell with "=1/3" in the formula. The TRUE/FALSE check is to see if the result is equal to 0 as we would expect it to be. Happily, we see that it is. Hurrah!

Now let's try that with 1/7 (which incidentally is a repeating decimal. i.e. 0.142857142857142857...).

What about the second one? Mathematically, they are all equivalent. Why do we get 0.0000000000000002?

Remember you are only seeing a 15 decimal number of how the number is represented internally in Excel. In the second formula, successively subtracting a representation of 1/7 from 1 means that small errors creep in to the calculation. If you step through that calculation with "Evaluate Formula", this is the first step you would see.

Again, Here's what I think (not sure about this): In the last case, the parenthesis surrounding the 1/7s means the representations of seven 1/7s added together get us back to 1.

If you entered these numbers by hand in Excel and subtracted them, you'd get -1.05471E-15.

=1-0.142857142857143-0.142857142857143-0.142857142857143-0.142857142857143-0.142857142857143-0.142857142857143-0.142857142857143 = -1.05471E-15

=1-(0.142857142857143+0.142857142857143+0.142857142857143+0.142857142857143+0.142857142857143+0.142857142857143+0.142857142857143) = 0

The take home point is to not add numbers up and compare to the original, or just check that the difference is zero. For the most part, these differences will be around the 15th decimal (i.e. the precision limit in Excel). So you may have to round the final numbers several decimals and then compare to to the original number also rounded (or to 0). With larger numbers, the error will probably present itself at the 15th significant figure.

Additional Links

Here are some articles that get into it as well. The first one talks about MS Access and VBA code, but still highlights the basic issue with numbers in computers.

• Download the Sample File
• When Microsoft Access Matt Doesn't Add Up: http://www.fmsinc.com/tpapers/math/index.html
• Floating-point arithmetic may give inaccurate results in Excel: https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel
• Understanding Floating Point Precision, aka "Why does Excel Give Me Seemingly Wrong Answers?": https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/
• 15 digit limit in Excel: http://excelunplugged.com/2014/05/19/15-digit-limit-in-excel/
• XLPrecision - Excel Add-in to increase precision: http://precisioncalc.com/what_is_xlprecision.html
• Bonus Link for programmers: http://www.vb123.com.au/precision_matters.htm