Compare numbers with difference vs. if()

Hey guys,

I have the following issue. I want to compare several numbers (always pairs). I used both the difference, as well as an if() formula to check it. The results are not similar though. Example:

3,665.2958619613400
3,665.2958619613500

If I take these two numbers and do A-B, the result is "-" (zero). If I do if(A=B;"match";"no"), I get a no, so they don't match.

Why is that, does anyone know?

The "-" is because of formatting so it is CLOSE to being a match but is NOT an actual match. If you kep expanding on the decimals shown you will see that it is not the same, and from simple math you also know it is NOT the same. Excel is just doing you a favor and formatting the cells the way you wanted/assigned.

You can try something like the following if you want it to match

if(round(A1,6)=round(B1,6);"match";"no)

And the 6 is just a random number I chose to determine how many decimal places to round.

Numeric calculations can only cater for numeric precision up to 15 digits. The difference in those numbers is in the 16th digit.

See: http://office.microsoft.com/en-gb/ex...005199291.aspx


Regards, TMS

(Also see my previous response.)

Originally Posted by Berbatov_

And how come this is the case, if you have a large number, say trillions, then Excel disregards the 10s and 1s etc. when calculating?

It doesn't.

Calculation anomalies arise because Excel represents numeric values and performs arithmetic using a standard 64-bit binary floating-point form. Specifically, values are represented by the sum of 53 consecutive powers of 2 ("bits") times an exponential factor.

(Actually, in Intel-compatible CPUs, arithmetic uses an Intel 80-bit binary floating-point form. Excel rounds pairwise operations to the 64-bit form, and the final arithmetic result is rounded to the 64-bit form.)

Consequently, most non-integers cannot be represented exactly. In subsequent arithmetic, the infinitesimal differences compound or cancel out in almost unpredictable ways.

For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!) because:

10.1:      10.0999999999999,996447286321199499070644378662109375
10.1 - 10:  0.0999999999999996,447286321199499070644378662109375
0.1:        0.100000000000000,0055511151231257827021181583404541015625

As you can see, the representation of 0.1 in the result of 10.1 - 10 is not exactly the same as the representation of the constant 0.1.

In this case, the difference is visible when formatted to 15 significant digits (demarcated by comma in my presentation above). So even Excel does not consider their difference to be "close to zero".

But the differences are not always visible.

The "-" is because of formatting

No, it's because there are too many digits / decimal places.

You're welcome. Thanks for the rep.

Digits, not decimals. Look in the link I included.


Regards, TMS

Just out of interest:

	A	B	C	D	E
1	3,665.295861961340000000000	3,665.295861961350000000000		3,665.29586196134	3,665.29586196135
2	16	16		16	16
3	=LEN(A1)	=LEN(B1)		=LEN(D1)	=LEN(E1)
4
5	0.000000000010004441720			0.000000000010004441720
6	=B1-A1			=E1-D1

Regards, TMS

Originally Posted by Berbatov_

I have the following issue. I want to compare several numbers (always pairs). I used both the difference, as well as an if() formula to check it. The results are not similar though. Example:

3,665.2958619613400
3,665.2958619613500

If I take these two numbers and do A-B, the result is "-" (zero). If I do if(A=B;"match";"no"), I get a no, so they don't match. Why is that, does anyone know?

The point that mikeTRON tried to explain is: the result is not zero when you subtract them. It only appears to be zero because of the way you formatted the result.

If you had formatted the subtraction result the same as the numbers, you would have seen the difference, namely: -0.0000000000100.

Usually, we need to format with even more precision. The best way to see such differences is use the Scientific format, ideally with 14 decimal places.

Then you would see the subtraction result is -1.00044417195022E-11. (Read: -1.00044417195022 times 10 to the power of -11.)

That should answer your question.

However, it is important to know that values are not always what they appear to be.

In fact, it is possible for calculated values to appear to be 3665.2958619613400 (A1) and 3665.2958619613500 (A2), but =A1-A2 results in exactly zero, yet =(A1=A2) results in FALSE.

The reason is complicated.

It starts with the fact that values and calculations in Excel are not limited to 15 significant digits. That is an oft-repeated misconception, even repeated in Microsoft documentation.

When we enter numbers, Excel does limit the precision to 15 significant digits. And when Excel displays numbers, it does format only up to 15 significant digits.

But the fact is: calculated values in Excel often have much greater precision.

Consider the following example.

A1: =3665.29586196134 + 4E-12
A2: =3665.29586196134 + 5E-12

When formatted to 11 decimal places (15 significant digits), they appear to be 3665.29586196134 (A1) and 3665.29586196135 (A2).

And =(A1=A2) does return FALSE [1], which indicates A1 and A2 are indeed different.

But =A1-A2 returns exactly zero; that is, 0.00E+0 in Scientific format.

The latter result is misleading. It is due to a special dubious heuristic in Excel whereby Excel arbitrarily (and inconsistently) replaces results that are "close to zero" with exactly zero. This is described in KB 78113 (click here) [2].

In fact, the subtraction result is not exactly zero. This is demonstrated by appending a "redundant" -0 to the formula. That is, =A1-A2-0 returns about -9.09E-13 in Scientific format.

Another important point that mikeTRON made should not be lost in the noise: the remedy is to explicitly round non-integer calculations [3] when you expect results to be accurate to a specific number of decimal places.


-----
[1] Usually, if =A1-A2 is exactly zero when it shouldn't be (due to Excel's dubious "close to zero" heuristic), =(A1=A2) is also TRUE when it shouldn't be.

But in such cases, =A1-A2-0 is consistently not zero, and =(A1-A2=0) is consistently FALSE.


[2] http://support.microsoft.com/kb/78113


[3] It is also prudent to explicitly round integer calculations when values might exceed 2^53 (9,007,199,254,740,992) in magnitude.