asked on

Freebie: Euro Rounding

With the new Euro currency it is important to know that the rounding rules used in VB DO NOT CONFORM to the rounding rules that must be applied when converting to and from the Euro.

The rules that must be applied for Euros are as follows:
When rounding to an x number of decimals, the last decimal must be:
- Rounded down (i.e. left alone) when the following decimal (if any) is 4 or less.
- Rounded up when the following decimal is 5 or more.

E.g. if rounding to 2 decimal places:
1.23432 rounds to to 1.23
3.39820982 to 3.40
2.285 to 2.29
2.275 to 2.28

VB ,however, conforms to the so-called "Computer's Rule".

Even though the "Computer's Rule" is very similar to the rule used for converting Euros, it is different in one significant aspect.

If after the digit that is to be rounded, the digits following are exactly equal to 5, the value is rounded to the NEAREST EVEN NUMBER.

This will cause such "strange" results as the following:

If rounding to an integer:
0.5 rounds to 0
1.5 rounds to 2
2.5 rounds to 2
3.5 rounds to 4
4.5 rounds to 4

Or, as in the previous example:
2.285 rounds to 2.28, not to 2.29 as it should for Euro conversions.

I'm posting this because it is important that people are aware of this feature of VB and must make sure they work around it if they want to write Euro-compliant code. I'll add comments with some suggestions of code that people can use to ensure this compliance. Because I'm adding suggestions myself I will rate this question 0 points but comments are welcome.

caraf_g

ASKER

The following comment contains a VB 5 form & sample code.

caraf_g

ASKER

(Copy using Notepad and save as Rounding.frm)

caraf_g

ASKER

VERSION 5.00
Begin VB.Form frmRounding
BorderStyle = 3 'Fixed Dialog
Caption = "VB v. Euro Rounding"
ClientHeight = 3015
ClientLeft = 45
ClientTop = 330
ClientWidth = 3015
LinkTopic = "Form1"
MaxButton = 0 'False
MinButton = 0 'False
ScaleHeight = 3015
ScaleWidth = 3015
ShowInTaskbar = 0 'False
StartUpPosition = 2 'CenterScreen
Begin VB.CommandButton cmdStdVBMultiply
Caption = "Multiply"
Height = 375
Left = 120
TabIndex = 13
Top = 1440
Width = 975
End
Begin VB.CommandButton cmdStdVBDivide
Caption = "Divide"
Height = 375
Left = 1200
TabIndex = 12
Top = 1440
Width = 975
End
Begin VB.TextBox txtResult
BackColor = &H8000000F&
Height = 315
Left = 1560
Locked = -1 'True
TabIndex = 10
TabStop = 0 'False
Top = 2595
Width = 1335
End
Begin VB.CommandButton cmdEuroDivide
Caption = "Divide"
Height = 375
Left = 1200
TabIndex = 8
Top = 2160
Width = 975
End
Begin VB.CommandButton cmdEuroMultiply
Caption = "Multiply"
Height = 375
Left = 120
TabIndex = 7
Top = 2160
Width = 975
End
Begin VB.TextBox txtDecimals
Height = 315
Left = 1560
MaxLength = 1
TabIndex = 5
Top = 840
Width = 375
End
Begin VB.TextBox txtSecond
Height = 315
Left = 1560
MaxLength = 9
TabIndex = 3
Top = 480
Width = 1335
End
Begin VB.TextBox txtFirst
Height = 315
Left = 1560
MaxLength = 9
TabIndex = 1
Top = 120
Width = 1335
End
Begin VB.Label lblStandard
Caption = "Standard VB"
Height = 255
Left = 120
TabIndex = 14
Top = 1200
Width = 1335
End
Begin VB.Label lblEuro
Caption = "Euro Compliant"
Height = 255
Left = 120
TabIndex = 11
Top = 1920
Width = 1335
End
Begin VB.Label lblDecimals2
Caption = "Decimals"
Height = 255
Left = 2040
TabIndex = 6
Top = 885
Width = 735
End
Begin VB.Label lblResult
Caption = "Result"
Height = 255
Left = 120
TabIndex = 9
Top = 2640
Width = 1335
End
Begin VB.Label lblDecimals
Caption = "Round to"
Height = 255
Left = 120
TabIndex = 4
Top = 885
Width = 1335
End
Begin VB.Label lblSecond
Caption = "Second Argument"
Height = 255
Left = 120
TabIndex = 2
Top = 525
Width = 1335
End
Begin VB.Label lblFirst
Caption = "First Argument"
Height = 255
Left = 120
TabIndex = 0
Top = 165
Width = 1335
End
End
Attribute VB_Name = "frmRounding"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private Function EuroDivide(dblValue1 As Double, _
dblValue2 As Double, _
intDecimals As Integer, _
dblResult As Double) As String

