Need help implementing EPA rounding method

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Nov 7, 2006.

  1. Guest

    Guest Guest

    This question concerns the environmental field. The EPA recommends a method
    of rounding which is statistically more accurate than the method we all
    learned in elementary school. Basically, if the tenth digit is .5, you do
    not simply round up, but rather round to the nearest EVEN whole number.

    Ex:
    1.5 = 2
    10.5 = 10 (not 11)

    The rule only makes a difference when the tenth digit is .5 . Using the IF
    statement, I think I can create a spreadhseet function that evaluates a
    number, and rounds to the even digit or rounds normally based on that
    evaluation. In order for this to work, however, I need Excel to only
    evaluate the first digit after the decimal point. The trouble is, I don't
    know how to get Excel to do this.

    I'm hoping that a simple manipulation of an existing function will solve
    this since our network may kick out an attempt to use a macro.

    Any help on this problem would be appreciated.
     
    Guest, Nov 7, 2006
    #1
    1. Advertisements

  2. Guest

    Dana DeLouis Guest

    > ...our network may kick out an attempt to use a macro.

    That's too bad, as vba uses Bankers Rounding.

    ?WorksheetFunction.Round(10.5, 0)
    11

    ?Round(10.5, 0)
    10

    --
    HTH :>)
    Dana DeLouis
    Windows XP & Office 2003


    "Will S." <Will > wrote in message
    news:...
    > This question concerns the environmental field. The EPA recommends a
    > method
    > of rounding which is statistically more accurate than the method we all
    > learned in elementary school. Basically, if the tenth digit is .5, you do
    > not simply round up, but rather round to the nearest EVEN whole number.
    >
    > Ex:
    > 1.5 = 2
    > 10.5 = 10 (not 11)
    >
    > The rule only makes a difference when the tenth digit is .5 . Using the
    > IF
    > statement, I think I can create a spreadhseet function that evaluates a
    > number, and rounds to the even digit or rounds normally based on that
    > evaluation. In order for this to work, however, I need Excel to only
    > evaluate the first digit after the decimal point. The trouble is, I don't
    > know how to get Excel to do this.
    >
    > I'm hoping that a simple manipulation of an existing function will solve
    > this since our network may kick out an attempt to use a macro.
    >
    > Any help on this problem would be appreciated.
     
    Dana DeLouis, Nov 7, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    I gave a user defined function for the purpose at

    http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69

    This method is specified by ASTM and most other standards organizations that
    choose to explicitly specify how rounding is done. How it came to be called
    "bankers' rounding" in some computer circles is a mystery to me, since
    bankers are one of the few groups who AFAIK never round in this way.

    Jerry

    "Will S." wrote:

    > This question concerns the environmental field. The EPA recommends a method
    > of rounding which is statistically more accurate than the method we all
    > learned in elementary school. Basically, if the tenth digit is .5, you do
    > not simply round up, but rather round to the nearest EVEN whole number.
    >
    > Ex:
    > 1.5 = 2
    > 10.5 = 10 (not 11)
    >
    > The rule only makes a difference when the tenth digit is .5 . Using the IF
    > statement, I think I can create a spreadhseet function that evaluates a
    > number, and rounds to the even digit or rounds normally based on that
    > evaluation. In order for this to work, however, I need Excel to only
    > evaluate the first digit after the decimal point. The trouble is, I don't
    > know how to get Excel to do this.
    >
    > I'm hoping that a simple manipulation of an existing function will solve
    > this since our network may kick out an attempt to use a macro.
    >
    > Any help on this problem would be appreciated.
     
    Guest, Nov 7, 2006
    #3
  4. Guest

    Guest Guest

    Jerry,

    I'm glad to see that someone else knows what I'm talking about. It also
    seems like the solution is only available in VB. Assuming I can make the
    macro work under our security settings, how would I implement the solution
    you've defined? First, where do I program it in. Second, how do I call it
    up in the spreadsheet?

    Thanks in advance,

    -Will

    "Jerry W. Lewis" wrote:

    > I gave a user defined function for the purpose at
    >
    > http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69
    >
    > This method is specified by ASTM and most other standards organizations that
    > choose to explicitly specify how rounding is done. How it came to be called
    > "bankers' rounding" in some computer circles is a mystery to me, since
    > bankers are one of the few groups who AFAIK never round in this way.
    >
    > Jerry
    >
    > "Will S." wrote:
    >
    > > This question concerns the environmental field. The EPA recommends a method
    > > of rounding which is statistically more accurate than the method we all
    > > learned in elementary school. Basically, if the tenth digit is .5, you do
    > > not simply round up, but rather round to the nearest EVEN whole number.
    > >
    > > Ex:
    > > 1.5 = 2
    > > 10.5 = 10 (not 11)
    > >
    > > The rule only makes a difference when the tenth digit is .5 . Using the IF
    > > statement, I think I can create a spreadhseet function that evaluates a
    > > number, and rounds to the even digit or rounds normally based on that
    > > evaluation. In order for this to work, however, I need Excel to only
    > > evaluate the first digit after the decimal point. The trouble is, I don't
    > > know how to get Excel to do this.
    > >
    > > I'm hoping that a simple manipulation of an existing function will solve
    > > this since our network may kick out an attempt to use a macro.
    > >
    > > Any help on this problem would be appreciated.
     
    Guest, Nov 7, 2006
    #4
  5. Guest

    Guest Guest

    To get the code into the workbook:

    1) Right click a tab on your workbook and select view code
    2) On the lefthand side of the screen you will see a window which lists your
    workbook, its associated objects, and any other workbook you have open.
    Right-click on the workbook name in question, select Insert Module.
    3) Copy and paste the code at the link into the large white space on the
    right hand side of the screen.
    4) Hit save.
    5) Run the function like any other; i.e., =ASTMround(A1)
    6) Post any questions you have including a detailed explanation of what you
    have done so far.

    As to your security settings--I have no idea how this would affect them,
    because everyone's security situation is different.

    Dave
    --
    Brevity is the soul of wit.


    "Will S." wrote:

    > Jerry,
    >
    > I'm glad to see that someone else knows what I'm talking about. It also
    > seems like the solution is only available in VB. Assuming I can make the
    > macro work under our security settings, how would I implement the solution
    > you've defined? First, where do I program it in. Second, how do I call it
    > up in the spreadsheet?
    >
    > Thanks in advance,
    >
    > -Will
    >
    > "Jerry W. Lewis" wrote:
    >
    > > I gave a user defined function for the purpose at
    > >
    > > http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69
    > >
    > > This method is specified by ASTM and most other standards organizations that
    > > choose to explicitly specify how rounding is done. How it came to be called
    > > "bankers' rounding" in some computer circles is a mystery to me, since
    > > bankers are one of the few groups who AFAIK never round in this way.
    > >
    > > Jerry
    > >
    > > "Will S." wrote:
    > >
    > > > This question concerns the environmental field. The EPA recommends a method
    > > > of rounding which is statistically more accurate than the method we all
    > > > learned in elementary school. Basically, if the tenth digit is .5, you do
    > > > not simply round up, but rather round to the nearest EVEN whole number.
    > > >
    > > > Ex:
    > > > 1.5 = 2
    > > > 10.5 = 10 (not 11)
    > > >
    > > > The rule only makes a difference when the tenth digit is .5 . Using the IF
    > > > statement, I think I can create a spreadhseet function that evaluates a
    > > > number, and rounds to the even digit or rounds normally based on that
    > > > evaluation. In order for this to work, however, I need Excel to only
    > > > evaluate the first digit after the decimal point. The trouble is, I don't
    > > > know how to get Excel to do this.
    > > >
    > > > I'm hoping that a simple manipulation of an existing function will solve
    > > > this since our network may kick out an attempt to use a macro.
    > > >
    > > > Any help on this problem would be appreciated.
     
    Guest, Nov 7, 2006
    #5
  6. Guest

    Dana DeLouis Guest

    > ...our network may kick out an attempt to use a macro.

    I know this is probably not foolproof, but just an initial attempt.
    Mod can't work with very large numbers either.

    =ROUND(A1,0)-(AND(MOD(A1,0.5)=0,ISEVEN(A1)))

    --
    Dana DeLouis
    Windows XP & Office 2003


    "Will S." <Will > wrote in message
    news:...
    > This question concerns the environmental field. The EPA recommends a
    > method
    > of rounding which is statistically more accurate than the method we all
    > learned in elementary school. Basically, if the tenth digit is .5, you do
    > not simply round up, but rather round to the nearest EVEN whole number.
    >
    > Ex:
    > 1.5 = 2
    > 10.5 = 10 (not 11)
    >
    > The rule only makes a difference when the tenth digit is .5 . Using the
    > IF
    > statement, I think I can create a spreadhseet function that evaluates a
    > number, and rounds to the even digit or rounds normally based on that
    > evaluation. In order for this to work, however, I need Excel to only
    > evaluate the first digit after the decimal point. The trouble is, I don't
    > know how to get Excel to do this.
    >
    > I'm hoping that a simple manipulation of an existing function will solve
    > this since our network may kick out an attempt to use a macro.
    >
    > Any help on this problem would be appreciated.
     
    Dana DeLouis, Nov 7, 2006
    #6
  7. Guest

    Guest

    , Nov 7, 2006
    #7
  8. Guest

    Guest Guest

    =ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )
    --
    Regards,
    Luc.

    "Festina Lente"


    "Will S." wrote:

    > This question concerns the environmental field. The EPA recommends a method
    > of rounding which is statistically more accurate than the method we all
    > learned in elementary school. Basically, if the tenth digit is .5, you do
    > not simply round up, but rather round to the nearest EVEN whole number.
    >
    > Ex:
    > 1.5 = 2
    > 10.5 = 10 (not 11)
    >
    > The rule only makes a difference when the tenth digit is .5 . Using the IF
    > statement, I think I can create a spreadhseet function that evaluates a
    > number, and rounds to the even digit or rounds normally based on that
    > evaluation. In order for this to work, however, I need Excel to only
    > evaluate the first digit after the decimal point. The trouble is, I don't
    > know how to get Excel to do this.
    >
    > I'm hoping that a simple manipulation of an existing function will solve
    > this since our network may kick out an attempt to use a macro.
    >
    > Any help on this problem would be appreciated.
     
    Guest, Nov 8, 2006
    #8
  9. Guest

    Guest

    PapaDos wrote:
    > =ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )


    That was my answer. But to avoid the anomalous failure of MOD() with
    large numbers, try:

    =round(a1,0) - and(a1-int(a1)=0.5, iseven(a1))
     
    , Nov 8, 2006
    #9
  10. Guest

    Guest Guest

    Your formula rounds in the wrong direction if A1<0.

    Also it may not give the intended result if A1 is calculated. There are 14
    distinct binary numbers that all display as decimal 0.5, but your formula
    will only recognize one of them as 0.5. As in my VBA function, you can treat
    as equal to .5 any number that equals .5 to 15 figures by converting to a
    string and back again. Thus

    =ROUND(A1,0) - AND(A1-INT(VALUE(A1&""))=0.5, ISEVEN(A1))*SIGN(A1)

    Should handle both issues.

    Jerry

    "" wrote:

    > PapaDos wrote:
    > > =ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )

    >
    > That was my answer. But to avoid the anomalous failure of MOD() with
    > large numbers, try:
    >
    > =round(a1,0) - and(a1-int(a1)=0.5, iseven(a1))
    >
    >
     
    Guest, Nov 8, 2006
    #10
  11. On Tue, 7 Nov 2006 05:53:02 -0800, Will S. <Will >
    wrote:

    >This question concerns the environmental field. The EPA recommends a method
    >of rounding which is statistically more accurate than the method we all
    >learned in elementary school. Basically, if the tenth digit is .5, you do
    >not simply round up, but rather round to the nearest EVEN whole number.
    >
    >Ex:
    >1.5 = 2
    >10.5 = 10 (not 11)
    >
    >The rule only makes a difference when the tenth digit is .5 . Using the IF
    >statement, I think I can create a spreadhseet function that evaluates a
    >number, and rounds to the even digit or rounds normally based on that
    >evaluation. In order for this to work, however, I need Excel to only
    >evaluate the first digit after the decimal point. The trouble is, I don't
    >know how to get Excel to do this.
    >
    >I'm hoping that a simple manipulation of an existing function will solve
    >this since our network may kick out an attempt to use a macro.
    >
    >Any help on this problem would be appreciated.



    Perhaps:

    =INT(A1+MOD(INT(A1),2)*MOD(A1,1))


    --ron
     
    Ron Rosenfeld, Nov 8, 2006
    #11
  12. Guest

    Guest

    Jerry W. Lewis wrote:
    > Also it may not give the intended result if A1 is calculated. There are 14
    > distinct binary numbers that all display as decimal 0.5, but your formula
    > will only recognize one of them as 0.5.


    I realize that; in fact, it was intentional. It is not clear to me
    whether the ASTM standard applies to "displayed" results or to actual
    results. I cannot find a (free) online copy of the standard [1]. I
    suspect the latter; that is, I suspect the ASTM standard makes no such
    distinction, since the ASTM is not specific to any application (e.g.
    Excel). In the real world, people need to decide at what point their
    numbers should and should not be rounded. I suspect that the ASTM
    standard specifies that all reported (i.e. visible) numbers and perhaps
    all intermediate computed results are rounded according to the
    standard.

    Of course, the point you made in your article that you cited earlier is
    that __other__ fractions ending in "5" (e.g. 0.05) cannot be
    represented exactly in binary computers. Therefore, a simple
    comparison with 0.05 (e.g.) might be suspicious. And that is where
    your VBA function and the distinction between internal and displayed
    representation becomes important. (Although one could argue that we
    are splitting hairs.)

    But the OP asked specifically about rounding 0.5 to an integer. Since
    0.5 can be represented exactly in binary computers, I think a
    comparison with 0.5 per se is valid and sufficient. Of course,
    reasonable people can have differing opinions. That is simply mine.
     
    , Nov 8, 2006
    #12
  13. Guest

    Guest Guest

    I might agree with you on not fuzzing to 15 figures if Excel allowed display
    of 17 figures (required to uniquely identify a binary floating point value).
    But given that the user is not permitted to see the differences that would
    otherwise drive the rounding, coupled with the fact that the number to be
    rounded is probably the result of a calculation (if you wanted the rounded
    entry wouldn't you just enter the rounded value) and therefore unlikely to be
    exactly .5 when true decimal calculations would be exactly .5, IMHO rounding
    based on what you are permitted to see is more likely to be what users want
    and expect from a rounding routine.

    Jerry

    "" wrote:

    > Jerry W. Lewis wrote:
    > > Also it may not give the intended result if A1 is calculated. There are 14
    > > distinct binary numbers that all display as decimal 0.5, but your formula
    > > will only recognize one of them as 0.5.

    >
    > I realize that; in fact, it was intentional. It is not clear to me
    > whether the ASTM standard applies to "displayed" results or to actual
    > results. I cannot find a (free) online copy of the standard [1]. I
    > suspect the latter; that is, I suspect the ASTM standard makes no such
    > distinction, since the ASTM is not specific to any application (e.g.
    > Excel). In the real world, people need to decide at what point their
    > numbers should and should not be rounded. I suspect that the ASTM
    > standard specifies that all reported (i.e. visible) numbers and perhaps
    > all intermediate computed results are rounded according to the
    > standard.
    >
    > Of course, the point you made in your article that you cited earlier is
    > that __other__ fractions ending in "5" (e.g. 0.05) cannot be
    > represented exactly in binary computers. Therefore, a simple
    > comparison with 0.05 (e.g.) might be suspicious. And that is where
    > your VBA function and the distinction between internal and displayed
    > representation becomes important. (Although one could argue that we
    > are splitting hairs.)
    >
    > But the OP asked specifically about rounding 0.5 to an integer. Since
    > 0.5 can be represented exactly in binary computers, I think a
    > comparison with 0.5 per se is valid and sufficient. Of course,
    > reasonable people can have differing opinions. That is simply mine.
     
    Guest, Nov 8, 2006
    #13
  14. Guest

    plb2882 Guest

    "Ron Rosenfeld" <> wrote in message
    news:...
    > On Tue, 7 Nov 2006 05:53:02 -0800, Will S. <Will
    > >
    > wrote:
    >
    >>This question concerns the environmental field. The EPA recommends a
    >>method
    >>of rounding which is statistically more accurate than the method we all
    >>learned in elementary school. Basically, if the tenth digit is .5, you do
    >>not simply round up, but rather round to the nearest EVEN whole number.
    >>
    >>Ex:
    >>1.5 = 2
    >>10.5 = 10 (not 11)
    >>
    >>The rule only makes a difference when the tenth digit is .5 . Using the
    >>IF
    >>statement, I think I can create a spreadhseet function that evaluates a
    >>number, and rounds to the even digit or rounds normally based on that
    >>evaluation. In order for this to work, however, I need Excel to only
    >>evaluate the first digit after the decimal point. The trouble is, I don't
    >>know how to get Excel to do this.
    >>
    >>I'm hoping that a simple manipulation of an existing function will solve
    >>this since our network may kick out an attempt to use a macro.
    >>
    >>Any help on this problem would be appreciated.

    >
    >
    > Perhaps:
    >
    > =INT(A1+MOD(INT(A1),2)*MOD(A1,1))
    >
    >
    > --ron

    I like this one, short works and does negative numbers.
     
    plb2882, Nov 9, 2006
    #14
  15. Guest

    plb2882 Guest

    <> wrote in message
    news:...
    > PapaDos wrote:
    >> =ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )

    >
    > That was my answer. But to avoid the anomalous failure of MOD() with
    > large numbers, try:
    >
    > =round(a1,0) - and(a1-int(a1)=0.5, iseven(a1))
    >

    This one works for positive numbers, when you get in the negative numbers it
    rounds the wrong way.
     
    plb2882, Nov 9, 2006
    #15
  16. Guest

    plb2882 Guest

    <> wrote in message
    news:...
    > PapaDos wrote:
    >> =ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )

    >
    > That was my answer. But to avoid the anomalous failure of MOD() with
    > large numbers, try:
    >
    > =round(a1,0) - and(a1-int(a1)=0.5, iseven(a1))
    >

    I was trying this out for negative numbers (loss) and it works well.
     
    plb2882, Nov 9, 2006
    #16
  17. Guest

    plb2882 Guest

    "plb2882" <> wrote in message
    news:%...
    > "Ron Rosenfeld" <> wrote in message
    > news:...
    >> On Tue, 7 Nov 2006 05:53:02 -0800, Will S. <Will
    >> >
    >> wrote:
    >>
    >>>This question concerns the environmental field. The EPA recommends a
    >>>method
    >>>of rounding which is statistically more accurate than the method we all
    >>>learned in elementary school. Basically, if the tenth digit is .5, you
    >>>do
    >>>not simply round up, but rather round to the nearest EVEN whole number.
    >>>
    >>>Ex:
    >>>1.5 = 2
    >>>10.5 = 10 (not 11)
    >>>
    >>>The rule only makes a difference when the tenth digit is .5 . Using the
    >>>IF
    >>>statement, I think I can create a spreadhseet function that evaluates a
    >>>number, and rounds to the even digit or rounds normally based on that
    >>>evaluation. In order for this to work, however, I need Excel to only
    >>>evaluate the first digit after the decimal point. The trouble is, I
    >>>don't
    >>>know how to get Excel to do this.
    >>>
    >>>I'm hoping that a simple manipulation of an existing function will solve
    >>>this since our network may kick out an attempt to use a macro.
    >>>
    >>>Any help on this problem would be appreciated.

    >>
    >>
    >> Perhaps:
    >>
    >> =INT(A1+MOD(INT(A1),2)*MOD(A1,1))
    >>
    >>
    >> --ron

    > I like this one, short works and does negative numbers.

    I'm bad this one messes up with negative numbers (losses).
     
    plb2882, Nov 9, 2006
    #17
  18. Guest

    plb2882 Guest

    "plb2882" <> wrote in message
    news:...
    > <> wrote in message
    > news:...
    >> PapaDos wrote:
    >>> =ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )

    >>
    >> That was my answer. But to avoid the anomalous failure of MOD() with
    >> large numbers, try:
    >>
    >> =round(a1,0) - and(a1-int(a1)=0.5, iseven(a1))
    >>

    > This one works for positive numbers, when you get in the negative numbers
    > it rounds the wrong way.

    Sorry this one does work with negative numbers.
     
    plb2882, Nov 9, 2006
    #18
    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. mark

    implementing an automatic formula change

    mark, May 14, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    147
    Frank Kabel
    May 14, 2004
  2. Guest

    Rounding or not rounding

    Guest, Apr 10, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    386
    Guest
    Apr 10, 2006
  3. Guest

    Need help in rounding answers to nearest multiple

    Guest, Jul 6, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    181
    Guest
    Jul 6, 2006
  4. Guest

    I need a formula with rounding up & rounding down to the nearest .

    Guest, May 25, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    779
    Guest
    May 29, 2007
  5. SunshineinFt.Myers

    ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH

    SunshineinFt.Myers, Mar 5, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    7
    Views:
    610
    SunshineinFt.Myers
    Mar 5, 2009
Loading...

Share This Page