Rounding Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

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) *
 
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

Matthew W said:
Hi All,

Excel has a rounding error. When rounding 2.5 it returns 3. However, the
Click to expand...
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,
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
 
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
 
Back
Top