Banker's Round / Half to Even Rounding problem
Results 1 to 5 of 5

Thread: Banker's Round / Half to Even Rounding problem
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    563
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Banker's Round / Half to Even Rounding problem

    I had a need for a Banker's rounding formula (aka Half Round to Even) so I did some searching and found a Mr. Excel video referencing a formula by Barry Houdini. My need was to round figures to the nearest even penny. (5.145 rounds to 5.14 and 5.155 rounds to 5.16) This is designed to keep the overall total close to the actual figure by sometimes rounding up and sometimes rounding down on an exact half cent.

    The formula in question is: =ROUND(A1,2)-(MOD(A1,10)*1000,20)=5)/100

    I found an interesting problem with it when the figure being rounded is based upon a calculation vs. just a number. In cell A1, I have a calculation and in cell A2, I have the same number just keyed in with no formula. One works and one doesn't and I can't see why. Any help would be appreciated. Thanks



    Microsoft Excel - Book2___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    6.5250000000000000 6.52 
    2
    6.5250000000000000 6.53 
    3
        
    4
    TRUE   
    Sheet2 

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Posts
    563
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Banker's Round / Half to Even Rounding problem

    The formula in question is: =ROUND(A1,2)-(MOD(A1,10)*1000,20)=5)/100
    Sorry, this should have been: =ROUND(A1,2)-(MOD(A1*1000,20)=5)/100

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,588
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Banker's Round / Half to Even Rounding problem

    As reluctant as I am to modify one of barry's formulas, maybe

    =ROUND(A1,2) - (MOD(ROUND(A1, 4) * 1000,20)=5)/100

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Posts
    563
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Banker's Round / Half to Even Rounding problem

    Quote Originally Posted by shg View Post
    As reluctant as I am to modify one of barry's formulas, maybe

    =ROUND(A1,2) - (MOD(ROUND(A1, 4) * 1000,20)=5)/100

    shg.... Sorry, just getting back to this thread. After posting my original post, I did try the same thing you did but I rounded A1 to 5 spaces which worked. I then changed it to 10 just to be safe and it still worked so I left it at that. I guess it has something to do with the calculation. ??

    Thanks for the help

  5. #5
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,588
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Banker's Round / Half to Even Rounding problem

    If you were doing this in VBA with data type Currency, the least significant bit is exactly $0.0001 -- that's why I chose four decimals. If 10 works for you, that's fine, but if the dollar values are > $10,000, rounding to 10 decimals doesn't do anything.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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