Forum Discussion
Issue with Equation Evaluating Incorrectly When Adding Fifth Term
Of course, simply adding zero should not change the actual arithmetic result.
But it does affect the arbitrary behavior of a dubious rule that Excel implements inconsistently in a misguided effort to minimize the effects of 64-bit binary floating-point arithemetic.
In general, it is prudent to explicitly round calculations that you expect to be accurate to some number of decimal places (use ROUND) or significant digits (use TEXT, converted to a number).
-----
The difference when zero is added is hinted at in the poor explanation under the misleading title "Example when a value reaches zero" in the document at Binary Arithmetic Anomalies .
In a nutshell, when the last operation of a formula is subtraction (or addition of oppositely-signed values), and the magnitudes of the two non-zero operands are "close enough", Excel arbitrarily replaces the actual arithmetic result with exact zero (0.00E+00).
When we add zero, that rule no longer applies because the last operation is not a subtraction of two non-zero operands.
BTW, the rule no longer applies even when we simply put parentheses around the entire expression. Using MSFT's example, =1.333+1.225-1.333-1.225 results in exact zero, but =(1.333+1.225-1.333-1.225) results in -2.22E-16.
And be forewarned: The SUM function is not consistent about when it does and does not apply the rule internally.