Int(X * 10 ^ Y) gives unpredictable results

[Yannick59000]

Hi, all, I'm trying to use the following function in Excel 2003's VBA:

Function myTest(ByVal X As Double, ByVal Y As Integer) As Double
myTest = Int(X * 10 ^ Y)
End Function

Nothing simpler, uh? Yet, try as I may, I cannot figure out the reason why
myTest(0.12, 5) gives 11,999 instead of 12,000.

If we do a step by step:

X = 0.12
Y = 5
10^Y = 10^5 = 100,000
X*Y = 0.12 * 100,000 = 12,000
INT(X*Y) should be 12,000...

Can anyone crack this for me...?

Thanks for your help,

-Yannick.

 

[Chirag]

Rounding errors. You might want to use Round() instead of Int() as:

Function myTest(ByVal X As Double, ByVal Y As Integer) As Double
myTest = Round(X * 10 ^ Y)
End Function

- Chirag

 

[Yannick59000]

Thanks, Chirag, I'll look into this. Int() and Round() are different
functions and I need to check how they will behave in the whole code (as you
can expect, my actual function is more complex than the three lines I have
submitted )

-Yannick.

 

[Tushar Mehta]

All digital computers have limits in the precision of certain numbers (just
like the decimal system cannot represent 1/3 completely). If you are
interested in learning more see
http://support.microsoft.com/default.aspx?scid=kb;en-us;78113 and
http://support.microsoft.com/default.aspx?scid=kb;en-us;42980 or search
google.

In your specific case, why are you use Int to essentially truncate a number
if the returned result type is supposed to be a double?

The foll. works just fine:

Function myTest(ByVal X As Double, ByVal Y As Integer) As Double
myTest = X * 10 ^ Y
End Function

as does

Function myTest(ByVal X As Double, ByVal Y As Integer) As Long
myTest = X * 10 ^ Y
End Function

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions