ROUNDING IN EXCEL

Discussion in 'Microsoft Excel Misc' started by Anon, Mar 13, 2008.

  1. Anon

    Anon Guest

    I have a customer who wants calculations Rounded per the requirements of
    ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
    numbers are rounded?

    Thanks
    Paul
     
    Anon, Mar 13, 2008
    #1
    1. Advertisements

  2. Jim Thomlinson, Mar 13, 2008
    #2
    1. Advertisements

  3. Anon

    Tyro Guest

    Have you Googled ASTM 29 Rounding? Excel is capable of rounding in
    different ways. I'm sure you can find one that is suitable to meed the
    standard

    Tyro

    "Anon" <> wrote in message
    news:...
    >I have a customer who wants calculations Rounded per the requirements of
    >ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
    >numbers are rounded?
    >
    > Thanks
    > Paul
    >
     
    Tyro, Mar 13, 2008
    #3
  4. Anon

    BoniM Guest

    ASTM E29 - Rounding...
    When the digit beyond the one you want to keep is less than 5, do not
    change the digit you are keeping. When the digit beyond the one you want to
    keep is greater than 5, increase the digit you are keeping by 1. When the
    digit beyond the one you want to keep is equal to 5 and there are non-zero
    digits beyond it, increase the digit you are keeping by 1. When the digit
    beyond the one you want to keep is equal to 5 exactly, and the digit you
    are keeping is odd, increase the digit you are keeping by 1. If the digit
    you are keeping is even, keep it unchanged.

    So, it doesn't... use this formula:
    =IF(A2-INT(A2)-0.5=0,EVEN(ROUNDDOWN(A2,0)),ROUND(A2,0))
    to round to whole numbers.

    If you want decimal places... it gets more complicated.
    Good luck!


    "Anon" wrote:

    > I have a customer who wants calculations Rounded per the requirements of
    > ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
    > numbers are rounded?
    >
    > Thanks
    > Paul
    >
    >
    >
     
    BoniM, Mar 14, 2008
    #4
  5. On Thu, 13 Mar 2008 15:51:41 -0700, "Anon" <> wrote:

    >I have a customer who wants calculations Rounded per the requirements of
    >ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
    >numbers are rounded?
    >
    >Thanks
    >Paul
    >


    Excel does not, but I believe VBA does. If it does, then you could use this
    UDF:

    To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
    highlighted in the Project Explorer window, then Insert/Module and paste the
    code below into the window that opens.

    To use this, enter the formula

    =bRound(num, numdigits)

    into some cell where num is either the number you want to round, or a cell
    reference containing that number; numdigits is the number of decimals to round
    to (0 by default).

    =========================
    Function bRound(num As Double, Optional numdigits As Long = 0) As Double
    bRound = Round(num, numdigits)
    End Function
    ==========================================

    --ron
     
    Ron Rosenfeld, Mar 14, 2008
    #5
  6. Anon

    Anon Guest

    Thanks everyone- lots to study!

    Thanks again!


    "Ron Rosenfeld" <> wrote in message
    news:...
    > On Thu, 13 Mar 2008 15:51:41 -0700, "Anon" <> wrote:
    >
    >>I have a customer who wants calculations Rounded per the requirements of
    >>ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
    >>numbers are rounded?
    >>
    >>Thanks
    >>Paul
    >>

    >
    > Excel does not, but I believe VBA does. If it does, then you could use
    > this
    > UDF:
    >
    > To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
    > highlighted in the Project Explorer window, then Insert/Module and paste
    > the
    > code below into the window that opens.
    >
    > To use this, enter the formula
    >
    > =bRound(num, numdigits)
    >
    > into some cell where num is either the number you want to round, or a cell
    > reference containing that number; numdigits is the number of decimals to
    > round
    > to (0 by default).
    >
    > =========================
    > Function bRound(num As Double, Optional numdigits As Long = 0) As Double
    > bRound = Round(num, numdigits)
    > End Function
    > ==========================================
    >
    > --ron
     
    Anon, Mar 17, 2008
    #6
  7. I think you will get more consistent results from the code I published at
    http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69

    Things my code fix are
    - VBA Round function does not support rounding to negative digits
    (multiples of 10)
    - VBA Round function does not handle slight discrepancies in the binary
    value, e.g. 1110*00.865 = 96.015, but =bRound(1110*00.865,2) returns 96.01
    instead of 96.02

    As a historical question, does anyone have evidence that ASTM rounding has
    ever been a standard in banking? Barring that, does anyone know how this
    came to be called "banker's rounding" in some circles?

    Jerry

    "Ron Rosenfeld" wrote:

    > On Thu, 13 Mar 2008 15:51:41 -0700, "Anon" <> wrote:
    >
    > >I have a customer who wants calculations Rounded per the requirements of
    > >ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
    > >numbers are rounded?
    > >
    > >Thanks
    > >Paul
    > >

    >
    > Excel does not, but I believe VBA does. If it does, then you could use this
    > UDF:
    >
    > To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
    > highlighted in the Project Explorer window, then Insert/Module and paste the
    > code below into the window that opens.
    >
    > To use this, enter the formula
    >
    > =bRound(num, numdigits)
    >
    > into some cell where num is either the number you want to round, or a cell
    > reference containing that number; numdigits is the number of decimals to round
    > to (0 by default).
    >
    > =========================
    > Function bRound(num As Double, Optional numdigits As Long = 0) As Double
    > bRound = Round(num, numdigits)
    > End Function
    > ==========================================
    >
    > --ron
    >
     
    Jerry W. Lewis, Mar 19, 2008
    #7
  8. The information is somewhat dated. While rounding ties to an even rounded
    number remains the standard from ASTM and most other (non-financial)
    standards bodies that explicitly specify how to round, ANSI Z25.1 was
    withdrawn so long ago that the ANSI bookstore still has not been able to tell
    me when, despite having had a couple of days to research it.

    Jerry

    "Jim Thomlinson" wrote:

    > Check out this link...
    >
    > http://exceltips.vitalnews.com/Pages/T002835_Rounding_Religious_Wars_Take_Two.html
    > --
    > HTH...
    >
    > Jim Thomlinson
    >
    >
    > "Anon" wrote:
    >
    > > I have a customer who wants calculations Rounded per the requirements of
    > > ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
    > > numbers are rounded?
    > >
    > > Thanks
    > > Paul
    > >
    > >
    > >
     
    Jerry W. Lewis, Mar 19, 2008
    #8
  9. On Wed, 19 Mar 2008 01:42:01 -0700, Jerry W. Lewis <post_a_reply@no_e-mail.com>
    wrote:

    >I think you will get more consistent results from the code I published at
    >http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69
    >
    >Things my code fix are
    > - VBA Round function does not support rounding to negative digits
    >(multiples of 10)
    > - VBA Round function does not handle slight discrepancies in the binary
    >value, e.g. 1110*00.865 = 96.015, but =bRound(1110*00.865,2) returns 96.01
    >instead of 96.02
    >
    >As a historical question, does anyone have evidence that ASTM rounding has
    >ever been a standard in banking? Barring that, does anyone know how this
    >came to be called "banker's rounding" in some circles?
    >
    >Jerry


    Thanks for adding that.
    --ron
     
    Ron Rosenfeld, Mar 19, 2008
    #9
  10. Anon

    Ron Coderre Guest

    Per wikipedia:
    "The origin of the term bankers' rounding is more obscure. If this rounding
    method was ever a standard in banking, the evidence has proved extremely
    difficult to find. To the contrary, section 2 of the European Commission
    report 'The Introduction of the Euro and the Rounding of Currency Amounts'
    suggests that there had previously been no standard approach to rounding in
    banking."

    --------------------------

    Best Regards,

    Ron
    Microsoft MVP (Excel)
    (XL2003, Win XP)



    "Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
    news:...
    >I think you will get more consistent results from the code I published at
    > http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69
    >
    > Things my code fix are
    > - VBA Round function does not support rounding to negative digits
    > (multiples of 10)
    > - VBA Round function does not handle slight discrepancies in the binary
    > value, e.g. 1110*00.865 = 96.015, but =bRound(1110*00.865,2) returns 96.01
    > instead of 96.02
    >
    > As a historical question, does anyone have evidence that ASTM rounding has
    > ever been a standard in banking? Barring that, does anyone know how this
    > came to be called "banker's rounding" in some circles?
    >
    > Jerry
    >
    > "Ron Rosenfeld" wrote:
    >
    >> On Thu, 13 Mar 2008 15:51:41 -0700, "Anon" <> wrote:
    >>
    >> >I have a customer who wants calculations Rounded per the requirements of
    >> >ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29
    >> >when
    >> >numbers are rounded?
    >> >
    >> >Thanks
    >> >Paul
    >> >

    >>
    >> Excel does not, but I believe VBA does. If it does, then you could use
    >> this
    >> UDF:
    >>
    >> To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
    >> highlighted in the Project Explorer window, then Insert/Module and paste
    >> the
    >> code below into the window that opens.
    >>
    >> To use this, enter the formula
    >>
    >> =bRound(num, numdigits)
    >>
    >> into some cell where num is either the number you want to round, or a
    >> cell
    >> reference containing that number; numdigits is the number of decimals to
    >> round
    >> to (0 by default).
    >>
    >> =========================
    >> Function bRound(num As Double, Optional numdigits As Long = 0) As Double
    >> bRound = Round(num, numdigits)
    >> End Function
    >> ==========================================
    >>
    >> --ron
    >>
     
    Ron Coderre, Mar 19, 2008
    #10
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nick

    excel - rounding down times

    nick, Aug 28, 2003, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    186
    Wild Bill
    Aug 28, 2003
  2. knicholson@gpsx

    Rounding UP Always in Excel

    knicholson@gpsx, Sep 26, 2003, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    169
    knicholson@gpsx
    Sep 26, 2003
  3. Denise

    Rounding Formula - Excel

    Denise, Oct 2, 2003, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    155
    Dan E
    Oct 2, 2003
  4. Norie Sutherland

    Rounding to nearest $500 or $1000 in Excel

    Norie Sutherland, Jan 6, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    941
    Ken Wright
    Jan 6, 2004
  5. robert cohen

    Excel VBA - Rounding highlighted cells

    robert cohen, Feb 22, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    749
    Frank Kabel
    Feb 23, 2004
Loading...

Share This Page