Excel Calculation Mathematically Incorrect

mmajor77 

For a dispositive explanation, please attach an example Excel file that demonstrates the problem.

In a nutshell, it is an anomaly of the way that Excel represents numbers internally, namely 64-bit binary floating-point.

Most decimal fractions cannot be represented exactly in binary.  So, they are approximated.  Moreover, the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number.  That is why, for example, 10.1 - 10 = 0.1 returns FALSE (!).

In general, when a calculation involves or results in numbers with decimal fractions, and we expect or want the result to be accurate to some number of decimal places, explicitly round to that number of decimal places -- and not to an arbitrary number like 10 decimal places.

For your example, we might write ROUND(B1-A1, 1) and ROUND(B2-A2, 1) because your data appears to be accurate to 1 decimal place. 

But perhaps they are accurate to more decimal places, and your examples just happen to have trailing zeros.  So you might want to round to 2 decimal places, for example.

-----

One or both of A1 and B1 are calculated, and their exact binary value is infinitesimally different from the binary approximation of the formatted decimal number, which Excel arbitrarily limits to 15 significant digits.

Example:

A1: =10697.7+200000-200000

B1: 10816.2

C1: 118.499999999989    (close!)

Although A1 appears to be 10697.7000000000, it is actually 10697.7 + 1.09E-11.  We can see the residual with the formula =SUM(A1,-(A1&"")), formatted as General or Scientific.

The same might be true of A2 and B2 as well.  But their difference (B2-A2) is close enough to the binary approximation of 118.5 that it is not apparent due to the Excel formatting limitation.