yet another rounding issue.. (1 Viewer)

CrystalSurfer

Matrix activist
Local time
Today, 10:13
Joined
Jan 11, 2006
Messages
75
Hi All,
I use the following generally available function for rounding.
I thought it cured the problem with 'Bankers' rounding which is Access' default method of rounding.
But can someone tell me why, when using this function on the value 4.725 it returns 4.72 when it should return 4.73?
I am using it in this instance within a query to round off (to the nearest) a sales tax (vat) calc. ie:
vbaRound(Abs(qryInvoiceExport02.NetAmount)*0.175,2)
As Im not specifying a parameter to round up or down the function will by default round off.
I've broken down the single line of code into constituent parts to see whats happening and what happens is the "Int(dblValue)" on the 3rd line contains what I expect (473) but after execution vbaRound contains 4.72!!

Am I missing something obvious here?
Thanks.


Code:
Public Function vbaRound(dblValue As Double, intDecimals As Integer, _
                         Optional RoundOpt As enumRoundOpt = rNearest) As Double
'Public Function vbaRound(dblValue As Double, intDecimals As Integer,

'*******************************************************************
' PURPOSE: To replace VBA's Round() function
'
' AUTHOR:
'
' ARGUMENTS:
' 1) dblValue - Value to Round
' 2) intDecimals - Decimal places to round to (any positive integer)
' 3) enumRoundOpt - To specify whether to round to nearest, up or down.
'    a) Valid argument values are
'       i)   rNearest (equivalent to 0)
'       ii)  rUp      (equivalent to 1)
'       iii) rDown    (equivalent to 2)
'       iv)  Any other number results in rounding to the nearest
'
' RETURNS:
' Result is a double value.
' e.g.
' vbaRound(12.3456, 2, rNearest)   = 12.35
' vbaRound(12.3456, 2, rUp)        = 12.35
' vbaRound(12.3456, 2, rDown)      = 12.34
'
' - http://AccessDB.Info

On Error GoTo HandleErr:

Dim dblPlacesFactor As Double
Dim dlbRoundFactor As Double
Dim dblCalc As Double

dblPlacesFactor = 10 ^ intDecimals

Select Case RoundOpt
Case rNearest    'Round to Nearest
    dlbRoundFactor = 0.5
Case rUp         'Round UP
    dlbRoundFactor = 1
Case rDown       'Round DOWN
    dlbRoundFactor = 0
Case Else        'Round to Nearest by default for all other values
    dlbRoundFactor = 0.5
End Select

'Calculate rounded value & Return value
'Original code:
'vbaRound = Int((dblValue * dblPlacesFactor) + dlbRoundFactor) / dblPlacesFactor

'Code broken down into component parts:
dblValue = dblValue * dblPlacesFactor
dblValue = dblValue + dlbRoundFactor
vbaRound = Int(dblValue) / dblPlacesFactor

ExitHere:
Exit Function
HandleErr:
'Leave value by default alone if an error occurs
'OR set it to any value you prefer (e.g. 0)
vbaRound = dblValue
Resume ExitHere:
End Function
 

ecawilkinson

Registered User.
Local time
Today, 18:13
Joined
Jan 25, 2005
Messages
112
I don't know where you got this function but gives some very wacky and incorrect answers, e.g.
vbaRound(4,2,rUp) = 4.01 (incorrect)
vbaRound(4.725, 1, rUp) = 4.8 (incorrect)
also, what do you want to happen with negative answers, should -4.726 rounded up to 2 decimal places be -4.72 or -4.73? It gives the following:
vbaRound(-4.726, 2, rNearest) = -4.73
vbaRound(-4.726, 2, rUp) = -4.72
vbaRound(-4.726, 2, rDown) = -4.73
Is that really what you want for negatives?
Access does not do bankers rounding it does "Access" rounding, see:
Round(1.015,2) = 1.01
Round(1.215,2) = 1.22
Round(1.025, 2) = 1.02
Round(4.025, 2) = 4.03
It something to do with not being able to represent fractional (floating point)numbers in binary.

