When comparing values from various systems, I’d sometimes notice $0.01 differences.
I put the differences down to rounding, but it seemed that no matter which approach I took, the differences could not be reconciled.
It’s one of those things that really bugged me, and I’d feel guilty if I spent too much time on the problem… after all, the differences added up to a lot less than the cost of my time.
The other day when I was comparing values in Excel I used the =ROUND() function and was shocked to see my old friend, the $0.01 difference.
The originating system was one designed by me using VBA, so I took the opportunity to look a bit deeper.
After some time, I isolated the problem to the VBA Round function.
In the image below, A1 to A20 are digits 0.0 to 1.9, B1 downward contains the formula =ROUND(A1, 0), and C1 down contains the formula =VBA_Round(A1, 0).
VBA_Round is a user defined function:
VBA_Round = Round(number, num_digits)
End Function
Excel will round 0.5 to 1, and VBA will round 0.5 to 0, yet they’ll both round 1.5 to 2.
It turned out that Excel and VBA use a different method of rounding, and it is by design.
Excel uses arithmetic rounding, and VBA uses Banker’s rounding. Bankers rounding means that you round a 5 to the nearest even number. There’s even a Microsoft Knowledge Base article about it.
For further reading, EWBI also has very good information on the topic.
Anything that requires rounding in VBA uses Banker’s Rounding. For example, both CLng(1.5) and CLng(2.5) will return 2 as the converted value because Banker’s Rounding lies at the heart of the conversion. HOWEVER, there is one (and only one as far as I have discovered) function in VBA that uses what I call “normal rounding”… you know, 5’s at the end of a number being rounded to the decimal position in front of the 5 always round up, not up or down depending. That function is the Format function. For example, Format(2.5, “0?) or Format(2.5, “#”), depending on if you want to force zeroes or not, will round up to 3 as expected (at least, as expected by me). Whatever number of decimal places you choose to round to (using the format pattern to set the number of decimal places). It appears that the Format function uses a Decimal subtype of a Variant as its data type since these expressions all round as expected for “normal rounding”…
Format(“1.234567890123456789012345674?, “0.00000000000000000000000000?)
Format(“1234.567890123456789012345675?, “0.00000000000000000000000?)
Notice the limit of 28 digits (and, of course, the need to feed numbers with more than 15 digits as a String). Okay, with that said, here is a “normal rounding” UDF/function that you can use on a worksheet or within your own code…
Function NormalRound(Number As Variant, Optional NumberOfDigits As Long) As Double
If NumberOfDigits < 0 Then
NormalRound = Format(CDec(Number) * 10 ^ NumberOfDigits, “0?) / 10 ^ NumberOfDigits
Else
NormalRound = Format(Number, “0.” & String(NumberOfDigits, “0?))
End If
End Function
This function assumes normal numeric input for the Number argument and normal limits for the NumberOfDigits argument (you can improve it by adding some error checking for the limits if you wish). Note that this function permits the NumberOfDigits argument to be negative (similar to the worksheet’s Round function). Also note the NumberOfDigits argument is optional (defaulting to 0 if omitted).
Of course, if you want normal rounding inside of your own code, you can call out to the worksheet’s ROUND function…
NormalRoundedNumber = WorksheetFunction.Round(1.2345, 3)
By way of explanation for my first posting… my background before coming to Excel was in the compiled VB language and it had no access to the Excel worksheet functions, so the Format function was a savior there as it regards normal rounding. Since the worksheet’s ROUND function is a simpler method of getting normal rounding inside your own code, consider my first posting a historical document more than a “you should do this in your own code” and take into consideration the reason I posted it was because it is after 3:00 AM and I should have gone to sleep long before I decided to comment on this blog article.[grin]
Ken Getz and Mike Gilbert suggest to convert the value to a Decimal datatype first. The following example is from their 2nd Edition of “VBA Developer’s Handbook”, page 113 (try it in the immediate window)
?Round(9.575, 2)
9.57
?Round(CDec(9.575), 2)
9.58
Regards,
Dominik.
Interesting, I wasn’t aware of that.
Wouldn’t the general solution in both Excel and VBA to have a third argument to ROUND, which specifies the rounding-type (xlRoundBankers :-), etc)? Default to xlRoundArithmetic, probably.
/Roy
Just as a side note: Access uses bankers rounding too, so if you use the Round function within a query, it will use bankers rounding.
My way around it is to have a function in a module that has the same name:
On Error Resume Next
Round = CDbl(FormatNumber(Number, NumDigitsAfterDecimal))
End Function
In Visual Basic for Applications, the following numeric functions perform banker’s rounding: CByte(), CInt(), CLng(), CCur(), and Round().
Source: http://support.microsoft.com/kb/196652/EN-US/
More on rounding floating-point numbers:
http://en.wikipedia.org/wiki/IEEE_754-1985#Rounding_floating-point_numbers
Regards,
Dominik.
@Dominik,
That list is not complete. As I said in my first message to this thread…
“Anything that requires rounding in VBA uses Banker’s Rounding”
except for the Format function (and its various incarnations as Jan Karel’s FormatNumber example shows). Just two items off the top of my head that are not on that list are the Mod and (Integer Division) operators. Except for the Format function (and its various incarnations), if an operation involves rounding at all then it uses Banker’s Rounding.
For those who might be interested, here is (what I think is) an interesting note that I posted to the compiled VB newsgroups many years ago about Integer Division as it relates to the Banker’s Rounding issue…
If floating point numbers are involved, integer division may not return the answer you expect. Most people think xy is short-hand for Int(x/y), probably because of its name “integer” division. However, VB applies Banker’s Rounding to the numerator AND denominator BEFORE performing the division. Many would think that this…
MsgBox 4.5 1.5
should display 3 in the MessgeBox, however, it prints out 2 instead. Banker’s Rounding rounds the 4.5 to 4 (the nearest even number) and rounds 1.5 to 2 (again, the nearest even number) AND ONLY THEN does it do the division and truncate the fractional part (4/2 producing 2 as the answer).