Serious Excel/VBA bug!!

**jasmith4** · 11-14-2013, 03:40 PM

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!!

**MrShorty** · 11-14-2013, 04:04 PM

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