Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Round to nearest whole number
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! -- Steve C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Round to nearest whole number
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) -- Regards, Tom Ogilvy "Steve C" wrote: 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! -- Steve C |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Round to nearest whole number
The second option does the trick. Thanks, Tom!
-- Steve C "Tom Ogilvy" wrote: 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) -- Regards, Tom Ogilvy "Steve C" wrote: 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! -- Steve C |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Round to nearest whole number
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 -- Jay "Tom Ogilvy" wrote: 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) -- Regards, Tom Ogilvy "Steve C" wrote: 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! -- Steve C |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Round to nearest whole number
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/Unbiase...to-even_method whereas the worksheet round function always rounds ties up. Jerry "Jay" wrote: 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 -- Jay "Tom Ogilvy" wrote: 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) -- Regards, Tom Ogilvy "Steve C" wrote: 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! -- Steve C |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Round to nearest whole number
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. -- Jay "Jerry W. Lewis" wrote: 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/Unbiase...to-even_method whereas the worksheet round function always rounds ties up. Jerry "Jay" wrote: 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 -- Jay "Tom Ogilvy" wrote: 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) -- Regards, Tom Ogilvy "Steve C" wrote: 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! -- Steve C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round a number to the Nearest .25 | Excel Worksheet Functions | |||
Round Number to Nearest 50 | Excel Discussion (Misc queries) | |||
How can you round a number to the nearest 900? | Excel Discussion (Misc queries) | |||
How do I round a number to the nearest 900? | Excel Discussion (Misc queries) | |||
How can you round a number to the nearest 900? | Excel Worksheet Functions |