+ Reply to Thread
Results 1 to 5 of 5

Rounding Question

  1. #1
    Guest

    Rounding Question

    Does anyone know if the format function ("Format/Number with x number of
    decimal places) happens to conform with ASTM E29 for the last right-hand
    digit...???


    Thanks

    Paul



  2. #2
    Ron Rosenfeld
    Guest

    Re: Rounding Question

    On Thu, 2 Feb 2006 06:56:55 -0800, <[email protected]> wrote:

    >Does anyone know if the format function ("Format/Number with x number of
    >decimal places) happens to conform with ASTM E29 for the last right-hand
    >digit...???
    >
    >
    >Thanks
    >
    >Paul
    >


    Format function? Are you talking about the VBA Format function?

    If so, it does not, as I understand you. The Format function returns a string
    of digits rounded according to the format expression. The rounding is done in
    the same method as the Format Cells dialog in Excel.

    Of course, the Format function actually changes the value. Whereas the Format
    Cells dialog only changes how the value is displayed.

    The VBA Round Function in VBA6+ does conform to the standard, as far as I know.
    It rounds the midway numbers to the nearest even number.


    --ron

  3. #3
    Bernard Liengme
    Guest

    Re: Rounding Question

    Hi Paul,
    A simple experiment will show you that 1.575 rounds to 1.58 while 1.585
    round to 1.59 with formatting and with the ROUND function. Clearly Excel
    does not use the ASTM E29 protocol (aka Banker's Rounding)

    However, VBA does follow ASTM E29
    (see http://support.microsoft.com/default...;EN-GB;q194983 and
    http://support.microsoft.com/default...b;EN-US;196652)
    So the user-defined function below will round both 1.575 and 1.585 to 1.58
    Function myround(rng, fig) myround = Round(rng, fig)End Functionbest wishes
    --
    Bernard V Liengme
    www.stfx.ca/people/bliengme
    remove caps from email

    <[email protected]> wrote in message
    news:[email protected]...
    > Does anyone know if the format function ("Format/Number with x number of
    > decimal places) happens to conform with ASTM E29 for the last right-hand
    > digit...???
    >
    >
    > Thanks
    >
    > Paul
    >




  4. #4
    Jerry W. Lewis
    Guest

    Re: Rounding Question

    VBA's Round function does not handle the vaguaries of binary approximations
    very well, for instance myround(1110*0.0865,2) will round down instead of up.
    Your code will often produce more satisfactory results if you use
    Round(CDbl(CStr(rng)), fig)

    Also, VBA's Round function does not support negative arguments in the way
    that the worksheet function does. The code I posted at
    http://groups.google.com/group/micro...7fce6145b70d69
    deals with this shortcoming.

    Does anyone know of any instances where bankers have EVER rounded in this
    way? Barring that, does anyone know how this came to be called "Banker's
    Rounding"?

    Jerry

    "Bernard Liengme" wrote:

    > Hi Paul,
    > A simple experiment will show you that 1.575 rounds to 1.58 while 1.585
    > round to 1.59 with formatting and with the ROUND function. Clearly Excel
    > does not use the ASTM E29 protocol (aka Banker's Rounding)
    >
    > However, VBA does follow ASTM E29
    > (see http://support.microsoft.com/default...;EN-GB;q194983 and
    > http://support.microsoft.com/default...b;EN-US;196652)
    > So the user-defined function below will round both 1.575 and 1.585 to 1.58
    > Function myround(rng, fig) myround = Round(rng, fig)End Functionbest wishes
    > --
    > Bernard V Liengme
    > www.stfx.ca/people/bliengme
    > remove caps from email
    >
    > <[email protected]> wrote in message
    > news:[email protected]...
    > > Does anyone know if the format function ("Format/Number with x number of
    > > decimal places) happens to conform with ASTM E29 for the last right-hand
    > > digit...???
    > >
    > >
    > > Thanks
    > >
    > > Paul
    > >

    >
    >
    >


  5. #5
    Bernard Liengme
    Guest

    Re: Rounding Question

    Many thanks, Jerry
    --
    Bernard V Liengme
    www.stfx.ca/people/bliengme
    remove caps from email

    "Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
    news:[email protected]...
    > VBA's Round function does not handle the vaguaries of binary
    > approximations
    > very well, for instance myround(1110*0.0865,2) will round down instead of
    > up.
    > Your code will often produce more satisfactory results if you use
    > Round(CDbl(CStr(rng)), fig)
    >
    > Also, VBA's Round function does not support negative arguments in the way
    > that the worksheet function does. The code I posted at
    > http://groups.google.com/group/micro...7fce6145b70d69
    > deals with this shortcoming.
    >
    > Does anyone know of any instances where bankers have EVER rounded in this
    > way? Barring that, does anyone know how this came to be called "Banker's
    > Rounding"?
    >
    > Jerry
    >
    > "Bernard Liengme" wrote:
    >
    >> Hi Paul,
    >> A simple experiment will show you that 1.575 rounds to 1.58 while 1.585
    >> round to 1.59 with formatting and with the ROUND function. Clearly Excel
    >> does not use the ASTM E29 protocol (aka Banker's Rounding)
    >>
    >> However, VBA does follow ASTM E29
    >> (see http://support.microsoft.com/default...;EN-GB;q194983 and
    >> http://support.microsoft.com/default...b;EN-US;196652)
    >> So the user-defined function below will round both 1.575 and 1.585 to
    >> 1.58
    >> Function myround(rng, fig) myround = Round(rng, fig)End Functionbest
    >> wishes
    >> --
    >> Bernard V Liengme
    >> www.stfx.ca/people/bliengme
    >> remove caps from email
    >>
    >> <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Does anyone know if the format function ("Format/Number with x number
    >> > of
    >> > decimal places) happens to conform with ASTM E29 for the last
    >> > right-hand
    >> > digit...???
    >> >
    >> >
    >> > Thanks
    >> >
    >> > Paul
    >> >

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1