2003.09.14 08:53 PM

VBA Round (and round and round)

This post is superceded by VBA Round (groan). I believe you'll find it more complete and accurate.

I recently posted a query to a feature called "Ask a Language Designer" (or something like that) on the blog of one of the VB gurus at right (I can't recall which one). What I wanted to know was how VB came to use banker's rounding (described here, here, or just search; here's the definitive MS rounding explanation in KB 196652) instead of the rounding method I learned in school, which was .1 thru .49 down, everything else up.

It's not that I have anything against the technique - as pointed out by many of the explanations, it is more accurate when applied to large sets of numbers. I was really just interested in how it came to be VB's preferred technique. A quick check of my IBM BASIC 3.0 manual (3rd edition, 1984) indicates that it used asymmetric arithmetic rounding, not banker's rounding. The two examples it provides don't prove this conclusively (and I don't have a copy running around here anywhere), but it refers to its method as "rounding (up)" then shows CINT(45.499) returning 45 (hence the 'arithmetic') and shows CINT(-2.89) returning -3 (hence the 'asymmetric'). So, what's the story behind VB's adopting the banker's technique?

The only reason I even bothered to ask was because the issue of rounding had just bit me again. Apparently, I am insane. Even though I've worked in VB since before version 3.0 and in all manner of VBA since its inception, I still fail to remember that VB works this way every time I engage in development of an app that requires some level of programmatic rounding. Without fail, some very late night (usually the night before delivery) I will notice that 2.5 just rounded to 2 and I will freak out and start a panicked debugging session involving lots of Googling only to find out (again) what I already know. After some forehead slapping and grumbling I will then either live with it (depending on the app) or override the VBA.Math.Round function with my own, something like this:

Public Function Round(Number As Double, Optional NumDigitsAfterDecimal As Integer = 0) As Double
Round = Fix(Number * (10 ^ NumDigitsAfterDecimal) + (0.5 * Sgn(Number))) / (10 ^ NumDigitsAfterDecimal)
End Function

However, my insanity is not the end of the story (or is it?). In my latest effort (a reworking of an old Access 2.0 database into Access 2002), in which I dutifully repeated the above insanity and implemented the above Round function, I noticed (purely by chance) that something was wrong with my Round function. Before I explain what, let me explain how I found it.

Contrary to the banker's rounding found in the VBA.Math.Round method, and in the VBA.Conversion.CInt and CLng methods, the VBA.Strings.Format method takes a different approach, namely asymmetric arithmetic rounding, the technique I always insanely expect. So, the above function could be written (with a few more variables, plus the whole String/Variant conversion overhead) as a call to Format with a string of #'s or 0's equal to NumDigitsAfterDecimal. It was this similarity in rounding techniques that highlighted my Round function's problem. On one of the app's forms, an hourly rate was multiplied by a number of hours and mistakenly not sent through my Round function prior to being put into a locked textbox formatted as "$0.00". In so much as my Round function and the Format method do the same thing, this should have been okay. But it wasn't, and I discovered that it wasn't because of another little weirdness in Access.

In Access, specifying a numeric format for an editable textbox without also specifying a matching input mask allows users to enter a value with greater precision than you may want. The value is rounded to the specified decimal places for display (only showing the extra digits when in focus and being edited), but the extra precision may be kept (depending on the FieldSize of the underlying table column). It's a little confusing. See the DecimalPlaces property help for all the details.

In any case, due to this weirdness and my hatred (and, thus, avoidance) of input masks, I was deliberately testing how the app would behave if a user entered hourly rates and/or hours having more than two decimal places. One of many sets of values I entered into the form mentioned above was a rate of $1 and hours of 1.335. The value shown in the "$0.00"-formatted textbox was the correct 1.34. Later, when I realized that the form was not applying my Round function to the result prior to display, I decided it needed to be fixed (owing mostly to my paranoid belief that the client would one day ask me to make banker's rounding the expected behavior and I would therefore want all programmatic rounding running through one routine). So I fixed the form and re-tested it, only entering a handful of values this time, because I was sure my Round function and the Format method worked the same. As my lucky stars would have it, one of the value sets I entered was the previously entered $1 and 1.335 hours, but this time the result in the textbox was an incorrect $1.33. Yikes.

I figured it had something to do with the inherent imperfection of floating point operations and so began by seeing if there was a range of numbers over which it would match the Format method. I discovered that the turning point was between 7 and 8 (i.e., 7.335 returned 7.33 and 8.335 returned 8.34). Also, I ran the same value through the Round function repeatedly to see if it ever returned the correct value, and it did (rarely2). So, I broke up the one-line formula into separate steps, with each intermediate result coming back to a Double, and the results matched the Format method. I was done, but very unhappy with that solution. Then I remembered that VB converts down the types of numeric constants found in expressions, and so tried the one-line formula again with explicit Double markers ("#") on each constant value. This didn't work, but then I figured VB must also do this for intermediate results within an expression, and so I reworked the Round function's one-line formula like so:

Round = Fix(CDbl(Number * (10 ^ NumDigitsAfterDecimal)) + (0.5 * Sgn(Number))) / (10 ^ NumDigitsAfterDecimal)

It now matches the Format method every time (actually, it doesn't; see the link at top for a better explanation and function). Note that only the first intermediate result needed the explicit Double conversion, the others were apparently of no consequence. If I were smarter I'd know why. Maybe because in this case the first one suffers an implicit Double-to-Single conversion?

Of course, as I'm sure everyone has already surmised, I could have avoided all of this trouble by simply creating a Round function based on the Currency data type instead of the Double, as I knew that I was only going to be working with Currency data types anyway and never needed more than 4-decimal precision. But, that kind of thinking fails to consider the depth of my insanity. Namely, that I couldn't stand the idea of overriding the VBA.Math.Round function with a same-named but differently typed version, and I didn't want to unnecessarily pollute the system with an additional function.

Round on, dude.

p.s. - A quick check of .NET shows VB continues to use banker's rounding.


Comments

Actually, it's not just VB in .NET that uses banker's rounding. The System.Math.Round() function uses this technique.

ewbi.develops | 2003.09.24 05:56 PM

You might also want to look at this later post of mine, which includes additional information on VBA, VB, VBScript, and VB.NET rounding, as well as links to a couple of very interesting posts by Eric Lippert concerning this topic:

http://ewbi.blogs.com/develops/2003/09/quick_addition_.html

Please feel free to leave a comment if you have any questions or concerns about implementing an alternative rounding strategy of your own in any of these environments.

ewbi.develops | 2003.10.03 02:12 AM

If I understand you correctly your code should be doing an arithmetic round (always round up when ending with 5)? It does not in all cases.

37.535 to 2 decimals round to 37.53. 37.035 also rounds down to 37.03

chris | 2003.11.04 01:24 PM

Wow. Thanks Chris. I knew the imprecision of floating point numbers would get me in the end. I was never comfortable with the reworking of the custom Round function's logic with the addition of the CDbl() on the first immediate result in the expression. I didn't fully understand why it improved the consistency of the expression, and wasn't sure that it did in all cases. Clearly, I should have said "it now matches the Format method...in all the cases I tested", instead of the broader statement I did make.

After some investigation using the number 37.535, what I discovered was that the CDbl(Number * (10 ^ NumDigitsAfterDecimal)) expression is not returning 3753.5, as expected, but is actually returning 3753.5 - 4.54747350886464E-13, putting the result just a hair below .5. So, when .5 is subsequently added to it, the result is not 3754, but is instead just short of it. The subsequent Fix throws away the decimal portion, leaving only the integer portion, which is now 3753, not 3754. When the decimal is moved back, the result is 37.53 instead of the 37.54 expected.

I'm going to have to think about this a little bit more. In the short term, a Currency-type version of this routine would help (it would certainly solve for 37.53), but I want a pure Double routine that matches the Format function every time. Hopefully I can post an update tomorrow.

Chris, thanks again for bringing this to my attention. If you get a chance, I'd sure like to know how you stumbled onto these particular numbers.

ewbi.develops | 2003.11.04 02:56 PM

Well, I wish I could say I picked that number because I understand every detail of how rounding and floating point numbers work... but the number came to me as a result of a problem in my code. I was searching for more information (and a solution) and ended up here.

My problem is that in my vb6 app I display a number which I need to round to 2 decimal places. I also calculate and round that same number in SQL. The vb6 version is just for display purposes, the SQL version is what I'm declaring as the real value I want since most of our business logic is in stored procedures. So I was looking for an arithmetic rounding function to use in my VB app.

chris | 2003.11.05 07:02 AM

Chris - I'm about to save a new post on rounding that describes the problem with this rounding routine and illustrates a couple of other (better) options. It also consolidates all the rounding info I've been accumulating here.

It sounds like you would be well served by the VBA FormatNumber function. It takes as parameters the number and decimal places to preserve. Unlike the Round, CInt, CLng, etc. functions, FormatNumber does not use banker's rounding - it uses asymmetric arithmetic rounding. Also unlike those other functions, which all return numbers, FormatNumber returns a string.

Good luck. And thanks again for taking the time to post a comment.

ewbi.develops | 2003.11.05 10:09 AM

My particular application involves foreign currency conversion and needs to apply in a generic manner to currencies from around the world. I also need to deal with some precision in the rates that the currencies are exchanged at, and that's why I'm looking for a generic double solution. I'll look into FormatNumber to see if it helps me.

Thanks for the posts and links on the topic, they've been very helpful. I hadn't realized all of the issues related to rounding floating point numbers. I look forward to your new post.

chris | 2003.11.05 01:38 PM

Here's the new post on rounding:

http://ewbi.blogs.com/develops/2003/11/vba_round_yet_a.html

Provides some better Round function alternatives, and a more complete explanation. Sorry for any confusion.

ewbi.develops | 2003.11.07 01:27 AM

Hey, I jst wanna help you about this problem of the century. :D

Try FormatCurrency and you'll see. :)

Paul Vargas | 2004.04.29 10:23 PM

Thanks,
I had tried 4 different very detailed rounding functions
when I realized after reading your article that all I
needed to do was used the built in format$ function
and most of my pain went away.
I was actually already using the following line:
whatever$=format$(rndfnc(nm#,decplcs#),"#0.00")
when all I had to do was remove the rndfnc
and quit looking for a better one.

Stefan | 2004.06.27 06:14 PM

thanks for giving good soln which will save a lot of time of mine.

dilip | 2005.03.23 12:01 AM

dilip - you're welcome, but please be sure and check out the later post where I correct some issues with, and generally clarify, the floating point rounding example given above:

http://ewbi.blogs.com/develops/2003/11/vba_round_yet_a.html

ewbi.develops | 2005.03.23 12:30 AM

Thanks for the function, just bumped into the problem myself. You may well have saved me hours of typing (or having to write work-arounds...)

Thanks a bunch.

Simon | 2006.07.10 07:52 PM

Simon - Be sure and check out the more accurate FormatNumber-based routine in the updated post linked at the top of this post. Eventually the floating point routine in this post will cause you grief. Good luck.

ewbi.develops | 2006.07.10 08:06 PM

The rounding in .NET was a pain when dealing with 3 decimal places. Your little code snippet worked a treat :)

Rob | 2006.08.29 08:04 PM

Yeah, thanks for this little but valuable research. The official Microsoft's solution is far from being so useful as yours.

Eugene | 2007.05.03 04:01 AM

Thanks, Eugene. If you haven't yet, though, please follow the link at top over to the later post for a more accurate explanation and floating point routine.

ewbi.develops | 2007.05.03 09:54 AM

thanks a lot for your function

Han Htoon Aung | 2008.05.23 03:53 AM

I need an excel formula or a macro to do bankers rounding in excel. I have many, many formulas and VBA macros (most using the VBA round function & additional calculations).
The problem: if I use the formula/macro in one cell to round another cell's number(from a calculation in that cell), all have exceptions - some more than others - but, all have exceptions. And, there does seem to be a difference if a number is entered in a cell vs a number is in a cell from a calculation (even a simple addition).
If it is possible, I need to round numbers to 1 & 2 decimal places and whole numbers. Does anyone know if a reliable formula exists?
Thanks,
Bill

Bill | 2011.06.22 11:08 AM

Bill, I'm not sure what types of exceptions you're encountering or why. Perhaps because the VBA rounding function(s) you are using aren't taking the right data type (Variant) or checking that the incoming values are Doubles before attempting a rounding operation? Here's what I use (it's going to format spectacularly bad here in the comments):

Public Function BRND(value As Variant, places As Integer) As Variant
If VarType(value) = vbDouble Then
BRND = Round(value, places)
Else
BRND = value
End If
End Function

If that doesn't work for you come back and let us know what kinds of exceptions you're getting. It would really help if you could isolate it to one or two cell/function/formula examples. Good luck!

ewbi.develops | 2011.06.22 11:29 AM

Well, I will give it a try. But, essentially, you are using the VBA rounding function & I have tried many.
The problem I have encountered using a testing spreadsheet:
I made a spreadsheet with a number in a cell(A3). In A4, I have 0.005+A3. And - in A5, I have 0.005+A4. And, so on to A1500. The rounding function is in an another column (C).
One exception is @ A51, in A50 is 0.0245 - so, A51 is 0.025. So in cell C51, I have BankRound(A51,2). The result is 0.03 (wrong). If I just enter the number 0.025 in cell A51, I get 0.02 (correct) in cell C51. If I use this BankRound((0.0245+0.0005),2), I get 0.02 (correct).
However in order to use this function in my spreadsheets, I need to use the format BankRound(A3,2).
Helpful?
I have many giving the same results - but, BankRound:
' ZVI:2008-07-18 True VBA Banking Rounding
Function BankRound(Number As Double, Optional Digits = 0) As Double
If Fix(Number * 10 ^ Digits) Mod 2 = 0 Then
' Even
BankRound = Round(Number, Digits)
' Exclude -0 return value
If Abs(BankRound) = 0 Then BankRound = 0
Else
' Odd
BankRound = CDbl(FormatNumber(Number, Digits))
End If
End Function

Bill | 2011.06.22 12:15 PM

Bill, I think there's a little confusion here. You don't have to check the even/oddness of the numbers you're passed before calling the VBA Round function. That's what the VBA Round function does - it determines the even/oddness in order to do banker's rounding. And it does it very accurately. Whereas your even/odd check is going to fail for some numbers owing to the the inexact representation of floating point numbers (see http://www.lahey.com/float.htm). I think the simpler BRND function I've given above should get you what you need.

ewbi.develops | 2011.06.22 12:26 PM

Thank you for the help.

I am not sure that I understand - but, I will read the link.

I have used one as simple as:
Function VBA_Round(number As Double, num_digits As Long) As Double
VBA_Round = Round(number, num_digits)
End Function

This gives the same results.

And, I just tried the BRND function & ended up with the same results.

This is just a spreadsheet I am using to determine if a function will work without exception.

For the spreadsheets I need the function for, I would have a value in one cell (Say,C5) that would use a value from various other cells that also have calculations from different cells. Then, use a formula: BRND(C5,2).

Helpful?

Bill | 2011.06.22 12:49 PM

Thank you for the help.

I am not sure that I understand - but, I will read the link.

I have used one as simple as:
Function VBA_Round(number As Double, num_digits As Long) As Double
VBA_Round = Round(number, num_digits)
End Function

This gives the same results.

And, I just tried the BRND function & ended up with the same results.

This is just a spreadsheet I am using to determine if a function will work without exception.

For the spreadsheets I need the function for, I would have a value in one cell (Say,C5) that would use a value from various other cells that also have calculations from different cells. Then, use a formula: BRND(C5,2).

Helpful?

Bill | 2011.06.22 12:49 PM

Sorry for the second post. Also, I tried the link & could not be directed to it. I will give it a try at home.

And, I have tried an even simpler one:

Public Function HalfWayEven(num, digits)
HalfWayEven = Round(num, digits)
End Function

For 0.65 in A5 (A4 + 0.05), the function gives: 0.7

But, if I just enter 0.65 into cell A5, the function returns 0.6

Am I just doing something wrong, or do all functions have exceptions?

Any help is greatly appreciated & thanks.
Bill

Bill | 2011.06.22 01:15 PM

No worries. You are right. I've reproduced the issue. The problem appears to be how Excel represents the source range's value to the VBA function, whether the function parameter is a double or variant (which for the latter may get us a double or a range, which for range means we're accessing the value from the default Value property, whether implicitly or explicitly).

In any case, passing into BRND the result of a series of simple formulas adding .005 to 1, until we're at 1.055, results in the BRND function getting a very interesting value, one which is not exactly 1.055. In fact, it's 1.11022302462516E-15 less than 1.055, which results in the VBA round function not seeing it as odd, but instead as even, so it rounds down to 1.05. Here again, the inexactness of floating point numbers. Still, I'm quite surprised to see Excel not managing to express the numbers more accurately on the way into a VBA function. Without spending more time on it, I can't see you overcoming this except with a fudging epsilon, as per the Mea Culpa section of this:

http://ewbi.blogs.com/develops/2003/11/vba_round_yet_a.html

Thanks for bringing this to everyone's attention. Perhaps if I get more time I'll write it up and/or try to come up with a safer way to get and round a value from Excel. Good luck!

ewbi.develops | 2011.06.22 01:46 PM

Thanks for the up-date. I was off yesterday.
I have been working on this for several months - proper bankers rounding is the only requirement left for all of my spreadsheets. But to be accepted, I must supply proof that there is no exception to QA. At this point, I have simply given up trying & require help from someone with far more knowledge than I have on the subject. And, it may just be impossible to accomplish the proper rounding in excel & a different type of software may be required.
If anyone has any advice or can offer suggestions - I would be very grateful.
Thank you,
Bill

Bill | 2011.06.24 04:31 AM


TrackBack

TrackBack URL:  https://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83421106553ef

Listed below are links to weblogs that reference VBA Round (and round and round):