Sum if statements are incorrectly calculating

[gsq797]

Hello All,

Im running into an issue where my IF() statement is incorrectly calculating the formula. For context here is the formula:

=IF(SUM(O38:O54)='4 Sources & Uses of Funds'!K48,"OK","CHECK")

As mutually exclusive formulas, SUM(O38:O54) absolutely equals ''4 Sources & Uses of Funds'!K48. But for some reason when placed in the If() Statement it states "CHECK" instead of "OK". I've done separate tests to see if its off by a few decimals points subtracting from each other equals zero. This problem just started happening as this has been a test created months ago with no issues.

Anything I'm missing here?

 

[Peter_SSs]

I've done separate tests to see if its off by a few decimals points ..

That is still the likely problem. What happens if you use something like this?

=IF(ROUND(SUM(O38:O54),4)=ROUND('4 Sources & Uses of Funds'!K48,4),"OK","CHECK")

 

[gsq797]

Thanks Peter. I've done that but I was hoping I could find a work around since what's interesting is that I'm sourcing from all the same numbers. Its impossible for it to be off by a few decimal points. But, I guess I'll just have to use the rounding function. Thanks for your help Peter!

 

[Peter_SSs]

I've done that but I was hoping I could find a work around ...

That is a work-around isn't it?

.. I'm sourcing from all the same numbers. Its impossible for it to be off by a few decimal points.

Are you sure? Consider the examples below. They are all working with exactly the same numbers so shouldn't they all return identical results rather than 3 different ones? They should, but given Excel's calculation limitations and algorithms, they don't. (And it's not just Excel).

My cells are formatted as Number with 23 decimal places.

20 05 07.xlsm
	A
1	0.00000000000000000555112
2	0.00000000000000001110223
3	0.00000000000000000000000
Calculation Error

Cell Formulas
Range	Formula
A1	A1	=(0.8-0.6-0.2)*0.1
A2	A2	=(0.8-0.2-0.6)*0.1
A3	A3	=(-0.6-0.2+0.8)*0.1

See also:
Floating-point arithmetic may give inaccurate result in Excel - Office

 

[joeu2004]

=IF(SUM(O38:O54)='4 Sources & Uses of Funds'!K48,"OK","CHECK")
[...] SUM(O38:O54) absolutely equals ''4 Sources & Uses of Funds'!K48. But for some reason when placed in the If() Statement it states "CHECK" instead of "OK". I've done separate tests to see if its off by a few decimals points subtracting from each other equals zero. [....] Anything I'm missing here?

The details, for one thing.

What are the values in O38:O54 and K48? Format them to display 15 significant digits (not necessarily 15 decimal places).

Are those values constants? Or are they calculations?

And what do you mean by "a few decimal points"? Do you really mean infinitesimal differences, like 1.23E-16?

I prefer that you upload a redacted example Excel file that demonstrates the problem to a file-sharing website (e.g. box.net/files) and post the public/share URL here. Be sure to test the URL first, being careful to log out of the file-sharing website.

Some participants in this forum object because they cannot or will not download files. However, with problems like, often the devil is in the details that we can only see in the Excel file.

BTW, although the workaround is indeed to explicitly round, do not round to an arbitrary number of decimal places. Instead, round to the number of decimal places that you expect the values to be accurate to.

-----

As for why SUM(O38:O54)=K48 might be FALSE, but SUM(O38:O54)-K48 might be exact zero....

First, be sure that the difference is truly "exact zero". Format as Scientific. Is the difference truly 0.00E+00, which is exact zero?

If it is, the explanation is: Excel plays tricks with some calculations in an effort to hide the infinitesimal differences that commonly arise with 64-bit binary floating point arithmetic.

Unfortunately, the tricks are different for comparisons and for subtraction. And Excel does not apply the tricks consistently.

In particular, for SUM(O38:O54)=K48, Excel rounds each side to 15 significant digits. You should see the difference if you format each to display 15 significant digits.

But for =SUM(O38:O54)-K48, Excel arbitrarily replaces the binary arithmetic result with exact zero based on very specific conditions. (I have not found an easy way to explain them.)

Note that this substitution applies only to a formula, not to the expression SUM(O38:O54)-K48.

Consider the following contrived example. (Although it is contrived, the situation can arise as a result of normal calculations. I just don't have the time now to generate a real-life example.)

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD="align: right"]20.7286008849557
[/TD]
[TD="align: right"]FALSE
[/TD]
[TD] B1: =A1=A2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD="align: right"]20.7286008849558
[/TD]
[TD="align: right"]0.00E+00
[/TD]
[TD] B2: =A1-A2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD="align: right"]FALSE
[/TD]
[TD] B3: =A1-A2=0
[/TD]
[/TR]
</tbody>[/TABLE]

A1: =20.7286008849557 + 14*2^-48
A2: =20.7286008849557 + 15*2^-48

Note that B3 demonstrates that A1-A2 is not really exact zero, as we would expect. Excel arbitrarily decided to "make it so" in B2.