Round function - how it REALLY works  | Expert | | Join Date: Sep 2006 Location: Israel
Posts: 181
| |
May be somebody can explain to me how Round function works in Access. This article claims VBA6 uses banker's rounding, which means that exact half rounds to the closest even digit. This article claims VBA6 uses banker's rounding ONLY when rounding to integer.
So how do I get Round(2.245, 2) = 2.24 while Round(1.245, 2) = 1.25 ?
I use both Acc2K and Acc2K2.
|  | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Round function - how it REALLY works
Quote:
Originally Posted by dima69 May be somebody can explain to me how Round function works in Access.
This article claims VBA6 uses banker's rounding, which means that exact half rounds to the closest even digit.
This article claims VBA6 uses banker's rounding ONLY when rounding to integer.
So how do I get Round(2.245, 2) = 2.24 while Round(1.245, 2) = 1.25 ?
I use both Acc2K and Acc2K2. Hi, dima.
As far as I've understood you, you mean rounding to lowest/uppest digit depending on parity of integer part.
If so, the function below does it. -
Public Function Round1(ByVal dblInput As Double, ByVal intDigits As Integer) As Double
-
-
Dim intParity As Integer
-
-
intParity = Int(dblInput) - Int(Int(dblInput) / 2) * 2
-
Round1 = Int(dblInput * 10 ^ intDigits + intParity / 2) / 10 ^ intDigits
-
-
End Function
-
|  | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 3,000
| | | re: Round function - how it REALLY works
Actually, I didn't read the second article as saying that VBA uses banker's rounding ONLY when rounding to integer. What I read was that if the integer portion of the number was even Access would round down, which is to say the same thing the first article said, that Access always rounds to the closest even integer.
Here's a function that I think will do what you want. Place it in a standard module. If you don't have a standard module to place it in, create one for it. Just remember do not name the module the same name as the function! This confuses Access! - Public Function RoundTotal(ByVal dblNumber As Double, ByVal intDecimals As Integer) As Double
-
-
' : 0.5 is rounded up
-
'Parameters : dblNumber - number to round
-
' : intDecimals - number of demal places
-
' to round to
-
-
' : (positive for right of decimal, negative for left
-
'Returns : Rounded number
-
-
Dim dblFactor As Double
-
Dim dblTemp As Double ' Temp var to prevent rounding problems in INT()
-
-
dblFactor = 10 ^ intDecimals
-
dblTemp = dblNumber * dblFactor + 0.5
-
RoundTotal = Int("" & dblTemp) / dblFactor
-
-
End Function
The, in code, call it just like Round() except use
RoundTotal( YourNumberToRound, NumberOfDigits)
Linq ;0)>
Edit: Sorry, Fish, got distracted mid-post! ;0)>
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Round function - how it REALLY works
Nice hint, Link.
I mean this - converting number to string before passing to Int function.
I was near trying to solve the problem with Format function, but your solution is really nice. -
Public Function Round1(ByVal dblInput As Variant, ByVal intDigits As Integer) As Double
-
-
Dim intParity As Integer
-
-
intParity = Int(dblInput) - Int(Int(dblInput) / 2) * 2
-
Round1 = Int(Str(dblInput * 10 ^ intDigits + intParity / 2)) / 10 ^ intDigits
-
-
End Function
-
| | Expert | | Join Date: Sep 2006 Location: Israel
Posts: 181
| | | re: Round function - how it REALLY works
Linq, FishVal - thanks for the tips, but I think you've got me wrong. What I am looking for is to understand how an existing Round function works, cause it seems that it works differently than Banker's round (see examples in my first post).
I was unaware of that weird behavior before, so now if I change the standard Round to something else, this may cause existing invoices to change :)
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Round function - how it REALLY works
Quote:
Originally Posted by dima69 Linq, FishVal - thanks for the tips, but I think you've got me wrong. What I am looking for is to understand how an existing Round function works, cause it seems that it works differently than Banker's round (see examples in my first post).
I was unaware of that weird behavior before, so now if I change the standard Round to something else, this may cause existing invoices to change :) It does Banker's rounding - rounds to the nearest even digit
Round(2.245, 2) = 2.24
Round(2.255, 2) = 2.26
From your example Quote:
Originally Posted by dima69 So how do I get Round(2.245, 2) = 2.24 while Round(1.245, 2) = 1.25 ? I understood that you expect it to round "half" to lower digit if integer part is even, and to upper digit if it is odd. Am I right?
To the best of my knowledge Round function doesn't work so, as well as any other standard rounding function. :)
| | Expert | | Join Date: Sep 2006 Location: Israel
Posts: 181
| | | re: Round function - how it REALLY works
Quote:
Originally Posted by FishVal It does Banker's rounding - rounds to the nearest even digit
Round(2.245, 2) = 2.24
Round(2.255, 2) = 2.26
From your example
I understood that you expect it to round "half" to lower digit if integer part is even, and to upper digit if it is odd. Am I right? NO. Nothing to do with Integer part parity.
Following the nearest even digit algorithm, I expect Round(1.245, 2) = 1.24 (4 is the nearest even digit) - am I right ?
But what I really get is 1.25 !
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Round function - how it REALLY works
Quote:
Originally Posted by dima69 NO. Nothing to do with Integer part parity.
Following the nearest even digit algorithm, I expect Round(1.245, 2) = 1.24 (4 is the nearest even digit) - am I right ?
But what I really get is 1.25 ! Weird.
Really when NumDigitsAfterDecimal>0, rounding direction is somewhat unpredictable. I suppose that this caused by VBA math precision.
As for me I would prefer to use smthng like this to get result of Banker's rounding. -
Public Function Round2(ByVal dblInput As Variant, ByVal intDigits As Integer) As Double
-
Round2 = Round(Str(dblInput * 10 ^ intDigits), 0) / 10 ^ intDigits
-
End Function
-
| | Expert | | Join Date: Sep 2006 Location: Israel
Posts: 181
| | | re: Round function - how it REALLY works
Amaizing !
When data type is Currency or Decimal, Round works as it supposed to (Banker's round).
But when the data type is double, Round result is something undefined !
For example:
Round(CCur(1.245) ,2) = 1.24 (as expected)
Round(CDbl(1.245) ,2) = Round(1.245 ,2) = 1.25 ! Wrong !
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
