VBA Number Rounding

  • Thread starter Thread starter tomhaddock
  • Start date Start date
T

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
 
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
 
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.
 
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
Click to expand...

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

Application.WorksheetFunction.Round


--ron
 
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.
Click to expand...

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.
 
Back
Top