Home Search   What's New Index Books Links Q & A Newsletter Banners   Feedback Tip Jar MSDN Visual Basic Community

Title Round a value to a specified number of digits round off, digits 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").

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...

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 so-called "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 pre-SP1 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 so-called 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

nTemp = (aNumberToRound * nFactor) + 0.5
RoundIt = Int(CDec(nTemp)) / nFactor

'-----------EXIT POINT------------------
ExitPoint:

Exit Function

'-----------ERROR HANDLER---------------
ErrHandler:

Select Case Err.Number
Case Else
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 reverse-engineer 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 infinite-precision 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).