Simple Math Formula But With Unexpected Result

[anyremote]

Hi,

I tried to compare 2 same result cell but it doesn't match. I put "=(1.81-1.73)/0.01" in cell A1, "=(1.95-1.87)/0.01" in cell B1 & "=IF(A1=B1,"Same","Not Same")" in cell C1.

The result that I got are "8" for cell A1, "8" for cell B1 & "Not Same" for cell C1.

How come it doesn't match? Did I missing something in my formula

Thanks in advance.

 

[VoG]

This is due to the imprecision of floating point arithmetic. Try for example

=IF(ROUND(A1,8)=ROUND(B1,8),"Same","Not Same")

 

[joeu2004]

I tried to compare 2 same result cell but it doesn't match. I put "=(1.81-1.73)/0.01" in cell A1, "=(1.95-1.87)/0.01" in cell B1 & "=IF(A1=B1,"Same","Not Same")" in cell C1. The result that I got are "8" for cell A1, "8" for cell B1 & "Not Same" for cell C1. How come it doesn't match?

If you temporarily format A1 and B1 as Number with 14 decimal places, you will see that they truly are not exactly 8, and they are not equal.

A1: 8.00000000000001
B1: 7.99999999999998

If you want the results to be accurate to 2 decimal places, the formulas should be:

A1: =ROUND((1.81-1.73)/0.01, 2)
B1: =ROUND((1.95-1.87)/0.01, 2)

The reason is: Excel stores numbers in a binary form, and most non-integers cannot be represented exactly in binary.

 

[anyremote]

This is due to the imprecision of floating point arithmetic. Try for example

=IF(ROUND(A1,8)=ROUND(B1,8),"Same","Not Same")

Work perfectly. Thanks.

If you temporarily format A1 and B1 as Number with 14 decimal places, you will see that they truly are not exactly 8, and they are not equal.

A1: 8.00000000000001
B1: 7.99999999999998

If you want the results to be accurate to 2 decimal places, the formulas should be:

A1: =ROUND((1.81-1.73)/0.01, 2)
B1: =ROUND((1.95-1.87)/0.01, 2)

The reason is: Excel stores numbers in a binary form, and most non-integers cannot be represented exactly in binary.

Understood. Thanks for your explanation.