INT Function Rounding Problems

I have just spent the last few hours trying to find a problem where applying rounding after adding VAT was coming up with an odd few pence difference. Not that unusual you might think until I tripped over the code that was actually causing the porblem.

I have recreated the problem as an example as follows.....

Private Sub Command1_Click()
Dim TotVal As Currency
Dim VatRate As String

VatRate = "15"

TotVal = 2.1

MsgBox "Total VAT = " & ((TotVal * Val(VatRate)) + 0.5) / 100
MsgBox "Total VAT = " & Int((TotVal * Val(VatRate)) + 0.5) / 100
End Sub

The first message box returns 0.32 as expected (2.1 * 15) = 31.5, +0.5 = 32, /100 = 0.32

However for some unknown reason the second message box using the INT function returns 0.31 !!! Basically according to this code the interger portion of 32 is 31.

Just to complicate matters, if I use a double instead of a currency variable then it returns the right result and if I use CINT again all is fine.

In my original program I am using a string for the VAT rate as it is stored in a flexgrid

I have found a work around by putting the VAT rate into a currency variable as well but I would like to know what is likely to be causing this kind of error.

Integers are used for whole numbers.

I'd actually recommend you stcik with double / currency when dealing with floating point numbers, as they are intended to do so. Currency, IMHO, will give you even more preciseness than double.

Yes, but now wait. This is rather an interesting oservation which I'd like to have explained, too.
This is really weird, in fact, (or I'm suffering from acute blindness).
Look:
We have a string VatRate, which is 15.
Debug.print int(totval * val(VatRate) + 0.5) gives you 31 ????? How is that.
If we resolve Val(VatRate) to 15 manually we get
Debug.Print Int(totval *15 + 0.5) and we get 32 as expected.
???????????
I tried this up and down now, and still wondering...

It gets weirder still. If Dim TotVal As Currency is changed to Dim TotVal As Double it does return 32 as expected!

Sorry, missed it in the first post, you have seen the double already.

How about Floor/Ceil?

Code:

---

Option Explicit

Private Sub Form_Load()
  MsgBox RoundUp(123.3456, 1)
  MsgBox "Ceiling " & Ceil(3.2)
  MsgBox "Floor " & Floor(3.2)
End Sub

Public Function RoundUp(dblNbr As Double, intPlaces As Integer) As Double
' When intPlaces is 0 it rounds up to the nearest whole number
' When intPlaces is 1 it rounds up to the nearest tenth
' etc.
    Dim intFactor As Integer
    intFactor = 10 ^ intPlaces
    RoundUp = (Int((dblNbr * intFactor) + 0.5) + 1) / intFactor
End Function

Public Function Ceil(ByVal Number) As Long
    If Number >= 0 Then
        If Number = Int(Number) Then
            Ceil = Number
        Else
            Ceil = Int(Number) + 1
        End If
    ElseIf Number < 0 Then
        Ceil = Int(Number)
    End If
End Function

Public Function Floor(ByVal Number) As Long
    Floor = Fix(Number)
End Function

---

Well, all this is really strange and I never noticed that before.
Your Ceil function does not get to the ground of it, as I see it, David. It is logical and works as expected.

Let's see.

Code:

---

  Dim tv As Currency
  tv = 2.1
  Debug.Print Int(tv * Val("15") + 0.5)

---

The above code prints 31, which is wrong.
Do I change tv as Double, it prints out 32, which is right.
BUT: Do I change Val("15") against 15 (which is the correct evaluation), the result is 32, too.???
Where is the error?

half wasn't getting the right value. try this.... Prints 32

Code:

---

Private Sub Form_Load()
    Dim tv As Currency
    tv = 2.1
    half = 0.5
    Debug.Print Int(tv * Val("15") + half)
End Sub

---

Are you saying the literal 0.5 is in fact converted to 0 when I use Val("15") and is 0.5 when I use 15 literally???
Really strange.
Hm... I played a little with your suggestion and thought, you have not declared half.
Go and dim half as currency and it prints 32. Dim half as double and it prints 31. :)
Why is this?
Change Val("15") with 15 and it prints 32 again...
It seems you are on the right track. depending on the previous operand the type of "half" or 0.5 is converted. If it is converted to Integer, we lose it, because Int(0.5) is 0.