Chris
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:13
Joined
Sep 12, 2006
Messages
15,749
i thought rounding rounds to the nearest EVEN number, where there is 5 involved

so 1.015 rounds up to 1.02
1.025 also rounds down to 1.02
 
R

Rich

Guest
Any help?

Public Function Round2CB(Value, Optional Precision As Variant) As Double

If IsNull(Value) Then Exit Function
If IsMissing(Precision) Then Precision = 2
Value = Fix(Value * 10 ^ Precision + 0.5 * Sgn(Value)) / 10 ^ Precision
Round2CB = Value

End Function
 

ecawilkinson

Registered User.
Local time
Today, 18:13
Joined
Jan 25, 2005
Messages
112
Gemma, Microsoft say that VBA uses Banker's rounding but it does not always work!
try:
round(3.5,0) = 4 correct for Banker's rounding
round(1.35,1) = 1.4 correct for Banker's rounding
round(1.035,2) = 1.03 incorrect for Bankers' rounding
round(0.035,2) = 1.04 correct for Banker's rounding.
you really need to write your own libraries if you absolutely must have total precision. I truly believe that. We had real problems at one firm I worked at recently because different PCs (different brands) gave different answers to the exactly same scientific calculation.

Chris
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Jan 23, 2006
Messages
15,422
Gemma, Microsoft say that VBA uses Banker's rounding but it does not always work!
try:
round(3.5,0) = 4 correct for Banker's rounding
round(1.35,1) = 1.4 correct for Banker's rounding
round(1.035,2) = 1.03 incorrect for Bankers' rounding
round(0.035,2) = 1.04 correct for Banker's rounding.
you really need to write your own libraries if you absolutely must have total precision. I truly believe that. We had real problems at one firm I worked at recently because different PCs (different brands) gave different answers to the exactly same scientific calculation.

Chris

Here is a conversation from a list I frequent that deals with Access/VBA rounding. This is relatively current and may assist.

