True or False

[gr8posts]

Is it true that excel can't calculate
(16,51 - 16)
What should be the result of:

=IF((16,51-16)=((1651-1600)/100);"TRUE";"FALSE")

Thank you for your attention

 

[Bob Umlas]

Excel (and all computers) work in binary. 16.51-16 yields 0.510000000000002
and (1651-1600)/100 yields 0.51, so they're different. This kind of thing
isn't new. If you need this kind of calculation, use either precision as
displayed or the ROUND function.

 

[gr8posts]

Thank you Bob for your quick answer.
I know that computers work in binary but I hope we will agree that this does
not mean that they are not able to do simple calculations.

Why don't you try same thing in VBA

If (16.51 - 16) = ((1651 - 1600) / 100) Then
MsgBox "Ok"
Else
MsgBox "Error"
End If

Do you know of any rules when to use ROUND and when we can trust
calculations with figures ?

Maybe issue is not new to you but it is very serious to my opinion not
knowing when you can trust software it gives the correct answers.

Thanks again for your time.
Thanassis

 

[gr8posts]

Problem solved:
Just to inform you that I found the explanation of this behaviour
It is because of floating arithmetic used by Excel
see http://support.microsoft.com/kb/78113

My approach and recomendation is:
If ROUND functions or Precision as Displayed approach is not convenient to
avoid your problem:
Instead of using =IF(A1=A2;TRUE;FALSE) use IF(ABS(A1-A2)<1/10^n;TRUE;FALSE)
Where n is the precision you are interested for maximum Excel precision use
n=14

T.

 

[Chip Pearson]

Maybe issue is not new to you but it is very serious to my opinion not
knowing when you can trust software it gives the correct answers.

Computers have limitations, and users should be aware of such
limitations (but, in reality, very few users are). It is and has been
since the early years a standard programming practice to test for a
very small difference and if that difference is less than some
threshold, consider the result equal. Your example could be rewritten
as

If Abs((16.51 - 16) = ((1651 - 1600) / 100)) < 0.000001 Then
MsgBox "Ok"
Else
MsgBox "Error"
End If

In this code, a difference less than 0.000001 is considered equality.
Many programming languages define a constant for this threshold, but
VB/VBA does not.

Such limitations exist in the real world as well. Consider

(1/3)+(1/3)+(1/3)

If you calculate this to any finite number of decimal places, it will
never equal 1. No matter what, it will calculate to 0.999..... which
is certainly not 1. Are the foundations of Arithmetic flawed? No, it
is a computational limitation, and isn't "wrong" in any sense.

If you do the math symbolically (and there are programs that do this,
at the expense of performance), you can achieve equality. But if you
calculate the (1/3) values, you'll never get the "right" result. The
world has gotten along rather well over the last few thousand years
with "problems" like this.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[gr8posts]

Computers have limitations, and users should be aware of such

limitations (but, in reality, very few users are).

In did: very few users are aware of limitations.

Thank you for your detailed explanations Chip.

Just one correction if I may in your proposal for VBA code replacement.
I think there it is a typing mistake.
The "=" (equal) sign should be "-" (minus) otherwise Abs(False)<0.000001
will evaluate always to true.

"If Abs((16.51 - 16) - ((1651 - 1600) / 100)) < 0.000001 Then"

Thank you for your time.
Thanassis