Well it's nice to see that I am not going completely mad.

WoF's code:
Dim tv As Currency
tv = 2.1
Debug.Print Int(tv * Val("15") + 0.5)

This will indeed return 31 but if you remove the INT then it returns 32! Also CINT returns 32.

This is obviously something weird with the INT function. If I step through my code and debug print any part of the calculation it returns exactly what I expect until the INT gets it's grubby hands on it.

I am trying to avoid using double variables as I know the problems these can have problems with numbers ending up with .0000000001 or .999999999999.

Looks like the 0.5 should be stored as a currency to avoid doubles but then the VAL function will return a double anyway.

Go Figure!

Well, a solution seems to be, not to evaluate Val("15") within the Int() function, but to compute it before to a currency.
(Anyway I donm't understand why you declare VatRate a string.)
If you want VatRate to be a string for some reason, then

Code:

---

  Dim curVatRate as Currency
  Dim TotVal as Currency

  curVatRate = Val(VatRate)
  TotVal = 2.1

  Debug.Print Int(TotVal * curVatRate + 0.5)

---

Although this is a solution to your problem, it does not explain (yet) why this happens.

I was using the VAT as a string because in the project that I found this problem the VAT is being stored in a flexgrid. Basically the grid holds details of each individual line of charges of an invoice and each line may be zero or 15% VAT (or indeed 17.5% if it is an older invoice).

This little nugget was buried way down iin the code and it was only after detailed inspection of an entire monthly invoice run that I found out where these odd pennies were disappearing to.

I agree there are a number of possible solutions/workarounds but the problem itself still seems somewhat curious. It would appear that CINT may be the way forward.

I will have to try this on .NET to see if this is unique to VB6.

Yes, please report on your findings.
I'd be curious about the reason of this. I'm quite confident that it has to do with the inherent type conversions of the parts of the entire expression.
If we have the Val("15") within the expressions, the result turns out ot be wrong.
If 15 is given literally, then we get the right result.
Strange.

Well, I've had a little play on this one with vb2008 and it seems to work fine (with these numbers anyway) but then 2008 doesn't have a currency type any more so I was using the decimal data type.

All I can really say on this one is that it definitely looks like a glitch within the INT function which is strange because you would have thought that Microsoft would have got that right by now!

Anyway thankyou all for your input, I think I will find a dark room to sit in for a while.

We have a winner!

Banker's Rounding

Quote:

---

When you add rounded values together, always rounding .5 in the same direction results in a bias that grows with the more numbers you add together. One way to minimize the bias is with banker's rounding.

Banker's rounding rounds .5 up sometimes and down sometimes. The convention is to round to the nearest even number, so that both 1.5 and 2.5 round to 2, and 3.5 and 4.5 both round to 4. Banker's rounding is symmetric.

In Visual Basic for Applications, the following numeric functions perform banker's rounding: CByte(), CInt(), CLng(), CCur(), and Round().

There are no Excel spreadsheet functions that perform banker's rounding.

---

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

Seems like I completely mis read the entire question :blush:
Sorry Killa, and everyone!

This is indeed very interesting findings :thumb:
Great thread!

Yes, and not yet finished.
@David, I'm not sure if this explins what happens here.
Remember the core question is
Why is Int(tv * 15 + 0.5) correctly 32 and
why is Int(tv * Val("15") + 0.5) wrongly 31
Val("15") can only evaluate to 15, but I think it depends what datatype this 15 is gonna be.
If tv is a double (not a currency) Val("15") as a multiplier evaluates to a double, too, and the result is correct.
I suspect when tv is a currency, Val("15") becomes a currency, too, as well as 0.5, and then and only then we get the error.
Only I can't explain then, why the result is ok when you give 15 literally...
It's really a little disquieting.

The Val function returns a double.
If this is the case then I would assume that the tv variable (if it is a currency) would have to be converted to a double as double variables have a much larger range.
Converting the result of the val function to a currency may result in overflow errors whereas converting the tv to a double would only affect the accuracy of the number.

WoF: The question is also why if tv is a currency variable Int(tv * Val("15") + 0.5) returns 31 but (tv * Val("15") + 0.5) returns 32. Basically int(32)=31!

Right, right you are.
And now after this one gave me the willies for days now, I'll add to this some more.

Code:

---

dim tv as Single
tv = 2.1
debug.print tv * Val("15") 'prints 31.4999985694885
debug.print tv * 15          'prints 31.5

---

Maybe there is the reason. We get conversion to a single where it is not 31.5 but 31.49...
Then the rounding of the Int() function gets it and cuts off to 31.
Then Adding 0.5 makes 31.5 and the final rounding of the Int() function drops bakc to 31.

Well I'm not sure, but it is something like this.

Didn't you read my post?

To make them equal, I added cInt()

Code:

---

Private Sub Form_Load()
    Dim tv As Single
    tv = 2.1
    Debug.Print tv * CInt(Val("15"))
    Debug.Print tv * 15
End Sub

---

David, I read your post full well.
Now your introduction of CInt() produces a new aspect of weirdness. :)
Try this

