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?

 

[gaz_chops]

What are the values in F3/F4 if you display decimals?

 

[Aladin Akyurek]

Try...

=ROUND(F3,2)=ROUND(F4,2)

The reason F3=F4 does not hold is probably the division in F3. See for example:https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel.

 

[cantstandya]

What are the values in F3/F4 if you display decimals?

So if I show a lot of decimals in F3/F4, I find a place where there's apparently a difference.
(But another kind of strange oddity is that if I subtract F4-F3 (as I did in cell F7, it's just all zeros, even with a ton of decimal places...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??)

 

[Aladin Akyurek]

Try...

=ROUND(F3,2)=ROUND(F4,2)

The reason F3=F4 does not hold is probably the division in F3. See for example:https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel.

Plus:

https://support.microsoft.com/en-gb/kb/78113

 

[joeu2004]

But another kind of strange oddity is that if I subtract F4-F3 (as I did in cell F7, it's just all zeros, even with a ton of decimal places...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?

Note for future reference: For seemingly "inexplicable" problems, it is best to upload an example Excel file to a file-sharing website and to post the public/share URL here. An image alone is insufficient because the devil is in the details. In your case, this is especially true because you have several typos in the image. In particular, the formula in F3 is =F2/B4, not =F2/B3 [sic]. And the formula in F4 is =D4*C4-E4, not =D3*C3-E3 [sic]. These are essential to understanding the problem.

You are correct: there is no good reason for the inconsistency. IMHO, it is a defect. More precisely, it is a defect in a heuristic that is defective design in the first place, IMHO.

The formula in F5 is =F3=F4. The formula in F7 is =F4-F3. The results should be consistent.

The same cannot be said of =SUM(F4,-F3), =F4-F3-0 and even =(F4-F3), nor of =F3-F4=0 and MATCH(F3,F4,0).

The inconsistency between F5 and F7 cannot be explained by binary computer arithmetic theory or by information in KB 78113 (click here) [1].

To explain....

You note that the values in F3 and F4 are infinitesimally different. That is indeed due to the vagaries of binary computer arithmetic, which is explained to some degree in KB 78113.

But =F4-F3 returns exactly zero because of a dubious heuristic that is poorly described in KB 78113 under the misleading heading "Example when a value reaches zero".

Essentially, when the last operation of a formula is subtraction (or addition of oppositely-signed values), and the two operands are "close" (and nonzero), Excel arbitrarily replaces the actual arithmetic result with exactly zero.

Likewise, in a formula, if the operands of a comparison are "close" (and nonzero), Excel arbitrarily treats them as equal.

Usually, the two heuristics are consistent between themselves. For example, =2.558-1.333-1.225 returns exactly zero, and =2.558-1.333=1.225 returns TRUE.

But that is only due to the arbitrary "close operands" heuristics. In contrast, =SUM(2.558-1.333,-1.225) returns the actual arithmetic result, which is about -2.22E-16. And =2.558-1.333-1.225=0 returns FALSE, which is correct based on the actual arithmetic result.

So in this context, F5 should return TRUE when F7 returns exactly zero, and vice versa; and F5 should return FALSE when F7 returns nonzero, and vice versa.

I suspect the inconsistency arises because (my guess) the comparison heuristic relies on the visible difference (or not) when the operands are rounded to 15 significant digits, whereas (my guess) the subtraction heuristic relies on the binary difference. [2]

Nevertheless, the inconsistencies of the application of these heuristics is bad enough. The comparison and subtraction heuristics should be applied (or not) consistently between themselves, IMHO.


-----
[1] https://support.microsoft.com/en-us/kb/78113

[2] In my recreation based on the image, F3 is 6793.19970786334,57931573502719402313232421875, and F4 is 6793.19970786334,39741679467260837554931640625. That explains the difference in the formatted values, since Excel formats only up to 15 significant digits, demarcated by the comma. In binary, F3 is &h40BA8933,200DF5D3, and F4 is &h40BA8933,200DF5D1. They differ only in that last 2 bits. Some years ago, I had concluded that "close" meant a difference in the last 3 bits or less. I had a.s.s-u-me-d that the same criteria for "close" applied to subtraction and comparison. Obviously not.

 

[joeu2004]

PS....

But =F4-F3 returns exactly zero because of a dubious heuristic that is poorly described in KB 78113 under the misleading heading "Example when a value reaches zero".

Essentially, when the last operation of a formula is subtraction (or addition of oppositely-signed values), and the two operands are "close" (and nonzero), Excel arbitrarily replaces the actual arithmetic result with exactly zero.

Likewise, in a formula, if the operands of a comparison are "close" (and nonzero), Excel arbitrarily treats them as equal.

I might have given the impression that the heuristic is not implemented in Excel functions; that it applies only to explicit arithmetic operations in formulas.

But if A1 is 2.558, A2 is -1.333, A3 is -1.225, B2 is =A2, and C3 is =A3, =SUM(A1:A3), =SUM(A1,A2,A3) and =SUM(A1,B2,C3) do return exactly zero, presumably due to something similar to the "close" subtraction heuristic.

On the other hand, ironically, =SUM(A1,A2,A3,0) returns exactly zero, but =A1+A2+A3+0 returns about -2.22E-16, the actual arithmetic result.

Moreover, =SUM(A1,--A2,--A3), =SUMPRODUCT(A1:A3) and =SUMIF(A1:A3,"<>0") return about -2.22E-16.

The take-away is: the "close" heuristics are applied and implemented inconsistently in Excel functions. (And mostly not at all, I believe.)

 

[pgc01]

...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??

Hi

Don't forget that the display is rounded.

I don't see anything strange in this. When your display is rounded it's normal that the last digits don't match the ones from the operation with the rounded values.

For the sake of simplicity let's take numbers that you display as integers with no decimals

1.7 -> displays as 2
1.4 -> displays as 1

1.7 - 1.4 = 0.3 -> displays as 0

but with the displayed values you'd think you'd get

2 - 1 = 1

This is normat because the display is rounded.

 

[joeu2004]

But another kind of strange oddity is that if I subtract F4-F3 (as I did in cell F7, it's just all zeros, even with a ton of decimal places...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?

Don't forget that the display is rounded. I don't see anything strange in this. When your display is rounded it's normal that the last digits don't match the ones from the operation with the rounded values.

Ordinarily, I would agree. To distinguish the difference, it is prudent to use the Scientific format with infinitesimal numbers and what appears to be zero, instead of Number even with "a ton of decimal places". Excel allows only up to 30 decimal places. So, for example, 1.23E-31 would appear to be zero when formatted as Number. With the Scientific format, 0.00E+00 is exactly zero [1].

But "cantstandya" formatted =F4-F3 in F7 as Number with 20 decimal places, and F4 and F3 are formatted with more than 15 significant digits. We cannot know exactly what values are in F4 and F3, since "cantstandya" did not provide an example Excel file. But F3 appears to be 6793.19970786335, and F4 appears to be 6793.19970786334.

The closest binary values (smallest difference) with that appearance are 6793.19970786335-5*2^-40 in F3 and 6793.19970786335-6*2^-40. The exact difference =F4-F3-0 is about -9.09E-13, which Excel displays as -0.00000000000091 with just 14 decimal places.

So with 20 decimal places, F7 would not appear to be zero due to rounding.

-----

But in this context, Excel does replace the infinitesimal difference with exactly zero, due to the "value reaches zero" (close operands) heuristic described in KB 78113.

This is demonstrated by my recreation of the Excel worksheet based on "cantstandya's" image. Download "calc oddity2.xls" (click here) [2].

However, Excel (usually) applies a similar heuristic to equality comparisons, as I demonstrated with the example of =2.558-1.333-1.225 (exactly zero), =2.558-1.333=1.225 (TRUE) and =SUM(2.558-1.333,-1.225) (an infinitesimal difference) in message #6.

So the point I was trying to make earlier is....

Ordinarily, it would not be unusual for =F4=F3 and =F3-F4 to have inconsistent results due to misleading formatting of the latter, as PGC explained.

But because of the "close operands" heuristics, which applies to comparisons as well as subtraction, I would expect consistent results in this context.


-----
[1] 0.00E+00 is exactly zero for normalized binary floating-point values. Excel and VBA arithmetic usually result in normalized values. However, it is possible to create denormalized values artificially in VBA. The smallest denormalized value (in A1) is &h00000000,00000001. Excel displays that as 0.00E+00, but =A1=0 correctly returns FALSE. My own formatter returns numeric text with 1075 decimal places, which represents about 4.9406564584124654E-324.

[2] https://www.dropbox.com/s/sjyg9q78ojwyb9c/calc%20oddity2.xls?dl=0

 

[pgc01]

Hi joeu

I still think that this is due to the 15 significant digits limit in the exel worksheet, like a "set precision to 15 significant digit".

I retyped the the values posted by cantstandya in a worksheet.

In the immediate window I got

?range("F3")
6793,19970786335
?range("F4")
6793,19970786334

This is what we expect.

This I subtracted them

?range("F4").value-range("F3").Value
-1,81898940354586E-12

They have a small difference.

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.

We can test this if we change the formula in the worksheet. If instead of F4-F3 we just take the difference of the decimal part, then the result will be the same, but not be so small compared to the operands.

We know that the result of the formulas

=F4-F3

=MOD(F4,1)-MOD(F3,1)

is the same, but if you try it in the worksheet you'll see that you get 0 for the first one while the second one reads:
-1,81898940354586E-12

which is exactly what we got in vba.

I think that what we see in this thread is just about how excel deals with the 15 significant digits rule.