On Local Error GoTo Err_EuroDivide
dblResult = CDbl(Format(dblValue1 / dblValue2, _
"###############0." & Left("000000000", intDecimals)))
EuroDivide = ""
Exit Function
Err_EuroDivide:
EuroDivide = Err.Description

End Function
Private Function EuroMultiply(dblValue1 As Double, _
dblValue2 As Double, _
intDecimals As Integer, _
dblResult As Double) As String

On Local Error GoTo Err_EuroMultiply
dblResult = CDbl(Format(dblValue1 * dblValue2, _
"###############0." & Left("000000000", intDecimals)))
EuroMultiply = ""
Exit Function
Err_EuroMultiply:
EuroMultiply = Err.Description

End Function
Private Function StandardDivide(dblValue1 As Double, _
dblValue2 As Double, _
intDecimals As Integer, _
dblResult As Double) As String

On Local Error GoTo Err_StandardDivide

Dim dblDecimals As Double
Dim lngResult As Long

dblDecimals = 10 ^ intDecimals
lngResult = dblDecimals * (dblValue1 / dblValue2)
dblResult = lngResult / dblDecimals
StandardDivide = ""
Exit Function
Err_StandardDivide:
StandardDivide = Err.Description

End Function
Private Function StandardMultiply(dblValue1 As Double, _
dblValue2 As Double, _
intDecimals As Integer, _
dblResult As Double) As String

On Local Error GoTo Err_StandardMultiply

Dim dblDecimals As Double
Dim lngResult As Long

dblDecimals = 10 ^ intDecimals
lngResult = dblDecimals * (dblValue1 * dblValue2)
dblResult = lngResult / dblDecimals
StandardMultiply = ""
Exit Function
Err_StandardMultiply:
StandardMultiply = Err.Description

End Function
Private Function GetAddedString(objText As TextBox, strAdd As String) As String

GetAddedString = Left(objText.Text, objText.SelStart) _
& strAdd _
& Right(objText.Text, Len(objText.Text) - _
objText.SelStart - objText.SelLength)

End Function
Private Function GetPastedString(objText As TextBox) As String

Dim strPasteText As String

strPasteText = Clipboard.GetText(vbCFText)
GetPastedString = GetAddedString(objText, strPasteText)

End Function
Private Sub NumericKeyDown(objText As TextBox, _
KeyCode As Integer, _
Shift As Integer, _
CanHaveDecimals As Boolean)

Dim strTestText As String

'This traps Shift + Insert = Paste
If ((KeyCode = 45) And (Shift = 1)) Then
strTestText = GetPastedString(objText)
If strTestText = "." Then
strTestText = "0."
End If
If Not IsNumeric(strTestText) Then
KeyCode = 0
Exit Sub
End If
If Not CanHaveDecimals Then
If InStr(strTestText, ".") > 0 Then
KeyCode = 0
Exit Sub
End If
End If
If Val(strTestText) < 0 Then
KeyCode = 0
Exit Sub
End If
End If

End Sub
Private Sub NumericKeyPress(objText As TextBox, _
KeyAscii As Integer, _
CanHaveDecimals As Boolean)

Dim strTestText As String

'This traps Ctrl + V = Paste
If KeyAscii = 22 Then
strTestText = GetPastedString(objText)
If strTestText = "." Then
strTestText = "0."
End If
If Not IsNumeric(strTestText) Then
KeyAscii = 0
End If
If Not CanHaveDecimals Then
If InStr(strTestText, ".") > 0 Then
KeyAscii = 0
Exit Sub
End If
End If
If Val(strTestText) < 0 Then
KeyAscii = 0
Exit Sub
End If
End If

