Excel calculation / bug(?) that makes no sense. Can any of you figure this out?

[cantstandya]

Have been troubleshooting this for 3 hrs, help? I can't upload the .xls file itself, but the image below captures it / will allow anyone to replicate. I'm just doing a simple exchange rate calculation for an investment in 2 different currencies (CAD / USD) at an exchange rate of 1.2323

Why does F5 return FALSE instead of TRUE?

1) Cell F3 calculates the total USD investment by simply dividing the total CAD investment by 1.2323
2) Cell F4 calculates the total USD investment by (i) dividing the CAD price by 1.2323, adding that to the CAD commission divided by 1.2323, then multiplying the sum by 1,500 shares.

The two methods should absolutely return the same result, but Excel doesn't agree.

May seem inconsequential, but I have formulas that rely on Excel identifying when 2 cells are equal, and this "bug"(?) makes no sense to me. Strangest of all, the above seems to be the only combination of values that returns FALSE and not TRUE in F5. If I change any of the values (the FX rate, the CAD price, the CAD Commission), F5 will return TRUE. It's only this precise set of values that Excel refuses to recognize as returning equal results.

Anyone?

 

[joeu2004]

I believe I have already explained the (second) problem that "cantstandya" asked about, to wit: why is =F4-F3 in F7 exactly zero (not just displaying 0.00...00), but =F3=F4 in F5 is FALSE?

The answer is: Excel applied its "value reaches zero" heuristic to F7, but not to F5. That is unexpected because usually when the "value reaches zero" heuristic applies to subtraction, it applies to equality comparison as well, as it should. I provided an example to demonstrate that fact.

PGC, if that is what you are saying, too, we are beating a dead horse, and the discussion should end there.

I still think that this is due to the 15 significant digits limit in the exel worksheet
[....]
We see in the worksheet F4-F3 with a result all 0's. We are in the presence of 3 values in the cell: F4 (6793,19970786334), F3 (6793,19970786335) and the result of the difference (1,81898940354586E-12), much much smaller ((F4-F3)/F4 < 10^(-15)) and ignored.

=F4-F3 is not just "all 0's". It is exactly zero, and it displays 0.00E+00 when formatted as Scientific.

But note that =F4-F3-0 displays about -1.82E-12 when formatted as Scientific.

How could merely subtracting zero alter the result, based on your understanding?

Hint: (F4-F3)/F4 < 1E-15 is true in both cases.

The question is rhetorical.

If it is not sufficient to put your arguments to rest, I suggest that you and I continue the discussion in Private Messages. I think I have gone far beyond TMI for "cantstandya's" benefit.

FYI, the answer is: the presence of -0 defeats the "value reaches zero" heuristic because the formula no longer meets the conditions as *I* intuit them, to wit: the last operation is not subtraction between two positive operands that are non-zero. Consequently, Excel does not arbitrarily replace the actual arithmetic result with exactly zero.

 

[joeu2004]

Minor errata, too late to edit....

Hint: (F4-F3)/F4 < 1E-15 is true in both cases.

I copied PGC's expression. I had meant to correct it, to wit: (F3-F4)/F4 < 1E-15, or ABS((F4-F3)/F4) < 1E-15.

 

[pgc01]

I agree. I believe that cantstandya already realised that what he sees is not a bug. It's how it works.
When compring floats it's safer to round them.

 

[joeu2004]

I agree. I believe that cantstandya already realised that what he sees is not a bug.

I disagree. I believe it is a defect in the implementation of the "value reaches zero" heuristic for comparisons, since it is inconsistent, but only sometimes, with the "value reaches zero" heuristic for subtraction.

As for what "cantstandya" realized or not, he/she wrote: "that makes no sense; if F3 and F4 are actually off by a decimal place why wouldn't subtracting one from the other also show that decimal point difference?".

But I do agree that it is prudent to round calculations and comparisons when we expect a specific degree of precision [1].


-----
[1] In "cantstandya's" example, it is important not to round D4 and E4, even though they are formatted to display only 2 decimals places. F3 and F4 are only close to their algebraic equivalence if D4 and E4 are rounded to 5 decimal places or more.

 

[pgc01]

I see that we disagree. Which is not a bad thing

I don't see this as a bug, meaning something not working as intended be the developer, I see it more as a design decision.

I see, however, that you really see this as a bug. And you can back it up with a structured logical reasoning.
Maybe you can consider reporting it?
Whether it's accepted or not, it's still a contribution to the improvement of excel.