Issue with Equation Evaluating Incorrectly When Adding Fifth Term

Copper Contributor

I am currently working on an equation in my excel sheet where I am adding a series of values based on if statements that check if a cell is blank or not. I currently have 4 of the 8 if statements inputted into the equation, and it is correctly evaluating to 0. However, as soon as I add 0 to that, it suddenly evaluates to -8.88E-16, which after checking the math by hand, is incorrect. If I add any value other than 0, it evaluates correctly though. The equation I am currently using (in cell J15) is listed below.

=IF(ISBLANK($D$13),0,G3*($D$50-$D$49))+IF(ISBLANK($D$14),0,K3*($D$51-$D$50))+IF(ISBLANK($D$15),0,O3*($D$52-$D$51))+IF(ISBLANK($D$16),0,S3*($D$53-$D$52))

 

This Is currently evaluating to 0.00E+00. However, the simple modification below alters that value:

 

=IF(ISBLANK($D$13),0,G3*($D$50-$D$49))+IF(ISBLANK($D$14),0,K3*($D$51-$D$50))+IF(ISBLANK($D$15),0,O3*($D$52-$D$51))+IF(ISBLANK($D$16),0,S3*($D$53-$D$52))+0

 

This Equation leads to the value of -8.88E-16. 

 

I would appreciate any assistance in solving this problem, thank you!

 

 

The excel file is attached. The cell I am trying to fix is J15, which will be used to populate the surrounding 8 cells.

 

2 Replies

@KevinHj Check out this link. And you can Google for more explanations of the issue.

https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision... 

In other words, Excel may give seemingly incorrect answers. Your "incorrect" number is still zero up to 15 decimals. Wrap the formula in a ROUND function. For example:

=ROUND(<your formula>,14)

and the answer will be 0.00+00E

@KevinHj  

 

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.