'Non control codes..
If KeyAscii >= 32 Then
strTestText = GetAddedString(objText, Chr(KeyAscii))
If strTestText = "." Then
strTestText = "0."
End If
If Not IsNumeric(strTestText) Then
KeyAscii = 0
End If
If Not CanHaveDecimals Then
If InStr(strTestText, ".") > 0 Then
KeyAscii = 0
Exit Sub
End If
End If
If Val(strTestText) < 0 Then
KeyAscii = 0
Exit Sub
End If
End If

End Sub

Private Sub cmdEuroDivide_Click()

Dim dblResult As Double

If Val(txtSecond.Text) = 0 Then
txtResult.Text = "Error"
MsgBox "Can't divide by zero!"
Exit Sub
End If

txtResult.Text = EuroDivide(Val(txtFirst.Text), _
Val(txtSecond.Text), _
Val(txtDecimals.Text), _
dblResult)
If txtResult.Text = "" Then
txtResult.Text = CStr(dblResult)
End If

End Sub

Private Sub cmdEuroMultiply_Click()

Dim dblResult As Double

txtResult.Text = EuroMultiply(Val(txtFirst.Text), _
Val(txtSecond.Text), _
Val(txtDecimals.Text), _
dblResult)
If txtResult.Text = "" Then
txtResult.Text = CStr(dblResult)
End If

End Sub

Private Sub cmdStdVBDivide_Click()

Dim dblResult As Double

If Val(txtSecond.Text) = 0 Then
txtResult.Text = "Error"
MsgBox "Can't divide by zero!"
Exit Sub
End If

txtResult.Text = StandardDivide(Val(txtFirst.Text), _
Val(txtSecond.Text), _
Val(txtDecimals.Text), _
dblResult)
If txtResult.Text = "" Then
txtResult.Text = CStr(dblResult)
End If

End Sub

Private Sub cmdStdVBMultiply_Click()

Dim dblResult As Double

txtResult.Text = StandardMultiply(Val(txtFirst.Text), _
Val(txtSecond.Text), _
Val(txtDecimals.Text), _
dblResult)
If txtResult.Text = "" Then
txtResult.Text = CStr(dblResult)
End If

End Sub

Private Sub txtDecimals_KeyDown(KeyCode As Integer, Shift As Integer)
NumericKeyDown txtDecimals, KeyCode, Shift, False
End Sub
Private Sub txtDecimals_KeyPress(KeyAscii As Integer)
NumericKeyPress txtDecimals, KeyAscii, False
End Sub
Private Sub txtFirst_KeyDown(KeyCode As Integer, Shift As Integer)
NumericKeyDown txtFirst, KeyCode, Shift, True
End Sub
Private Sub txtFirst_KeyPress(KeyAscii As Integer)
NumericKeyPress txtFirst, KeyAscii, True
End Sub
Private Sub txtSecond_KeyDown(KeyCode As Integer, Shift As Integer)
NumericKeyDown txtSecond, KeyCode, Shift, True
End Sub
Private Sub txtSecond_KeyPress(KeyAscii As Integer)
NumericKeyPress txtSecond, KeyAscii, True
End Sub

mdougan

Hi,

I was not able to duplicate the errors in rounding that you mentioned in VB. Try this. Put a textbox, a label, and a command button on a form. In the command button's click event put the code:

Label1.Caption = Format(Text1.Text, "###,##0.00")

Then, run through your Euro rounding numbers
E.g. if rounding to 2 decimal places:
1.23432 rounds to to 1.23
3.39820982 to 3.40
2.285 to 2.29
2.275 to 2.28

and you will get the correct results . If you want integers change Format to :

Label1.Caption = Format(Text1.Text, "###,##0")

Then run through your integer conversion examples and you will see that anything .5 and above will round up, anthing less than .5 will round down consistently.

Now, a couple of thoughts on the code above. First, never ever ever use the Val function. It is very unreliable. If you say:
x = Val("1,000")

x will = 1

I always use some code similar to:

Dim x as Single

If IsNumeric(MyString) then
x = CSng(Format(MyString, "###,###,##0.00"))
else
x = 0
end if

Now, you can be sure that x is a Single variable correctly rounded to two decimal positions.

Another rounding error occurs when VB creates a temporary intermediate result. So the following code will cause a problem:

Dim x as Single
Dim y as Single
Dim z as Integer

x = 1
y = 2
z = x/y

Label1.Caption = Format(z, "###,###,##0.00")

z will display 0 because VB created a temporary intermediate variable of Integer type to hold the result of the x/y which caused truncation, not rounding.

