Round to nearest whole number

[Steve C]

Upon dividing one cell by another, I want to store the result in a variable
that is rounded to the nearest whole number (6.5 = 7, 6.2 = 6, etc.).

My basic formula is:
NamesPerSlip = ActiveCell.Offset(0, -3) / ActiveCell.Offset(0, -2)

Rounding NamesPerSlip up or down as needed is my challenge. Thanks as always!

 

[Tom Ogilvy]

NamesPerSlip = Round(ActiveCell.Offset(0, -3) / ActiveCell.Offset(0, -2),0)

if you like the way the worksheet rounds

NamesPerSlip = Application.Round(ActiveCell.Offset(0, -3) /
ActiveCell.Offset(0, -2),0)

 

[Steve C]

The second option does the trick. Thanks, Tom!

 

[Jay]

Hi Tom -

What's the underlying difference between 'Round' and 'Application.Round' ?

Here's a utility to test the preformance of each. It detects a difference
between the two methods when rounding a number with a right-most digit of
"5", but I don't undrestand the fundamental reason why:

Sub rTest()
Dim icount As Long
Dim a As Double
Dim b As Double
Dim r1 As Double
Dim r2 As Double

Randomize

Do While diff = 0
icount = icount + 1
a = Rnd()
b = Rnd()
On Error Resume Next 'trap division by zero
r1 = Round(a / b, 0)
r2 = Application.Round(a / b, 0)
On Error GoTo 0
diff = r1 - r2
Loop

MsgBox "Iteration = " & Format(icount, "#,##0") & Chr(13) & Chr(13) & _
"a = " & a & Chr(13) & _
"b = " & b & Chr(13) & _
"a / b = " & Format(a / b) & Chr(13) & Chr(13) & _
"Round = " & r1 & Chr(13) & _
"Application.Round = " & r2 & Chr(13) & _
"diff = " & r1 - r2
End Sub

 

[Jerry W. Lewis]

Both round to the nearest rounded number. The difference is in how ties are
handled. VBA Round follows the ASTM standard with unbiased rounding
(sometimes called "Banker's Rounding" for reasons that remain mysterious)
http://en.wikipedia.org/wiki/Unbiased_rounding#Round-to-even_method
whereas the worksheet round function always rounds ties up.

Jerry

 

[Jay]

Thanks Jerry. Your explanation is completely informative. It sounds like
Microsoft products demonstrate various rounding methods based on history, or,
the evolution of the products. Users of Excel and VBA should be aware of
this. Thanks to Tom and you for opening our eyes. I used to assume that
this type of rounding difference had something to do with 'unstable' digits
way out to the right of the decimal point. Your information demonstrates
that rounding in this environment is completely predictable as long as you
are aware of the rules (as usual...).

Here's a statement from MS article KB194983 that I navigated to from the
Wikipedia link you provided. It essentially states exactly what you
described for the VBA/Excel environment:

"The VBA Round() function uses Banker's rounding while the spreadsheet cell
function uses arithmetic rounding."

Thanks again.