Code:

---

    Dim tv As Single
    tv = 2.1
    'Debug.Print tv * CInt(Val("15"))
    'Debug.Print tv * 15
    Debug.Print Int(tv * CInt(Val("15") + 0.5))
    Debug.Print Int(tv * CInt(15) + 0.5)

---

If you remove the Int function in fornt of the expressions you'll see some binary rounding error for sure.

Adding cInt() does it again. What answer do you want?

Code:

---

Private Sub Form_Load()
    Dim tv As Single
    tv = 2.1
    Debug.Print tv * CInt(Val("15")) ' 31.5
    Debug.Print tv * 15 ' 31.5
    Debug.Print Int(tv * CInt(Val("15") + 0.5)) ' 33
    Debug.Print Int(tv * CInt(15) + 0.5) ' 31
    Debug.Print CInt(tv * CInt(15) + 0.5) ' 32
    Debug.Print (tv * CInt(Val("15") + 0.5)) ' 33.6
    Debug.Print (tv * CInt(15) + 0.5) ' 31.9999985694885
End Sub

---

Yes, I admitted already that CInt does it. There were already two more solutions how to avoid the problem, but there was no real explanation, why that darn error happens.

I posted the latest code, along with the link to my group. They didin't understand what the question is? Then they pointed to the link I gave before explaining Banker's Rounding. Int() does not, but Cint() does use it.

Ok, I will not longer insist in this, since the reason of this might be the bankers or no bankers rounding.

So in memory of this code:

Code:

---

dim tv as Single
tv = 2.1
debug.print tv * Val("15") 'prints 31.4999985694885
debug.print tv * 15          'prints 31.5

---

I will issue the warning never to use the Val() function within an expression where rounding takes place.
Because the full integral value of 15 is 'rounded' down to 14.9999999 (something like that)
What I wanted to point out is, the Val() function should NOT do any (un)rounding at all, bankers or no.

I think Val() evaluates as a Long, which is the rounding problem.

?typename(val("1"))
Double

I ran some tests. Val("15") returns 15. The problem only seems to occur here when tv is defined as a single and the val() statement is used without being converted to a specific type. Vb is guessing at what type of var should be returned and apparently in this case it is incorrect.

If I use a double or currency it returns correctly, if I place the val() inside a csng() or cint() it returns correct. If I place it inside a clng() or Ccur() or Cdbl() it shows the issue.

It would seem that one should assign the result of the Val() statement into the proper variable type before doing math operations on the data returned by this function.

Yes, that's what I concluded, too:
Do not use Val() inside expressions with different datatypes.

Val function is Evil.:D

Try
? val("3,512.12")


A good reason not to use it.

Yes, well it is known that the val function stops at the next nonnumerical character, which - in this case - is the comma. That's not very good, but it's a well known fact.
What I did not know is, that it will fail at evaluating an integer to the point, when used within an expression of other numeric data types.
Since other type conversion functions like CCur(), CInt(), CDbl() take string type arguments as well, your advice not to use Val() is not wrong, though. :)

I believe the problem is actually two-fold. 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.

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.

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.

I understand the floating point error, but why ONLY on 19.99 and not any other number?

Quote:

---

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

---

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.

Quote:

---

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.

Both Single and Double data types are subject to floating point errors.

Quote:

---

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

Try it this way:
Int(CSng(19.99)*100)

Quote:

---

Originally Posted by WizBang

Try it this way:
Int(CSng(19.99)*100)

---

Hehehe, i missed that. Learning about VB bugs :)