In general, if working with variables of different types, I will convert them all to the most precise type (so, if you are mixing Integers and Singles, convert the Integers to Singles, if you are mixing Singles and Doubles, convert the Singles to Doubles) THEN do all of your calculations using variables of the same data type. Then, when you are all done, use Format to format your result to the proper number of decimal places.

Good Luck.

MD

caraf_g

ASKER

Hello mdougan,

The Euro* routines are the routines that round correctly WRT Euro conversions. The Standard* routines are the ones that round according to the standard VB rounding rules.

I am not saying that the VB rounding rules are wrong - just that they are different; but in the case of Euro conversion, the rounding rule is dictated to us and as it is different from VB's rounding rule, in that specific case you could say that VB has got it wrong.

In your example you are using the Format function - that is exactly what I am using in the Euro* routines to create the correct behaviour and it explains why you can't reproduce the problem. If you stick to "simple" aritmetic and conversions you will see it happen. Try:
Dim x As Integer
x = 1 / 2 '(gives 0)
x = 3 / 2 '(gives 2)
x = 5 / 2 '(gives 2)
x = 7 / 2 '(gives 4)

As you have correctly pointed out, using Format will allow you to revert to the more widely used convention and the one that is acceptable for Euro conversion.

I have rejected your answer; not because it is wrong, but because this was intended as a freebie for general information and this way it will remain in the public eye a bit longer. Thanks for your input!

mdougan

Hi,
Thanks. I understood about the Freebie, I just wanted to make sure that people didn't panic thinking VB used strange rounding rules. The problem isn't really that VB's rounding rules are different, they are not, but when it is that VB actually applies rounding rules and when it simply truncates.

As far as I know VB does not round during either implicit or explicit mathmatical conversions in expressions. The only reliable place to force rounding to a specific number of decimal positions is using Format. The code you provided shows extensive implicit mathmatical conversions which could result in truncation, if you didn't shift the decimal position matmatically.

In your example:
Dim x As Integer
x = 5 / 2 '(gives 2)
Label1.Caption = Format(x, "##0") ' this will result in 2

This is because the system looks at the variable that will hold the result, x, and sees that it is an integer, so, it creates an implicit temporary variable of type integer to hold the result of the expression 5/2. Since it's data type is less precise than the actual calculated result it truncates (not rounds) and then assigns to x. No rounding will happen in the format statement, because x (integer) will have no decimal positions to round.

if you changed it to
Dim x As Single
x = 5 / 2

Label1.Caption = Format(x, "##0") ' this will result in 3

You will get 3 instead of 2. Because the implicit temporary variable will have a Single data type and no truncation will occur. However, rounding will occur in the format statement with the desired results.

To complicate things a little further. VB has a currency data type for which the rounding rules are different!

Anyway, not to nitpick, but I think it is important for people to know the nature of how things are working behind the scenes so they can predict the behavior of their code.

And please, please replace your Val statements with a conversion using one of the CInt, CSng or CDbl functions!

MD

vikiing

Friend Caraf_g: you said "I am not saying that the VB rounding rules are wrong - just that they are different".

In fact, they are SO DIFFERENT, that they adheres to no existing standard. These rounding rules (also known as "Banker method") tend to distribute rounding of decimals in a more even way; but, unfortunately, almost the whole world rounds as 5, this is, if decimal is 4 or lower, rounds downwards; if 5 or higher, upwards.

I remember when we had (circa 1986) a change of currency, when we used decimals down to HALF A CENT.

Thus, we spoke with "milli-pesos", 'cause lowest unit was $0.001, althoug the lowest fraction was actually $0,005 , and there weren't other sub-unit.

Our geniuses at Ministry of Economy then dictated a very crazy way to round:

From 0 to 3: down to 0
From 4 to 6: 5
From 7 to 9: up to next digit

Of course, that madness couldn't be achieved just in time by all computer systems, and they had to go back.

It's a very well known fact that when you apart away from standards, you're calling for problems.

caraf_g

ASKER

mdougan,

