Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet rounding vs VBA rounding
Excel 2K
The VBA Round function uses 'Bankers rounding' (half the time the .5 is rounded up, half the time down). The worksheet ROUND() function uses 'normal rounding' (.5 is always rounded up) Is there an easy way to make the VBA rounding the same as the worksheet rounding Thanks in advance Simon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet rounding vs VBA rounding
Hi Simon
You could use the same worksheet round function invoked using WorksheetFunction.Round(arg1,arg2) in your VBA code Thanks Xcelion "Simon Cleal" wrote: Excel 2K The VBA Round function uses 'Bankers rounding' (half the time the .5 is rounded up, half the time down). The worksheet ROUND() function uses 'normal rounding' (.5 is always rounded up) Is there an easy way to make the VBA rounding the same as the worksheet rounding Thanks in advance Simon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet rounding vs VBA rounding
Or you could cut your own
Function myRound(num, Optional places As Long = 0) If Int(num) Mod 2 = 0 Then myRound = Round(num + 1 / (10 ^ places), places) - 1 / (10 ^ places) Else myRound = Round(num, places) End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "Xcelion" wrote in message ... Hi Simon You could use the same worksheet round function invoked using WorksheetFunction.Round(arg1,arg2) in your VBA code Thanks Xcelion "Simon Cleal" wrote: Excel 2K The VBA Round function uses 'Bankers rounding' (half the time the .5 is rounded up, half the time down). The worksheet ROUND() function uses 'normal rounding' (.5 is always rounded up) Is there an easy way to make the VBA rounding the same as the worksheet rounding Thanks in advance Simon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet rounding vs VBA rounding
Xcelion & Bob - Thanks for the alternative solutions
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet rounding vs VBA rounding
Hi,
Format function also does the arithmetic rounding like the worksheet ROUND function. (except in a particular environment) MsgBox CDbl(Format(2.5, "0")) 'shows 3 The Format() Function Gives Different Results in Windows XP Than in Windows 2000 http://support.microsoft.com/kb/321047/en-us/ the accuracy of round function for decimal number is not so good. MsgBox WorksheetFunction.Round(9000.92585, 4) 'shows 9000.9258 MsgBox Round(0.00015, 4) 'shows 0.0001 if you want such accuracy, you should think about a method that fits the range of numerical data. (as we won't test functions with all number.) for instance, if the minimum change is 0.00001, Int((9000.92585 + 0.00001 / 10) * 10000 + 0.5) / 10000 is sure to return 9000.9259 even though a number has a small rounding error. -- HTH, okaizawa Simon Cleal wrote: Excel 2K The VBA Round function uses 'Bankers rounding' (half the time the .5 is rounded up, half the time down). The worksheet ROUND() function uses 'normal rounding' (.5 is always rounded up) Is there an easy way to make the VBA rounding the same as the worksheet rounding Thanks in advance Simon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
applying rounding formula to entire worksheet | Excel Worksheet Functions | |||
ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH | Excel Worksheet Functions | |||
rounding | Excel Discussion (Misc queries) | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Rounding | Excel Discussion (Misc queries) |