Rounding 0.5 to 1

[rd]

Hi,

I wonder why my VBA code is not rounding 0.5 to 1. The code below produces
zero in the message box instead of the required 1.

Public Sub rwnd()
c = 0.5
MsgBox Round(c, 0)
End Sub

Any help would be appreciated.

Regards,
RD

 

[JE McGimpsey]

VBA's Round() method rounds a 5 in the last digit to the next EVEN
number (which is pretty standard in scientific and financial
calculations, rather than XL's method that biases results away from
zero). So 0.5 rounds to 0, 1.5 rounds to 2, 2.5 rounds to 2, 3.5 rounds
to 4, etc...

However, you can use XL's function:

Public Sub rwnd()
Dim c As Double
c = 0.5
MsgBox Application.Round(c, 0)
End Sub

 

[rd]

Thanks a lot for your help.

RD

VBA's Round() method rounds a 5 in the last digit to the next EVEN
number (which is pretty standard in scientific and financial
calculations, rather than XL's method that biases results away from
zero). So 0.5 rounds to 0, 1.5 rounds to 2, 2.5 rounds to 2, 3.5 rounds
to 4, etc...

However, you can use XL's function:

Public Sub rwnd()
Dim c As Double
c = 0.5
MsgBox Application.Round(c, 0)
End Sub

 

[Rick Rothstein \(MVP - VB\)]

VBA's Round() method rounds a 5 in the last digit to the next EVEN

number (which is pretty standard in scientific and financial
calculations,

I wouldn't be so quick to include "scientific" calculations in there. I was
a practicing Civil Engineer for some 30 years and we never rounded numbers
ending in 5 that way. Perhaps "scientific" calculations in laboraties, where
I would guess heavy use of Statistics takes place, that might be the case;
but in the practical world of road and bridge design (the fields I worked
in), this wasn't the case at all.

rather than XL's method that biases results away from zero).
So 0.5 rounds to 0, 1.5 rounds to 2, 2.5 rounds to 2, 3.5 rounds
to 4, etc...

However, you can use XL's function:

Public Sub rwnd()
Dim c As Double
c = 0.5
MsgBox Application.Round(c, 0)
End Sub

You can also use the Format function which is the only VB function that
rounds in the "proper" way. In your example, you could do this instead...

MsgBox Format(c, "0")


Rick

 

[JE McGimpsey]

I wouldn't be so quick to include "scientific" calculations in there. I was
a practicing Civil Engineer for some 30 years and we never rounded numbers
ending in 5 that way. Perhaps "scientific" calculations in laboraties, where
I would guess heavy use of Statistics takes place, that might be the case;
but in the practical world of road and bridge design (the fields I worked
in), this wasn't the case at all.

Well, I've had careers in both science and engineering, and they are
definitely NOT the same thing.

If you never rounded to even in civil engineering, biasing your results
away from zero was not apparently not significant to your results.

I've used round-to-even since at least junior high school in any physics
or chemistry class I've taken. I used it in designing and analyzing rf
and nuclear signal processing instrumentation.

Round-to-even has been the ASTM and IEEE754 standard for decades, at
least partly due to it being unbiased and not susceptible to drift over
many iterations.

However, as with any data reduction method, one has to analyze the data
space. One can imagine a set for which the symmetric arithmetic (XL's)
method might be preferable, but for natural data, round-to-even nearly
always produces no more bias, and usually less, than the XL method.

 

[Guest]

I agree that rounding to even is best practice to avoid systematic biases in
rounded numbers. I know that it is specified by almost all standards bodies
that bother to specify a rounding method (including ASTM, and in its
corresponding binary form IEEE). I also know that MS and some other
programming houses call it "banker's rounding", but I have been unable to
find evidence that it has ever been a standard in banking or other financial
calculations. Can you point me in the right direction?

Jerry

 

[JE McGimpsey]

but I have been unable to find evidence that it has ever been a
standard in banking or other financial calculations. Can you point
me in the right direction?

Not really. Back in the early 80's I did some programming for financial
institutions, and the round-to-even method was specified (while I'm sure
they wouldn't have minded rounding up their clients' payments, all those
pennies going back out in interest held them back...). When I inquired
of the department head, I was told that this was standard even back
before electronic calculation. However, the ASTM standard was
well-established by then, so it could have been based on that. Based on
the little research I've done, I suspect that rather than being standard
in financial transactions, round-to-even was *a* standard method.

I guess I've just understood round-to-even to be so superior to
symmetric arithmetic rounding (in most situations) for so long that I
may the victim of unintentional confirmation bias <g>.

FWIW, the *only* references to the origin of the term "Banker's
Rounding" that I've ever found is "unknown".

And for a discussion of on uncertainty, rounding, and the evils of
significant digits, I found this amusingly written rant worth reading
again:

http://www.av8n.com/physics/uncertainty.htm