Believe me, VB does not truncate, it rounds. And as vikiing points out, it does use the Banker Method (Which I called the Computer's Rule - n.b. "Computer" in this phrase is a person who performs computations, e.g. an accountant)

You've managed in your example to steer away from the problem area. Instead of using 5 / 2 in your example, try 7 / 2 and see what happens!

In your example:
Dim x As Integer
x = 7 / 2 '(gives 4)
Label1.Caption = Format(x, "##0") ' this will result in 4

So, if you leave x as an Integer, you will get the odd situation that in some cases
x = 5 / 2
x = 9 / 2, etc. ..
it seems to truncate, but in others
x = 7 / 2
x = 11 / 2, etc. ..
it seems to round up!

The fact that VB uses the Computer's Rule explains this strange behaviour.

Initially I felt, like vikiing, that VB had simply got it wrong, but when I investigated the matter further, I found out that not only is the Computer’s Rule a well known method of rounding, it has supporters in many places. Even the literature cannot seem to agree on "the one and only" rounding method:

Truncation and rounding are described in Michael Sullivan Algebra & Trigonometry, 4th Edition (p. 19) ISBN 0-13-370149-2
"In approximating decimals, we either round off or truncate to a given number of decimal places. This number of places establishes the location of the first digit in the decimal approximation.
Truncation: Drop all the digits that follow the specified final digit in the decimal.
Rounding: Identify the specified final digit in the decimal. If the next digit is 5 or more, add 1 to the final digit; if the next digit is 4 or less, leave the final digit as it is. Now truncate following the final digit."

The computer's rule is described in James & James Mathematics Dictionary, 5th Edition (p. 366) ISBN 0-412-99041-5
"ROUND'ING, n. rounding off. Dropping decimals after a certain significant place. When the first digit dropped is less than 5, the preceding digit is not changed; when the first digit dropped is greater than 5, or 5 and some succeeding digit is not zero, the preceding digit is increased by 1; when the first digit dropped is 5, and all succeeding digits are zero, the commonly accepted rule (computer's rule) is to make the preceding digit even, i.e. add 1 to it if it is odd, and leave it alone if it is already even. E.g. 2.324, 2.316, and 2.315 would take the form 2.32, if rounded off to two places."

I therefore cannot say that VB has got it right or VB has got it wrong, but I can try and make people aware of the fact that there are different rounding conventions out there and that VB is not very consistent.

To recap, if you simply perform an arithmetic operation and move the result into an integer, be aware that VB performs the Computer’s Rule for rounding the result. If you want to avoid this, use, e.g., a Double and use Format which rounds according to the simpler rounding convention.

Thank you both for your comments!

mdougan

Wow! I stand corrected. That is definately a convoluted rounding convention. Thank you for taking the time to research it and explain it so clearly.

MD

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

caraf_g

ASKER

Thanks - that will make this a PAQ then.

caraf_g

ASKER

martike - correct! The rounding rules apply to conversions between member currencies and the Euro.

Conversion from any other currency to Euro is at your own discretion.

On the other hand though, my guess is that if you wish to convert from, e.g., US Dollars to Dutch Guilders, you must do so via a triangulation method through the Euro. It is up to you how you are going to round for the conversion from Dollars to Euros, but the subsequent conversion from Euros to Guilders must be done conform the "rule". But I'll be honest and admit that this is pure conjecture on my part.

vikiing

Martike: that article says "This spelling without an "s" may be seen as departing from usual English practice for currencies", because in another laguages they accept a plural form (as in Spanish, "euro" ==> "euros").

Even in the case a plural wouldn't had been defined, each country has its own practices and rules, and surely any Spanish-speaking country would use "euros", according to Spanish grammar.

vikiing

Martike: I said "in another languages, they accept a plural form", as stated in the article at the URL you've mentioned.

Inteqam

wow

caraf_g

ASKER

Someone (I think it was ameba) mentioned in another thread that my numeric only text box code allowed for the use of the right mouse button to bring up a pop-up menu, from which Paste can be chosen to paste any (even non-numeric) text into the text box)

This can be avoided as follows.

1 On the mousedown event, check the mouse button. If it is the right button....
2 Check the contents of the clipboard against the contents of the text box. If pasting this in the current location would cause non numeric data....
3 blank out the contents of the clipboard.

Private Sub CMSSingle1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)

Dim intKeyCode As Integer
Dim intShift As Integer

If Button = 2 Then
'Check whether pasting would be OK
intKeyCode = 45
intShift = 1
'The following tests whether pasting the current contents
'of the clipboard would be OK.
CMSSingle1_KeyDown intKeyCode, intShift
If intKeyCode = 0 Then
Clipboard.SetText ""
End If
End If

End Sub