
May 29th, 2009, 02:47 PM
#31
Re: INT Function Rounding Problems
I believe the problem is actually twofold. One is floating point error, making the result of the multiplication slightly off. The other is banker's rounding.
Try it like this, and the two give the same answer:
Code:
Private Sub Command1_Click()
Dim TotVal As Currency
Dim VatRate As String
VatRate = "15"
TotVal = 3.1
MsgBox "Total VAT = " & ((TotVal * Val(VatRate)) + 0.5) / 100
MsgBox "Total VAT = " & Int((TotVal * Val(VatRate)) + 0.5) / 100
End Sub
This demonstrates banker's rounding.
The FormatNumber() function does not use banker's rounding, but if you're dealing with financial data, that may not be appropriate. The Currency data type would be the way to go, along with using only numeric data types in mathematical formulas.
Please remember to rate the posts and threads that you find useful.
How can something be both new and improved at the same time?

July 28th, 2009, 05:32 AM
#32
Re: INT Function Rounding Problems
You want wierd try this very simple INT
Dim subtotal As Double
Dim vat As Double
subtotal = 12
vat = 180
vat = Int(vat)
vat = subtotal * 0.15
vat = 100 * vat
vat = Int(vat)
You will see that if you int the 180 it remains 180
But if you int 180 ont he line "vat=int(vat) then its 179.
Even more wierd try this: (and believe me this is impossible)
Dim subtotal As Double
subtotal = 19.99
subtotal = subtotal * 100
subtotal = Int(subtotal)
the result is 1998
however if you put any other number in (ie 19.98, 29.99, 99.99) then it gets the answer right the only one that goes wrong is 19.99.

July 28th, 2009, 06:18 AM
#33
Re: INT Function Rounding Problems
Yes uknod, the Double data type is subject to floating point error. Try it with the Currency data type, and you will see there is no such error.
Please remember to rate the posts and threads that you find useful.
How can something be both new and improved at the same time?

July 28th, 2009, 08:29 AM
#34
Re: INT Function Rounding Problems
I understand the floating point error, but why ONLY on 19.99 and not any other number?

July 28th, 2009, 08:41 AM
#35
Re: INT Function Rounding Problems
Originally Posted by uknod
I understand the floating point error, but why ONLY on 19.99 and not any other number?
Check this.
Code:
Private Sub Form_Load()
Dim x As Integer
subtotal = 19.99
subtotal = subtotal * 100
x = subtotal
subtotal = Int(x)
Stop
End Sub
or
Code:
Private Sub Form_Load()
Dim x As Currency
subtotal = 19.99
subtotal = subtotal * 100
x = subtotal
subtotal = Int(x)
Stop
End Sub
or
Code:
Private Sub Form_Load()
Dim x As Single
subtotal = 19.99
subtotal = subtotal * 100
x = subtotal
subtotal = Int(x)
Stop
End Sub
Last edited by Ash Katchup; July 28th, 2009 at 08:46 AM.

July 28th, 2009, 08:58 AM
#36
Re: INT Function Rounding Problems
Yes but the question is why is 19.99 so different, to any other. Check this out.
Dim x As Double
subtotal = 29.99
subtotal = subtotal * 100
x = subtotal
subtotal = Int(x)
or
Dim x As Double
subtotal = 19.99
subtotal = subtotal * 100
x = subtotal
subtotal = Int(x)
only the 19.99 fails ANY other number is OK.

July 28th, 2009, 09:01 AM
#37
Re: INT Function Rounding Problems
Originally Posted by uknod
Yes but the question is why is 19.99 so different, to any other. Check this out.
Dim x As Double
subtotal = 29.99
subtotal = subtotal * 100
x = subtotal
subtotal = Int(x)
or
Dim x As Double
subtotal = 19.99
subtotal = subtotal * 100
x = subtotal
subtotal = Int(x)
only the 19.99 fails ANY other number is OK.
I can't tell you why, but i know that you can workaround using Single.

July 28th, 2009, 11:32 AM
#38
Re: INT Function Rounding Problems
Both Single and Double data types are subject to floating point errors.
Please remember to rate the posts and threads that you find useful.
How can something be both new and improved at the same time?

July 28th, 2009, 12:18 PM
#39
Re: INT Function Rounding Problems
Originally Posted by WizBang
Both Single and Double data types are subject to floating point errors.
But int(csng(19.99*100)) = 1999
and int(cdbl(19.99*100)) = 1998

July 28th, 2009, 12:47 PM
#40
Re: INT Function Rounding Problems
Try it this way:
Int(CSng(19.99)*100)
Please remember to rate the posts and threads that you find useful.
How can something be both new and improved at the same time?

July 28th, 2009, 01:25 PM
#41
Re: INT Function Rounding Problems
Originally Posted by WizBang
Try it this way:
Int(CSng(19.99)*100)
Hehehe, i missed that. Learning about VB bugs
Tags for this Thread
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
OnDemand Webinars (sponsored)
