VBA Number Rounding
tomhaddock
I noticed in the Immediate window that rounding numbers after dividing
them returns strange results
? ROUND(5/2, 0)
2
? ROUND(15/2, 0)
8
? ROUND(35/2, 0)
18
You can see here that the first result conflicts with the other results
logically. I would have thought that 5/2 = 2.5 would round up like the
others to 3 but instead was rounded down to 2.
Is this a bug of Excel (ver 2000) or am I doing something wrong?
Tom
Tom Ogilvy
HOWTO: Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194983
PRB: Round Function different in VBA 6 and Excel Spreadsheet
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q225330
OFF2000: New Round Function in Visual Basic for Applications 6.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209996
ACC2000: How to Round a Number Up or Down by a Desired Increment
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q279755
INFO: Visual Basic and Arithmetic Precision
--
Regards,
Tom Ogilvy
"tomhaddock" <tomhaddo...@excelforum.com> wrote in message
news:tomhaddo...@excelforum.com...
tomhaddock
You have saved me a lot of time figuring out this rounding problem. I
am now using a UDF to round it the way I want rounded! I cannot
understand why the rounding functions in formula and visual basic are
different... trust Excel to make things complicated!
Tom
Myrna Larson
average it will round up half the time and down half the time.
The point is to eliminate bias due to rounding. The digits 1-4 always round down, 6-9 always
round up, 5 is 50/50. So half the time a number is rounded up, half the time rounded down.
Ron Rosenfeld
wrote:
In VBA, if you want it to round as it does on the worksheet, you can use:
Application.WorksheetFunction.Round
--ron
Tom Ogilvy
--
Regards,
Tom Ogilvy
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:038slvc3b200sbqpc...@4ax.com...
Jerry W. Lewis
configuration option that would impact both the worksheet and VBA round
function. It would also have helped if the VBA function had been as
well written as the worksheet round function (identifying whether the
first digit to be rounded away is >=<5 is nontrivial in binary, where
the math is done, and the worksheet does it better).
Those problems aside, the availability of a Round function that rounds
in this way is a welcome addition. You wouldn't know it from MS
documentation (which calls it "Banker's" rounding despite the fact that
banker's don't seem to use it), but this is type of rounding is
specified by ASTM, IEEE, and most other standards bodies that choose to
define rounding. It is usually a better way to handle data, because it
tends to minimize the impact of accumulated rounding errors on
subsequent calculations, because it tends to equalize the number of
times that you round up vs. round down. As such it is sometimes called
"unbiased" rounding.
Jerry
Alan
Jerry wrote:
> Those problems aside, the availability of a Round
> function that rounds
> in this way is a welcome addition. You wouldn't
> know it from MS
> documentation (which calls it "Banker's" rounding
> despite the fact that
> banker's don't seem to use it), but this is type of
> rounding is
> specified by ASTM, IEEE, and most other standards
> bodies that choose to
> define rounding. It is usually a better way to handle
> data, because it
> tends to minimize the impact of accumulated
> rounding errors on
> subsequent calculations, because it tends to equalize
> the number of
> times that you round up vs. round down. As such it
> is sometimes called
> "unbiased" rounding.
>
I struggle with the explanation that this reduces bias (unless you are
looking at a non random distribution that is already biased to return
values exactly on the 0.5 points).
If you use 'true' rounding, then everything from N to N+4.9999
(recurring) inclusive will be rounded 'down' to N, and everyting from
N+0.5 to N+0.9999 (recurring) will be rounded 'up' to N+1 (where N is
an integer number - consider it positive for the sake of this
discussion).
This is a symmetrical rounding 'function' and as such is unbiased.
If we now redefine to use what was referred to above as Banker's
rounding, and round down approximately half the time when we have a
value of N+0.5, we have made the function non-symmetrical, and hence
biased.
The only excpetion would be if we have a sample that returns discrete
values, that may include N+0.5 more often that we would otherwise
expect, in which situation there may be a case.
Does this make sense?
Alan.
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/