+ Reply to Thread
Results 1 to 3 of 3

Serious Excel/VBA bug!!

  1. #1
    Registered User
    Join Date
    01-14-2011
    Location
    N/A
    MS-Off Ver
    Excel/Access/Word 2010
    Posts
    11

    Serious Excel/VBA bug!!

    My test case can be downloaded from http://home.comcast.net/~jasmith4/Ex...g/RoundBug.xls
    It's an Excel 2003 file, but the bug shows up in Excel 2007 and 2010 too.

    The bug has to do with VBA rounding numbers that end with one decimal digit, xxx.5 -- for example, if you round 0.5 you'll get 0, not 1, but if you round 1.5 you'll get 2! Here are a few more results using VBA's ROUND function -- yikes!

    0.5 0
    1.5 2
    2.5 2
    3.5 4
    4.5 4
    5.5 6
    6.5 6
    7.5 8
    8.5 8
    9.5 10

    So I decided to do a little more testing, and here's how I composed the test case:

    First I created a new XLS (Excel 2003) file, added a regular module to its VBAproject, and wrote the following code into it:

    Please Login or Register  to view this content.
    Then in the UI's first tab I added 12 headings to the first row (A1:L1):

    Test number --> A1
    UI Round(#,0) --> A2, etc.
    UI Int(#)
    UI Fixed(#,0,True)
    UI RoundUp(#,0)
    UI RoundDown(#,0)
    VBA Round(#,0)
    VBA implicit conversion
    VBA CLng(#)
    VBA CInt(#)
    VBA Int(#)
    VBA Fix(#)

    Next I added the following formulas to the second row (A2:L2):

    -50.5 <-- this is my first test number, in A2
    =ROUND($A2,0) --> A3
    =INT($A2) --> A4, etc.
    =VALUE(FIXED($A2,0,TRUE))
    =ROUNDUP($A2,0)
    =ROUNDDOWN($A2,0)
    =RoundVBA($A2)
    =DoubleToInt($A2)
    =RoundClng($A2)
    =RoundCint($A2)
    =RoundInt($A2)
    =RoundFix($A2)

    Finally I entered -49.5 into cell A3, selected A2:A3, filled down to A103 (ending up with 50.5), and filled down all the formulas.

    The results are startling!!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,368

    Re: Serious Excel/VBA bug!!

    Why do you think this is a bug? I recall back in school learning that there are basically two schools of thought around rounding 0.5. As most of us were taught in school, because it is the easiest for young kids to grasp, 0.5 rounds up to the next largest whole number. In some circles, the problem with rounding this way is that you create a slight bias towards rounding up. In other words, you will round up slightly more often than you will round down. In order to account for this bias, some people feel it is better to round 0.5 to the nearest even number. The theory being that, in this way, 0.5 will round up half the down and round down half the time.

    I will leave the justification for this behavior to those in numerical science who care about such things (discussed in this Wikipedia article in the section under tie breaking http://en.wikipedia.org/wiki/Rounding). The behavior you are seeing is because the programmers at Microsoft were convinced that one should round 0.5 to the nearest even number instead of rounding up all the time. I believe Excel's ROUND() function behaves like you are expecting (0.5 always rounds up). In essence, (to use the cliche) this is not a bug but a feature. http://support.microsoft.com/kb/225330
    Last edited by MrShorty; 11-14-2013 at 04:07 PM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: Serious Excel/VBA bug!!

    Different functions round differently. See http://support.microsoft.com/kb/196652
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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