Calculation/Number Bug in .xls? Any ideas?

[AB]

Hi,

I just stumbled across this problem - i can sort it out using a
rounding calculation but anyway i was wondering if anyone had a clue
why it's happening.

so, could you please let me know why this code fires "Not match"?

if (329970.14 + 1012000) = 1341970.14 then
msgbox "Match"
else
msgbox "Not match"
End if

At the same time if i type in a cell
=(329970.14 + 1012000) = 1341970.14
it returns TRUE...

Any idea?

 

[Nigel]

Check out.....Floating point numbers

http://support.microsoft.com/kb/214118

 

[Charles Williams]

Because binary floating point numbers (which is what Excel and VBA use) do
not correspond exactly to decimal, this kind of test is very unsafe.

You should use something like this instead
if abs((329970.14 + 1012000) - 1341970.14)<0.000001 then

Or use Currency datatype which will give greater accuracy for up to 2
decimal places
if (CCur(329970.14) + CCur(1012000)) = CCur(1341970.14) then

Excel tries to be cleverer, but it does not always work
=(329970.14+1012000)-1341970.14 returns zero, but
=((329970.14+1012000)-1341970.14) returns a very small number


Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[AB]

it just proves what a newbie I am...

It's very good to know - thanks a lot Nigel/Charles!!

 

[Dana DeLouis]

Just another option is the Currency symbol @:

Sub Demo()
If (329970.14@ + 1012000) = 1341970.14 Then
MsgBox "Match"
Else
MsgBox "Not match"
End If
End Sub