

Title  Round a value to a specified number of digits 
Description  
Keywords  round off, digits 
Categories  Algorithms 


Multiply the value by a power of ten, round to an integer using CInt, and then divide by the power of ten.


' Round value with this many digits.
Private Function RoundOff(ByVal value As Double, ByVal _
digits As Integer) As Double
Dim shift As Double
shift = 10 ^ digits
RoundOff = CInt(value * shift) / shift
End Function


Note that numbers are not stored exactly in the computer. For example, you may round a number to 1.50 but it may be stored as 1.499999999999 in the computer. In some cases, you may need to use Format$ to make the value display properly as in Format$(value, "0.00").
See also Tutorial: Using Floating Point Numbers.
Robert Heinig says:
Dangerous. Revise and explain that CInt coerces a value to 16bit signed. CLng is the better choice in most cases anyway, but you still have to ensure
the input values stay within 2^31...
Gary German added this:
I recently did some research on rounding in VB, and it is a surprisingly complex subject! Your RoundOff routine will round by using the language's underlying rounding functions. So, for most versions of VB/VBA (but not all), it will use socalled "banker's rounding". This is an acceptable form of rounding in some financial scenarios, but it does not always provide the answer that you would expect.
The same problem applies to VB's "Round" function (added in VB6, I believe). In fact, these functions are dependent on the underlying OS'es rounding scheme, as implemented in oleaut32.dll. But, this can sometimes be a problem (e.g., in preSP1 versions of XP), as shown in the article The Format() Function Gives Different Results in Windows XP Than in Windows 2000
Microsoft has a good KB article documenting the different types of rounding in
KB article HOWTO: Implement Custom Rounding Procedures.
To avoid reliance on the OS'es rounding scheme, and implement socalled Asymmetric Arithmetic Rounding, I created the following routine:


Public Function RoundIt(ByVal aNumberToRound As Double, _
Optional ByVal aDecimalPlaces As Double = 0) As Double
On Error GoTo ErrHandler
Dim nFactor As Double
Dim nTemp As Double
nFactor = 10 ^ aDecimalPlaces
nTemp = (aNumberToRound * nFactor) + 0.5
RoundIt = Int(CDec(nTemp)) / nFactor
'EXIT POINT
ExitPoint:
Exit Function
'ERROR HANDLER
ErrHandler:
Select Case Err.Number
Case Else
' Your error handling here
RoundIt = 0
Resume ExitPoint
End Select
End Function


Note too how this routine handles negative numbers ending in 5. Because it implements "asymmetric arithmetic rounding", RoundIt(2.5, 0) = 2 (i.e., the .5 is rounded "up"). Another way of doing this is "symmetric arithmetic", where the 5's are always rounded "away from zero" (so 2.5 rounds to 3).
Here's how to implement "symmetric arithemtic" rounding:


Function SymArith(ByVal x As Double, _
Optional ByVal DecimalPlaces As Double = 1) As Double
SymArith = Fix(x * (10 ^ DecimalPlaces) + 0.5 * Sgn(x)) _
/ (10 ^ DecimalPlaces)
End Function


There are also some interesting routines in this Advisor article
Which rounding method to use depends on your needs. I usually use "asymmetric" because it's how most folks round numbers in their heads. But, one of my apps use "symmetric" because it is emulating another software package that uses symmetric rounding.
To ensure that rounding is happening the way you expect it to (and won't change due to an OS service pack), I recommend always using your own rounding routines.
Ben McCormack discovered a very interesting feature of VBA's Round function. The following shows two queries in the Immediate window and their results:
? Round(134.425,2)
134.43
? Round (1.425,2)
1.42
The first rounds 134.425 incorrectly towards an odd digit while the second correctly rounds 1.425 to an even digit.
Ben's requirement was to reverseengineer an application that showed this behavior so this inconsistency was quite a problem. Eventually he found a Microsoft Knowledge Base article (How To Implement Custom Rounding Procedures) that seems to imply that VBA's Round function uses code similar to this:


Function BRound(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
' For smaller numbers:
' BRound = CLng(X * Factor) / Factor
Dim Temp As Double, FixTemp As Double
Temp = X * Factor
FixTemp = Fix(Temp + 0.5 * Sgn(X))
' Handle rounding of .5 in a special manner
If Temp  Int(Temp) = 0.5 Then
If FixTemp / 2 <> Int(FixTemp / 2) Then ' Is Temp odd
' Reduce Magnitude by 1 to make even
FixTemp = FixTemp  Sgn(X)
End If
End If
BRound = FixTemp / Factor
End Function


This code seems to produce the same result as VBA.Round.
It seems that the problem is caused by rounding errors. You cannot store all decimal numbers with a binary representation without some errors. You could use an infiniteprecision rational representation (where you track numerators and demoninators) but that would be much less efficient.
It seems that this example is bumping up against binary representation errors. I suspect you could fiddle with the algorithm a bit to fix this particular problem but you'll probably have others for certain values.
You can get better results if you keep an eye on your precision. For example, instead of asking whether the final digit is exactly 5, ask whether it is very close to 5 (i.e. 0.05000000001 is close enough to 0.05).