-----Original Message-----
From: accessd-bounces@databaseadvisors.com [mailto:accessd-bounces@databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, May 02, 2008 10:22 AM
To: accessd@databaseadvisors.com
Subject: Rounding with CCur conversion

Hi Lambert

You may have noticed the Round11 function of that page.

I modified it slightly to perform correct Banker's Rounding:

<code>
Private Declare Function CRoundBankers Lib "oleaut32.dll" _
Alias "VarR8Round" ( _
ByVal dblValue As Double, _
ByVal lngDecimalPlaces As Long, _
ByRef dblRounded As Double) As Long
'

Public Function RoundBankers( _
ByVal dblNumber As Double, _
Optional ByVal lngDecimalPlaces As Long) As Double

' Performs banker's rounding of number dblNumber.
'
' Idea by Lyle Fairfield, LyleFairfield@CyRiv.Com, 20001218
' Note: Performs no rounding if lngDecimalPlaces is negative.
'
' 2002-03-31. Gustav Brock, Cactus Data ApS, CPH.

CRoundBankers dblNumber, lngDecimalPlaces, dblNumber

RoundBankers = CDec(dblNumber)

End Function
</code>

Limitation is that is will not round below zero decimals but in most cases that is not an issue.
Advantage is, however, that it is very fast.

After the disappointment with CCur I may need to brush this up!

/gustav

>>> Lambert.Heenan@aig.com 02-05-2008 15:53 >>>
I'm glad I said that round "seems" to get it right. :)

But even though Round() in VBA/VB6 is buggy, it at least (seems) to attempt
banker's rounding. I have not tried to test the 17 other rounds at the web
site, but I wonder if any of them correctly implement bankers rounding?

There's an interesting article of the subject here...
http://en.wikipedia.org/wiki/Rounding

Lambert

-----Original Message-----
From: accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, May 02, 2008 9:45 AM
To: accessd@databaseadvisors.com
Subject: Re: [AccessD] Rounding with CCur conversion

Hi Lambert

> So plain old Round() seems to do it "right", but not Ccur()???

Well, Round is not old - it was introduced with A2000 - and it only does it
right sometimes.
See (below) the test in the link I provided where it fails.

/gustav


>>> Lambert.Heenan@aig.com 02-05-2008 15:23 >>>
Access 2002:

? round(111.11115,4),round(1111.11115,4), round(11111.11115,4)
111.1112 1111.1112 11111.1112
? round(111.11105,4),round(1111.11105,4), round(11111.11105,4)
111.111 1111.111 11111.111

Round up if there is and odd digit left of the last decimal, and round down
for even digits. Bankers rounding.


? ccur(111.11115),ccur(1111.11115), ccur(11111.11115)
111.1111 1111.1111 11111.1112
? ccur(111.11105),ccur(1111.11105), ccur(11111.11105)
111.1111 1111.111 11111.111

Sometimes round up, sometimes round down. Chaotic rounding.

So plain old Round() seems to do it "right", but not Ccur()???


 

CrystalSurfer

Matrix activist
Local time
Today, 10:13
Joined
Jan 11, 2006
Messages
75
Thats interesting Chris. I didn't realise Access has its own proprietry rounding methods. And yes, I would ideally like to handle negatives properly in a rounding function although not applicable in this calc as I want the absolute value.

Rich, thanks for that calc - I'll give it a whirl..
 

ecawilkinson

Registered User.
Local time
Today, 18:13
Joined
Jan 25, 2005
Messages
112
hi CrystalSurfer,

Microsoft say Access implements Banker's Rounding, so I'm sure they do. The problem is that VBA does not take into account the problems of internal binary representation of fractions. Your 4.725 is internally probably actually4.724999999999999. So even if you add 0.5 after multiplying it by 100, you still only get 472.499999999, which has an int of 472, hence the anomoly. This is what Microsoft has to say about it - it has some useful routines toward the end.

HTH,
Chris
 

evanscamman

Registered User.
Local time
Today, 10:13
Joined
Feb 25, 2007
Messages
274
Banker's Rounding

To bypass banker's rounding and always round a 5 up, you can add .0000001 to a number before rounding it.

Evan
 

ecawilkinson

Registered User.
Local time
Today, 18:13
Joined
Jan 25, 2005
Messages
112
CrystalSurfer,

I think the following function should do what you wanted.
Code:
'
' Custom Rounding function.
'
' To always round down, make iRoundOpt less than zero
' To always roun up, make iRoundOpt more than zero
' To round to the nearest, make iRoundOpt exactly zero
'
' for negative numbers, rounding down means closer to zero,e.g CWRound(-4.4) = -4
' for negative numbers, rounding up means further from zero,e.g CWRound(-4.6) = -5
' for negative numbers, rounding to the nearest follows the above 2 rules
' e.g CWRound(-4.4) = -4 and CWRound(-4.6) = -5
'
Function CWRound(ByVal vValue As Variant, Optional ByVal iDecimalPlaces = 0, Optional iRoundOpt As Integer = 0) As Variant
    Dim vFactor As Variant
    Dim vTemp As Variant
    
    If iDecimalPlaces < 0 Then
        iDecimalPlaces = 0
    End If
    
    vFactor = CDec(10 ^ iDecimalPlaces)
    vValue = CDec(vValue)
    
    If Not IsNumeric(vValue) Then
        CWRound = vValue
    Else
        Select Case iRoundOpt
            Case Is < 0
                CWRound = Fix(CDec(vValue * vFactor)) / vFactor
            Case Is = 0
                CWRound = Fix(CDec(vValue * vFactor) + 0.5 * Sgn(vValue)) / vFactor
            Case Is > 0
                vTemp = Fix(CDec(vValue * vFactor))
                CWRound = (vTemp + IIf(vValue = vTemp, 0, Sgn(vValue))) / vFactor
        End Select
    End If
End Function
It gives you the choice of the rounding style and how many digits precision you want. For me, it rounds in the way i would expect from learning maths at school. No odd rules. uing the Decimal data type means that it can go to more digits of precision. At least 14 - that is how many Access will show on screen.

HTH,
Chris
 

Users who are viewing this thread

Top Bottom