VBA Number Rounding

[tomhaddock]

Hi

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

 

[tomhaddock]

Thanks Tom!

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]

The pattern in your examples is that 0.5 rounds to the nearest EVEN number. That means that on
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]

Thanks Tom!

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

In VBA, if you want it to round as it does on the worksheet, you can use:

Application.WorksheetFunction.Round


--ron

 

[Tom Ogilvy]

Or even Application.Round or just Worksheetfunction.Round

 

[Alan]

Hi,

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.