Tags: basic, digit, error, excel, following, microsoft, msdn, returns, rounding, rules, software, states

Rounding Error

On Microsoft » Microsoft Excel

5,277 words with 4 Comments; publish: Wed, 04 Jun 2008 11:58:00 GMT; (30646.88, « »)

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

All Comments

Leave a comment...

  • 4 Comments
    • 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" <NFinegan.excel.todaysummary.com.borne-digital.com> wrote in message

      news:3BB22B11-F5BC-4433-B0AD-9DF41BE9CE6C.excel.todaysummary.com.microsoft.com...

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

      >

      #1; Wed, 04 Jun 2008 11:59:00 GMT
    • 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" <post_a_reply.excel.todaysummary.com.no_e-mail.com> wrote in message

      news:408330B0.9000009.excel.todaysummary.com.no_e-mail.com...

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

      > http://groups.google.com/groups?selm=3E55A396.8080708%40no_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

      > Peo Sjoblom wrote:

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

      >

      #3; Wed, 04 Jun 2008 12:01:00 GMT
    • 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

      Bernard V Liengme wrote:

      > 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" <post_a_reply.excel.todaysummary.com.no_e-mail.com> wrote in message

      > news:408330B0.9000009.excel.todaysummary.com.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

      >>Peo Sjoblom wrote:

      >>I think you are referring to "bankers rounding".

      #4; Wed, 04 Jun 2008 12:02:00 GMT