=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.)
| A
| B
| C
|
1
| 20.7286008849557
| FALSE
| B1: =A1=A2
|
2
| 20.7286008849558
| 0.00E+00
| B2: =A1-A2
|
3
| | FALSE
| B3: =A1-A2=0
|
<tbody>
</tbody>
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.
