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?

Check out.....Floating point numbers

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

--

Regards,
Nigel
(E-Mail Removed)



"AB" <(E-Mail Removed)> wrote in message
news:d137e904-0967-41d3-a7d8-(E-Mail Removed)...
> 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?

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" <(E-Mail Removed)> wrote in message
news:d137e904-0967-41d3-a7d8-(E-Mail Removed)...
> 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?

it just proves what a newbie I am...

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

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

--
Dana DeLouis

"AB" <(E-Mail Removed)> wrote in message
news:f5135ec7-df2c-4329-826b-(E-Mail Removed)...
> it just proves what a newbie I am...
>
> It's very good to know - thanks a lot Nigel/Charles!!