HOW TO: Round number to any increment

[Mark M]

Hi,
I've noticed many people asking how to round numbers to the nearest .05 or
..25 or whatever, and many people responding with good answers. I was
cleaning out my file cabinet and came across an old KB article that provides
a function that will round to any increment you choose as well as the
direction to round; and it works. All you need to do is create a standard
module and paste the following into it:

'****Code Start****
Option Compare Database
Option Explicit

Public Const vb_roundup = 1
Public Const vb_rounddown = 0

Function RoundToNearest(Amt As Double, RoundAmt As Variant, _
Direction As Integer) As Double
'******************************************************
'Parameter Value
'--------- --------------------------------------------
' Amt The numeric value you want to round
'RoundAmt The increment to which Amt will be rounded
'Direction Constant indicating which direction to round
'******************************************************
On Error Resume Next
Dim Temp As Double
Temp = Amt / RoundAmt
If Int(Temp) = Temp Then
RoundToNearest = Amt
Else
If Direction = vb_rounddown Then
Temp = Int(Temp)
Else
Temp = Int(Temp) + 1
End If
RoundToNearest = Temp * RoundAmt
End If
End Function
'****Code End****

To test it, type the following into the debug window:
?RoundToNearest(1.36, 0.05, vb_rounddown)
returns 1.35

?RoundToNearest(1.36, 0.25, vb_roundup)
returns 1.5

Hope you find this useful.
Mark

 

[Tim Ferguson]

I was
cleaning out my file cabinet and came across an old KB article that
provides a function that will round to any increment you choose as
well as the direction to round; and it works.

Actually it doesn't: it does a crude truncation or truncation +1, with no
attempt to round to nearest at all.

The crucial line would be something like:

Temp = Int(Amt / RoundAmt + 0.5) * RoundAmt

Banker's rounding would be a little more complex. So would dealing
appropriately with negative numbers.

All the best


Tim F