Calculation/Number Bug in .xls? Any ideas?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AB - 24 Jan 2008 11:00 GMT

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?

Reply to this Message

Nigel - 24 Jan 2008 12:55 GMT

Check out.....Floating point numbers

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

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

> Hi,
>
[quoted text clipped - 15 lines]
>
> Any idea?

Reply to this Message

Charles Williams - 24 Jan 2008 12:57 GMT

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

> Hi,
>
[quoted text clipped - 15 lines]
>
> Any idea?

Reply to this Message

AB - 24 Jan 2008 17:47 GMT

it just proves what a newbie I am...

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

Reply to this Message

Dana DeLouis - 24 Jan 2008 19:37 GMT

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

Signature

Dana DeLouis

> it just proves what a newbie I am...
>
> It's very good to know - thanks a lot Nigel/Charles!!

Reply to this Message