Serious excel/vba bug!

[jasmith4]

My test case can be downloaded from http://home.comcast.net/~jasmith4/ExcelVBAroundBug/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:

Option Explicit

Public Function RoundVBA(TestNumber#)
    RoundVBA = Round(TestNumber#, 0)
End Function

Public Function DoubleToInt&(TestNumber#) ' implicit conversion
   DoubleToInt& = TestNumber#
End Function

Public Function RoundClng&(TestNumber#)
   RoundClng& = CLng(TestNumber#)
End Function

Public Function RoundCint%(TestNumber#)
   RoundCint% = CInt(TestNumber#)
End Function

Public Function RoundInt(TestNumber#)
   RoundInt = Int(TestNumber#)
End Function

Public Function RoundFix(TestNumber#)
   RoundFix = Fix(TestNumber#)
End Function

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

 

[Rick Rothstein]

My test case can be downloaded from http://home.comcast.net/~jasmith4/ExcelVBAroundBug/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!

It is not a bug, rather, it was a conscious decision that Microsoft made a long time ago. The rounding you are seeing is known as "Banker's Rounding"... you can look it up online to find out more about it (here is one link... Banker's rounding). Just so you know, any rounding anywhere within VB uses Banker's Rounding with one exception... the Format function uses what you (and I) consider to be "normal" rounding. So, if you wanted to round your numbers to whole numbers, use this...

Format(YourNumber, "0")

If you want to round to the second decimal place, then use this...

Format(YourNumber, "0.00")

and so on. Note, you could also use # signs in place of the 0's if you did not want to "fix" the number of decimal places returned (kind of depends on what you plan on doing with the rounded number).

 

[jasmith4]

Thanks, Rick -- I'm getting the same response from MSDN about "bankers' rounding". So I'm going to post my same reply: please show me any documentation on VBA's ROUND function that either uses the phrase "bankers' rounding" or describes rounding to an even number. That lack, I think, is the real serious bug. I've been training people in Excel and doing pretty significant VBA development for years, and I've never heard of bankers' rounding -- I find it pretty silly, if only because it goes against what I learned in grade school!

Your solution is OK, except that FORMAT returns a string, so you'll have to apply VALUE to it. But the better solution, as well as a serious inconsistency, is: the UI's ROUND function rounds "normally", whereas only VBA uses this strange bankers' rounding!

 

[shg]

Cross-posted at http://www.excelforum.com/excel-programming-vba-macros/968552-serious-excel-vba-bug.html

And, as posted there, see http://support.microsoft.com/kb/196652

 

[Rick Rothstein]

Thanks, Rick -- I'm getting the same response from MSDN about "bankers' rounding". So I'm going to post my same reply: please show me any documentation on VBA's ROUND function that either uses the phrase "bankers' rounding" or describes rounding to an even number. That lack, I think, is the real serious bug. I've been training people in Excel and doing pretty significant VBA development for years, and I've never heard of bankers' rounding -- I find it pretty silly, if only because it goes against what I learned in grade school!

Your solution is OK, except that FORMAT returns a string, so you'll have to apply VALUE to it. But the better solution, as well as a serious inconsistency, is: the UI's ROUND function rounds "normally", whereas only VBA uses this strange bankers' rounding!

Here is an official Microsoft KnowledgeBase article that discusses the rounding method and mentions "Banker's Rounding"...

PRB: Round Function different in VBA 6 and Excel Spreadsheet

and as you can see from the article, the rounding method is by design. As for the Format function... it does not return a String, rather, it returns a Variant with a sub-type of String, but as a Variant, it will automatically convert to numberic types if used in numeric calculations. By the way, in Excel, and alternative exists... you can have VB use the worksheet's ROUND function within your VB code via the WorksheetFunction argument. For example...

YourNumber = 2.5
NormallyRoundedNumber = WorksheetFunction.Round(YourNumber, 0)

 

[jamcall]

Just an FYI... this is something that went around years ago - but I hadn't even thought of 'til now...

Although the Round function is useful for returning a number with a specified number of decimal places, you can't always predict how it will round when the rounding digit is a 5. How VBA rounds a number depends on the internal binary representation of that number. If you want to write a rounding function that will round decimal values according to predictable rules, you should write your own. For more information, see the Visual Basic Language Developer's Handbook by Ken Getz and Mike Gilbert (Sybex, 1999).

From: Conversion, Rounding, and Truncation

Cheers,
~ Jim

 

[jasmith4]

1. It's horribly inconsistent that Excel's ROUND and VBA's ROUND are named the same but do different things.
2. It's a stunning lack of omission that the entire help has nothing about bankers' rounding, even though six functions implement it.
3. Anything called ROUND that doesn't do what we all learn in grade school is simply wrong, if only because it's unintuitve.
4. Bankers' rounding is so obscure that there should be a separate function called BANKERSROUND to implement it -- and with some Help that actually describes it!

 

[shg]

Also Replace and REPLACE, Mod and MOD, Weekday and WEEKDAY ...

Maybe next we could launch into why =-1^2 returns 1 in Excel and -1 in VBA.

 

[jasmith4]

• VBA REPLACE = Excel Substitute
• VBA MOD is an operator; Excel's Mod is a function
• Because the unary negation operator binds at higher precedence than the exponentiation operator, so "=-1^2" is "(-1)^2", not "-(1^2)".

 

[shg]

Indeed to all. Now just add Round and ROUND and we'll have come full circle.

Also, Mod operates only on Longs, and rounds by truncation.