A1 =SUM(AE10:AP10)-O10, which returns 0. O10 is yearly revenue, cells AE to AP are monthly revenues, which are calculated on the basis of $O$10/12, so it's normal that I get 0 in A1. I double-check by doing B1 = A1 = 0, which gives me TRUE. However, when I do A1 =(SUM(AE10:AP10)-O10) [so with brackets], suddenly B1 = A1 = 0 gives me FALSE. Any idea why that might happen?
I think my previous response answers your primary question, which I believe is: why does simply surrounding an expression with parentheses changes the result?
But I see some nuances in your description that might not have been addressed.
-----
First, do you really type B1
=A1=0, or did you mean B1
-A1=0? The difference is "B1 equals A1" v. "B1 minus A1".
We
expect the first form to be FALSE because B1=A1 results in TRUE or FALSE, and the expressions TRUE=0 and FALSE=0 both return FALSE, even though TRUE and FALSE can be
converted to 1 and 0.
Moreover, even if you change the first form to B1=A1=FALSE, we
expect that to be FALSE when B1
equals A1. You never tell us the value of B1; presumably zero. In that case, B1=A1=FALSE is FALSE when A1
is exactly zero(!).
-----
Second, with the Excel screwy "close enough" heuristic, it is possible that =B1-A1 results in exactly zero, but =B1=A1 results FALSE, and vice versa.
In all such cases, =B1-A1-0 and =(B1-A1) consistently result an infinitesimal non-zero value (e.g. 1.23E-15) and =B1-A1=0 results in FALSE.
The reason is: Excel uses slightly different criteria for determining that B1 and A1 are "close enough" for B1-A1 to be considered zero and for B1=A1 to be considered TRUE.
B1=A1 is considered TRUE when they
appear to be the same when formatted to 15 significant digits, even if their
actual values are not exactly the same.
In contrast, B1-A1 (in some contexts) is considered exactly zero when the difference is below some threshold relative to their magnitudes. This is very technical; I am trying to simplify. The point is: it is
not based on their appearance when formatted to 15 significant digits.
These infinitesimal differences arise because Excel uses the industry standard 64-bit binary floating-point to represent numbers internally. And that is
not limited to 15 significant digits, an oft-repeated misconception even in Microsoft documentation like the aforementioned KB 78113.
However, Excel does limit
formatting to 15 significant digits arbitrarily. Consequently, often we cannot see the infinitesimal differences.
For example, 0.1 appears to be 0.100000000000000 no matter how many decimal places we format. But in fact, it is exactly 0.100000000000000,0055511151231257827021181583404541015625.
(I use period for the decimal point and comma to demarcate the first 15 significant digits.)
-----
Finally, because of the binary approximation, it is not unusual for $O$10/12 added 12 times to be infinitesimally different from O10.
You
might see the difference if you temporarily format =SUM(AE10:AP10) to display 15 significant digits.
But as noted above, often we do not see the infinitesimal difference even then.
The following would display the infinitesimal difference, if any:
=SUM(AE10:AP10)-TEXT(SUM(AE10:AP10),"0.00000000000000E+0")-0
formatted as Scientific. That is 14 zeros after "0.".
Note that that shows any infinitesimal difference in the internal binary representations. It does not show the difference between the decimal and binary representations; for example, 0.1 v. 0.100000000000000,0055511151231257827021181583404541015625.
