Rounding Error

[Guest]

Hi All

Excel has a rounding error. When rounding 2.5 it returns 3. However, the basic rules of rounding states that when the digit following the number to be rounded is 5 you always round to an even number. In other Microsoft Tools this does not occur. For example, if you perform the following two computations in excel and Visual Basic, Visual Basic will give you the correct answer. Is there another function that can be used to resolve this problem

The correct answer(Check VB)
4=Round(2+.5, 0) * 2

The incorrect answer(Check Excel)
6=Round(2+.5, 0) *

 

[Peo Sjoblom]

I think you are referring to "bankers rounding".
Although Excel's VBA does round the way you want,
the function does not.

http://support.microsoft.com/default.aspx?scid=KB;en-us;q225330



--

Regards,

Peo Sjoblom

Hi All,

Excel has a rounding error. When rounding 2.5 it returns 3. However, the

basic rules of rounding states that when the digit following the number to
be rounded is 5 you always round to an even number. In other Microsoft Tools
this does not occur. For example, if you perform the following two
computations in excel and Visual Basic, Visual Basic will give you the
correct answer. Is there another function that can be used to resolve this
problem?

 

[Jerry W. Lewis]

For an easy way to use the VBA round function in a worksheet, see

http://groups.google.com/groups?selm=3E55A396.8080708@no_e-mail.com

Aside: I know that Microsoft refers to the rounding method specified in
the ASTM E 29 standard as "bankers rounding", but do/have bankers ever
used it?

Jerry

 

[Bernard V Liengme]

Jerry,
I recall a message some time ago that stated the "round to even when 5" rule
is mandatory in Australia (presumable in financial circles)
In Canada, my kids were taught this rule 15 years ago in Nova Scotia but
students I get now from Nova Scotia do not use it. A flash in the pan as
part of "new math"?
Regards
Bernard

 

[Jerry W. Lewis]

Thanks, I will look for that Australia reference.

Round 5 to even has been an ASTM standard since the 1940's, so hardly a
flash in the pan, but I agree that education has been inconsistent.
IEEE specifies the binary equivalent, which is presumably used by all
microprocessors.

US IRS always rounds 5 up, as do banks for Euro conversion (in Europe
and presumably worldwide), also the United States Pharmacopoeia (because
someone convinced them that computers couldn't do ASTM rounding). I
have no training in accounting, but but cannot believe that the rule
would be so poorly known in the US if it was regularly used in US banking.

Jerry