2003.11.04 09:09 PM

VBA Round (groan)

I can't believe I'm writing about rounding again. I'm compelled to do this because a reader named Chris was kind enough to point out that the precision of my previously described replacement for the VBA Round function fell short of the mark. I figured this was the case when I wrote it, then hack-patched it with a CDbl and blogged about it, but I didn't have time to figure it out then.

Of course, I don't have time now either, but I get a dozen or more folks looking at that post every day, usually as a result of a Google search. Clearly folks are still stumped by VBA's rounding, and I hate to be responsible for leading them astray with a half-jiggered solution and an incomplete explanation. And, to be honest, my last post had as much to do with Access textboxes and my insanity as it did with VBA rounding. So, I'm going to try again. This time, it's all about rounding (well, mostly).

Smoke 'em if you got 'em, because this post gets long.

Caveats

Let's get this out of the way first. I am not an expert on rounding. I am not a math major. I am not a low-level programmer. I prefer not to twiddle bits or worry about what IEEE standard my chosen language has selected for handling floating point operations. That's not my bag.

I am a business application developer. I solve business problems with technology. I spend my time investigating and solving problems, and leveraging (higher-level) languages, development environments, operating systems, and system services to solve my clients' needs. I find low-level things like operator overloading and generics interesting (in an adolescent "hey, cool car!" kind of way), but I really don't want to know how the apps I develop add two numbers together.

That's why this whole rounding thing pisses me off. In nearly all VB-related things (and I'm talking about VB, VBA, VBScript, and VB.NET, and the environments that rely on them, like Access, Excel, and Word), I get to think about the business problem first and the language second. That's the beauty of VB, its raison d'être. I ask it to add, it adds. I ask it to divide, it divides. But, for some reason, if I ask it to round, it turns 2.5 into 2. Grrrr.

What We Get

The VBA library's Round function, which is shared by VB, and the separately implemented VBScript Round function, all use a technique known as banker's rounding. Sometimes also called round to nearest, or round to even. Banker's rounding rounds .5 up sometimes, and down other times. The convention is to round to the nearest even number, so that both 1.5 and 2.5 round to 2, and 3.5 and 4.5 both round to 4. If you care, it is described by IEEE Standard 754, section 4. It is the same technique used by the VBA and VBScript conversion functions CByte, CInt, CLng, and CCur.

The argument for using this technique goes something like, "When rounding large sets of numbers, a constant rounding of the middle number (i.e., .5) always up or always down results in a bias. By alternating the direction of the rounding, the bias is reduced and statistical accuracy is increased." For all I know, that's probably true. Darnley Bynoe, a friend of mine who is considerably smarter than me about such things, laid out the math and made a fairly convincing argument for why this is true. I'm still not convinced, though, as the advantage he described appears to require an infinite set of numbers to materialize, and I think he forgot to consider the numbers between .0x and .1. Whatever. Like I said, I'm not an expert.

What I do know is that I rarely round over large sets of numbers. The applications I write tend to be concerned with rounding one significant number at a time. I'm usually rounding a number that appears somewhere in a calculation, and the context for the measure of its accuracy is not blurred by a thousand sibling numbers. It sticks out. So if one of my apps rounds 2.5 to 2, lots of people notice, and they call it a "bug".

Consider the calculation of a retirement benefit. I'm not talking about the rounding of its final dollars and cents. (When dealing with money, you can and probably should stick to the Currency data type. It's exact to 4 decimal places and fairly easy to round.) What I'm talking about is the calculation of a tax rate to 8 decimal places, or the interpolation of an actuarial table to 6 places, and the subsequent application of these values to things like fractional hours or years according to specified or generally accepted principles. In all my years of doing this, never has an actuary or a CPA said to me, "When you round the do-hickey factor to 5 decimal places, be sure and use banker's rounding, because we want .444725 rounded to .44472 instead of .44473." Never. Yet this is exactly what we get with Round.

Is banker's rounding better? Maybe. Is it what folks want? I don't think so. Darnley likens it to the metric system of measurement, at least in the US:  "Better, but not expected."

What We Want

What I want, and a lot of folks who go searching on Google and find this blog apparently want, is what's called arithmetic rounding. Sometimes called 4/5 rounding, or round to larger. It's simple. It's the rounding technique we all learned in grade school. Numbers at or above .5 round up, everything else rounds down.

The only tricky thing about this rounding technique is what to do with negatives. If you followed the rule just described to the letter, then -2.5 would round to -2 (i.e., up), but what folks often want is for "up" to mean "away from zero" so that -2.5 rounds to -3. The former is called symmetric arithmetic rounding, because positives and negatives are treated the same way (up is up). The latter is called asymmetric arithmetic rounding, because positives and negatives are treated differently (when negative, up is down).

If what you want is asymmetric arithmetic rounding, and you're working with VB, VBA, or VBScript, then you're going to have to roll your own. Interestingly, the easiest way to do this is to leverage an existing VBA or VBScript function that already does it. "Huh?! Didn't you just say that VBA and VBScript use banker's rounding? What are you, a moron?" Yes I did, and yes I am.

Let me be clear. The VBA and VBScript functions you would expect to use for rounding (e.g., Round, CInt, etc.) do all use banker's rounding. However, VBA and VBScript also provide some functions for converting and formatting numbers (and dates, and lots of other things) to Strings. Turns out these functions also do rounding. And, unlike their mathematically challenged brethren, they use asymmetric arithmetic rounding, not banker's rounding. Yippee. In VBA, these are the Format and FormatNumber functions. In VBScript, there is only the FormatNumber function.

While we're on the subject of morons, whose bright idea was it at Microsoft to utilize two different rounding techniques in the same language anyway? This was the core rant in my earlier post. If I explicitly Round a number and present it, I expect to see the same damn thing if I instead implicitly round it with Format and present it. But I don't. Grrrr, again. I can forgive Microsoft for adopting banker's rounding (one might say they did the right thing, though certainly also the obscure thing). And I can almost forgive them for not consistently adopting this technique in all their products, like Excel (after all, they buy some products and are forced to adopt compatible standards in others). But I cannot forgive them for adopting two different rounding techniques in the same damn language (and then for documenting it so badly).

Enough ranting, let's go get what we want.

Show Me the Round

What follows are two asymmetric arithmetic rounding functions with which to replace the banker's rounding Round functions in VBA and VBScript.

In VBA and VB:

Public Function Round(ByVal Number As Double, Optional ByVal NumDigitsAfterDecimal As Integer = 0) As Double
  Round = CDbl(FormatNumber(Number, NumDigitsAfterDecimal))
End Function

In VBScript:

Function Round(ByVal Number, ByVal NumDigitsAfterDecimal)
  Round = CDbl(FormatNumber(Number, NumDigitsAfterDecimal))
End Function

Note that VBScript doesn't allow for creation of functions with optional parameters, so NumDigitsAfterDecimal is required in the VBScript version. The VBScript version also explicitly converts the result to a Double (this is necessary to avoid returning a String), whereas the native VBScript Round function will return whatever type is appropriate relative to the type of Number passed in. The parameters are all ByVal to allow non-Double numeric variables to be passed in without causing ByRef type mismatch errors. Neither function bothers to check whether NumDigitsAfterDecimal is >= 0, so be careful and/or add you own assertions. Also, the untyped VBScript version should probably check to make sure it is even getting numbers. Finally, if you don't want to replace the native Round functions, be sure and give the functions different names.

Rounding Access Data

If you're using Access, and your interest is not explicit programmatic rounding, but is instead rounding of values that appear on forms and reports, you're in luck (sort of). The value-formatting options configured via the Format and Decimal Places properties of table and query columns, as well as form and report controls like textboxes and comboboxes, ultimately perform VBA-like FormatNumber operations. (I say "like", because I don't know whether it actually uses the VBA.Conversion.FormatNumber function to do it.) What this means for us is that these value-formatting options already use asymmetric arithmetic rounding. Coolness.

But be careful, because this type of value-formatting is not applied until values are shown. This means that by default, formulas used in queries, forms, and reports always operate on native non-formatted, and therefore non-rounded, values. When you combine this with the fact that Access may actually store values with precisions greater than expected (particularly, if like me, you avoid using input masks), you can get some unexpected results.

To illustrate this, let's create a new table with a field named key (make it an AutoNumber and mark it as the primary key), a field named hours (make it Currency, set Format to Fixed and Decimal Places to 2), and a field named rate (make it Currency, leave Format as Currency and set Decimal Places to 2). Save the table as Table1 and open it to add data. In the first row, enter an hours value of 1.325 and a rate value of 1.325 and then move off the row. Notice that the values you entered show only 2 decimal places, and that they are arithmetically rounded to 1.33 (banker's rounding would have returned 1.32). This is a good thing, and it works the same way in queries and on forms and reports.

Here's the rub. If you return to the first row's hours column and press F2, you'll see that Access has actually kept the value's full precision, 1.325. Access only rounds the value when formatting it for presentation. The deeper implication is that that when the column is referenced in a formula, whether in a query or on a form or report in a textbox or combobox, its full precision value of 1.325 will be used, not the nicely rounded value of 1.33. Maybe this is what you want, maybe it's not. Let's create a query and see. Start a new query, switch to SQL view, and copy in the following statement:

select key, hours, rate, hours*rate as total from table1

Now preview the query. The first thing to note are the value formats. Because we didn't explicitly set the formatting options for the columns in Design view, the rate and hours columns adopted the formats of their underlying table column definitions, and Access gave the calculated total column a 2 decimal Currency format by default, because its resulting value has a data type of Currency. No problem.

However, note the value of the calculated total column. It's 1.76. Where I come from, it should be 1.77, which is 1.33 * 1.33 = 1.7689 arithmetically rounded to 2 decimal places. Instead, Access gave us 1.325 * 1.325 = 1.7556 arithmetically rounded to 2 decimal places.

To get 1.77, we need to round the formula values ourselves. We can do this either when the values are created, assuming they come from a form or custom import routine where we can inject some programmatic logic, or we can do it directly in the formula. Either way, we will do it using the custom FormatNumber-based Round function described above. Assuming you've added that function to a module in the database, we can change the SQL to this:

select key, hours, rate, Round(hours, 2)*Round(rate, 2) as total from table1

Now when you preview the query, the total column should read 1.7689, which is correct, but a little raw. Because we didn't explicitly apply formatting to the column in Design view, Access determined it for us based on the value's data type, which in this case is Double. This results in a default Format of Fixed and a Decimal Places of Auto. You can rectify this a few different ways.

The first way is to switch to Design view, select the column, and use its Properties window to specify a Format of Currency and Decimal Places of 2. As described above, this will perform an aymmetric arithmetic rounding of the value for presentation, giving us the 1.77 we want. However, you will still have a column with a data type of Double containing values with extra hidden precision. Maybe this matters to you, maybe it doesn't. It depends on what the value will be used for downstream.

Another way is to convert the formula's resulting Double value to Currency by changing the formula to include a surrounding CCur, like so:

select key, hours, rate, CCur(Round(hours, 2)*Round(rate, 2)) as total from table1

This allows Access to once again recognize the resulting value as a Currency data type, and to apply a default Format of Currency with Decimal Places of 2. However, while the CCur gives us a column of data type Currency, we still have hidden precision in the column's values, and worst of all, we will get a banker's rounding of any precision exceeding the Currency data type's maximum 4 decimals, which kind of defeats the purpose.

By the way, if you ever want to see the extra precision, wrap the formula in a CStr, like so:

select key, hours, rate, CStr(CCur(Round(hours, 2)*Round(rate, 2))) as total from table1

The only way to avoid these potential problems is to round the result ourselves, like so:

select key, hours, rate, Round(Round(hours, 2)*Round(rate, 2), 2) as total from table1

This still results in a Double value, which may be okay, again it depends on what you're doing with it. If it's not okay, then wrap the formula in a CCur; assuming the outer Round specifies 4 or fewer decimal places, no banker's rounding will occur.

Mea Culpa

Now we come to the function that got me back here in the first place. In my earlier post on rounding, I described a flawed Round function replacement, which was not based on Format or FormatNumber. I then wasted time describing how in Access I discovered it was flawed, and how I attempted to fix it. The fix was a little-understood hack involving the application of a CDbl. It resolved a long list of incorrect roundings that I discovered with the first version. Unfortunately, it also introduced a bunch of new inaccuracies. Chris was kind enough to report some of these to me, and some quick tests (using a better number generator) clearly illustrated how far off the mark the supposedly fixed function was.

It seems that both of my earlier functions suffered from the inherent inaccuracies found in floating point numbers. Rather than try and explain the nature of floating point inaccuracies, I'll point you to Bruce M. Bush's still relevant 1996 article The Perils of Floating Point. There you'll find Mr. Bush explaining, with the help of some FORTRAN, the "inexactness" of base-2 (binary) floating point number representations, and learn the significance of "insignificant digits". In an admirably succinct way, Mr. Bush says:

"Perhaps someday the computer will keep track of the number of bits in a result that are truly significant. For now, it is still the responsibility of the programmer."

Unfortunately, in my earlier post, I didn't take that responsibility. As a result, my earlier Round replacement function suffers from some serious floating point "inexactness" and some unaccounted for "insignificant digits". More specifically, as Chris pointed out, it means the supposedly fixed function incorrectly rounds 37.535 to 2 decimals as 37.53, instead of 37.54, and 37.035 to 37.03, instead of 37.04. Oops.

It's interesting to see how this happens. When 37.535 is passed through the expression CDbl(Number * (10 ^ NumDigitsAfterDecimal)), where NumDigitsAfterDecimal is 2, it does not return 3753.5. It returns 3753.4999999999994543 (that's the "inexactness" part). This value is increased by .5, becoming 3753.9999999999994543. This is then run through Fix, leaving 3753. Finally, it is divided by 100 to become 37.53. Which is definitely not what we're looking for.

After much study, I now know that what my earlier functions needed was an Epsilon, a fudge factor to account for the inaccuracies found in floating point numbers. I also now know that I am not the right guy to figure out a reliable Epsilon. If I understand the problem correctly (and I'm sure I don't), floating point accuracy is measured on a sliding scale, so an accurate Epsilon fudge will vary with many things, like the type of CPU, mantissa size, integer value size, and the possibility of floating point operations producing values that are "not mathematically possible" (huh?). For all I know, solar flares may impact the thickness of my Epsilon. Clearly, this is a job for someone with a degree and probably something other than VB at their disposal.

Notwithstanding all that, I am still a moron, and so I can't help but take another crack at a pure floating point Round replacement function using a static Epsilon. Here's one for VBA and VB.

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

I used an Epsilon of .000000000001, because that's where I found floating point inaccuracies begin to vary within the approximately 16 digits of precision in VB Doubles when dealing with numbers having a maximum magnitude of .1. With that Epsilon and that magnitude, the function matched the FormatNumber-based function above to 14 digits of rounding over millions and millions of numbers (I've got a 2GB file full of them). However, if you increase the magnitude of the number a notch to 1, it starts mismatching at a rate of about 20% for 13-14 digits of rounding. Increase the magnitude again to 10, and it starts mismatching at that rate for 10-11 digits of rounding. Solving this requires the calculation of a dynamic Epsilon, and that's not happening here.

Given all the caveats and potential problems, both with the manipulation of floating point numbers and with my code, why would anyone dare use this function instead of the FormatNumber-based one above? I don't know. I suppose some folks might value this function's slightly better performance. In my testing, it's about 16% faster than the FormatNumber-based function. But when you're able to make +10K calls to these functions every second, that 16% requires a lot of calls to add up to any measurable advantage. Plus, this function clearly requires way too much attention in order to produce consistently accurate results. Personally, I'm sticking with the FormatNumber-based function.

Truncation as Rounding

To be complete, I should point out that there are some other techniques loosely described as rounding that always take numbers up or down, without consideration for their decimal values. In VBA and VBScript there are two functions that provide this type of truncation-like rounding, Int and Fix. Essentially, they chop off decimals leaving whole numbers. The only difference is in how they treat negatives. Int is symmetric, always going down (2.5 to 2, -2.5 to -3), and Fix is asymmetric, down when positive but up when negative (2.5 to 2, -2.5 to -2).

You might also be interested to know that the integer division operator "\" does not round, like you would get if you did a floating point division using "/" followed by a CInt. Instead, it performs the same asymmetric truncation as Fix.

Rounding in .NET

Rounding hasn't changed much with the advent of VB.NET. Microsoft dutifully re-implemented banker's rounding in the Round, CByte, CInt, CLng, and CCur functions. Note, though, that Round is now a shared method of the Math class in the System namespace (in other words, it is not a part of the Microsoft.VisualBasic namespace). This means that everyone relying on the .NET Framework's System.Math.Round function is now doing banker's rounding. Wonder if they know this?

Well, hold the phone. Almost everyone. I just noticed that JScript.NET's Math.round function is doing something different:

"If the decimal portion of number is 0.5 or greater, the return value is equal to the smallest integer greater than number. Otherwise, round returns the largest integer less than or equal to number."

In other words, arithmetic rounding. A quick test shows this to be true, and also that it is symmetric (-2.5 rounds to -2). I assume this is required for compatibility with the ECMA specification.

This is unfortunate, as I believe it adds unnecessary complexity to the whole of .NET. The implementation of similarly-named classes and functions with different behaviors in different .NET languages really blurs the line between framework and language-specific functionality and makes language-agnostic .NET development harder to accomplish.

I'm not saying that JScript should have broken with the ECMA spec (though I might argue that .NET should have broken with banker's rounding). What I am saying is that I would have rather seen the framework's System.Math class be given new overloaded Round methods with options for different rounding techniques and for the JScript language developers to have relied on these in their Math.round implementation. At least then we'd know where this basic system behavior was coming from and how it was all expected to work. Instead, the JScript.NET folks coded their own. So now in JScript.NET, Math.round(2.5) and System.Math.Round(2.5) return different results, and there's no System.Math.Round implementation that provides equivalent behavior. Yuck.

Anyway, back to VB.NET. Here's a code snippet illustrating VB.NET's parity with VB, VBA, and VBScript's rounding behavior (import the System.Math namespace to get the unqualified Round calls to work).

Console.WriteLine("{0} {1}",     CInt(1.5),            CInt(2.5))
Console.WriteLine("{0} {1}",     Round(1.5, 0),        Round(2.5, 0))
Console.WriteLine("{0} {1}",     Format(1.5, "0"),     Format(2.5, "0"))
Console.WriteLine("{0} {1}",     FormatNumber(1.5, 0), FormatNumber(2.5, 0))
Console.WriteLine("{0} {1}",     1.5.ToString("0"),    2.5.ToString("0"))
Console.WriteLine("{0:0} {1:0}", 1.5,                  2.5)

And here are the results.

2 2
2 2
2 3
2 3
2 3
2 3

The last two Console.WriteLine's illustrate a new formatting feature available in .NET. In case you don't already know, the System.Object.ToString method can be overridden in classes to return values that are meaningful for a particular type. In addition, as illustrated above, classes requiring more control over their output can implement IFormattable, whose ToString function can also take a string describing the format to use (i.e., the "0" above), and an optional object which implements IFormatProvider. Because System.Console.WriteLine also supports this feature, with slightly different syntax, the last two lines do the same thing:

- 2.5 is an instance of System.Double,
- System.Double implements IFormattable, and thus overrides ToString,
- "0" is passed as the format to 2.5's System.Double.ToString(String) method,
- this calls the System.Double.ToString(String, IFormatProviderMethod) method, passing Null for the IFormatProviderMethod,
- this calls the shared System.Number.FormatDouble method (whose contents we're not privy to) to apply the format, which in this case results in asymmetric arithmetic rounding.

It's no surprise that the VB.NET Format and FormatNumber functions also use asymmetric arithmetic rounding, as a quick check of their guts shows they rely heavily on the whole IFormattable infrastructure. Unfortunately, as I ranted above, this means we still have two different rounding behaviors in the same language (and within the .NET Framework). Too bad.

Enough Already

If you really want to read more about all this (you don't, right?), below are some links to some additional resources.

Microsoft Office XP Developer
Conversion, Rounding, and Truncation
This quote slays me: "Although the Round function is useful for returning a number with a specified number of decimal places, you cannot 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."

Microsoft Knowledge Base
HOWTO: Implement Custom Rounding Procedures (Article 196652)
The definitive Microsoft explanation of all things round.

Eric Lippert
Banker's Rounding
What could numeric rounding possibly have to do with MS-DOS?
Mr. Lippert's interesting backgrounders on rounding in VBScript and its (non)relation to VBA.


Comments

"If you want to write a rounding function that will round decimal values according to predictable rules, you should write your own."

Too funny!

chris | 2003.11.07 01:36 PM

Chris,

That quote kills me, too. In case you didn't notice, I inserted a whole new section on rounding Access values this afternoon that may interest you. Probably nothing you don't already know, but maybe it will spark a thought or two.

Thanks again for your input.

ewbi.develops | 2003.11.07 03:17 PM

Just stumbled onto a tip by Access guru Ken Getz in "Access VB * SQL Advisor Magazine", wherein he repackages and explains a function from a reader named Dejan Mladenovic that shows how to round numbers to multiples of some factor (like .25 or .125). Note that to accomplish this, they use a Variant Decimal variable, which is something I didn't write about, but should.

http://vbadvisor.com/doc/08884

ewbi.develops | 2003.11.10 09:20 AM

One of the best articles around on this subject. Thanks

Matt | 2004.03.17 04:22 AM

"What Every Computer Scientist Should Know About Floating-Point Arithmetic"

http://docs.sun.com/source/806-3568/ncg_goldberg.html

Thanks to Raymond Chen for the link.

ewbi.develops | 2004.05.25 09:54 AM

Thank you so much for these articles! I have never been a big comment poster, but I had to get this off my chest:

I've been struggling with rounding in VB for a long time, and although the answer I was looking for wasn't actually on these pages, it did help me to finally identify the cause my problems.

Although I vaguely rememberred from programming courses long ago, I never fully realized the impact that the use of floating point variables could have. Over the past years I've spent numerous days trying to get my own rounding function to work properly and as it was getting larger and larger I was beginning to think that there were some hidden bugs inthere I just wouldn't be able to fix. I don't think I have to explain what it does to a programmer's mind if you can't fix your own code, especially if it concerns something that seemed like a simple function at the beginning. (Actually ofcourse, in the beginning I assumed VB would have it's own usable rounding function....)

All this time my rounding function worked fine (even the original version of 3 lines of code), where the floating points were bugging me all along, for example:

a = 1.11
b = 2.22
c = 3.33

MsgBox "1: " & Format((a + b) - _
c, "0.0000000000000000000000")

So all my "if (a+b) = c"-troubles are explained. Now the soultion is just a matter of choise: changing a LOT of variables to datatype Currency in stead of Double, or rounding the intermediate (a+b).

Anyway, thanks again for your research. Sorry for the long post, but there's nobody here to celebrate my victory with me and I had to tell someone who would understand ;)

Ruben | 2004.08.04 04:09 PM

Hi Ruben,

Good for you - we can celebrate together. :)

It's amazing how many hits this post continues to get from Google and Yahoo searches. It seems a lot of folks continue to struggle with this same thing. Your comment rings true for a lot of folks: "I assumed VB would have its own usable rounding function..."

Thanks for the comments.

ewbi.develops | 2004.08.05 08:11 AM

Here's some info on the different ways money is rounded around the world:

http://www.xencraft.com/resources/multi-currency.html#rounding

ewbi.develops | 2004.08.09 09:05 PM

Your round algorithm fails for 20730546.6025, among others. I have a fix if you're interested.

Chris Fant | 2004.09.15 08:09 AM

Hi Chris,

Not sure to which of the round algorithms you are referring, or to what precision you tested. If you're talking about the floating point round routine, I'm not surprised. As I pointed out, developing an accurate floating point routine in VBA requires more effort than I can sustain, particularly when the Format-based approach does what I want. I only threw it in to frame the discussion (and because the original was even worse (and because I'm an idiot)).

Please feel free to share the details of your solution here, or post a link. A lot of people stop by and read this post every day, and I'm sure some would love to see a native solution.

ewbi.develops | 2004.09.15 09:36 AM

I found formatnumber quite slow so I tried this:

Function AInt(x As Double) As Long

Dim i As Long, m As Double: i = Int(x): m = x - i
If m < 0.5 Then AInt = i Else AInt = i + 1

End Function

It's not fancy but it seems to work for integer rounding and could be adapted for decimals. Any thoughts? (Am i missing an obvious reason why it's a bad solution?)

mark | 2004.09.17 05:14 PM

Hi Mark,

Your solution is a fast and simple way to do asymmetric non-bankers rounding to whole numbers. I mentioned Fix and Int in my old post above, but didn't go on to explain how they might be used to round to whole numbers as you've done. Thanks for sharing it.

One should note, though, that your logic behaves slightly different for negative numbers than VBA's round and Format/FormatNumber methods. Due to your explicit check for "< 0.5" without consideration for being negative, your routine rounds down on .5x instead of exactly .5 for negative numbers. Compare:

Round(-5.5, 0) = -6
FormatNumber(-5.5, 0) = -6
AInt(-5.5) = -5
AInt(-5.51) = -6

Also, I'm not sure I see how your routine could be "adapted for decimals" with introducing multiplication and division, which would put you squarley into the floating point camp, imprecisions and all. I would like to hear more of your thoughts on this.

Thanks for taking the time to write.

ewbi.develops | 2004.09.20 08:22 AM

Are you sure it’s called asymmetric – Google searches showed contradictory definitions. From your definition I’m not treating up as down for negatives, which I thought, you called symmetric. Others refer to symmetric to rounding away from 0 (because symmetry in geometry implies equal distance from a point, in this case 0, so –5.5 goes to –6 and 5.5 to 6). Anyway I don’t want to be bogged down in semantics.

I chose this particular system because it fits with what I was taught at school. At school I was taught to round to the nearest integer (hence -5.51 is closer to -6 than -5 which minimizes error) but for .5's, where there are two possibilities both of equal distance, choose the larger integer i.e. round up. To ignore the sign and "round up" to -6 just feels odd to me since round up implies choose a greater number. What does C do? The fact VB doesn't - well VB rounding sucks, I was particularly shocked to discover from your article how bad it is. I suppose the lesson is not to make assumptions - check the rounding implementation yourself, or though you might not catch bankers rounding without knowing about it in advance.

Your .5x comment confuses me because -0.51 is lower than -.5 so it should be rounded down to –1 under any reasonable scheme that isn’t rounding up or down for everything. The confusion with negatives often stems from the fact the number system is asymmetric in written form because -0.99...9 is not the next rational number less than 0, -0.0...01 is. However to me I always imagine number lines with 0 having an arbitrary place and the distance between points being what’s important. It’s the same for arrays – they don’t have to start at one or zero. In fact I do a lot of Maths and Physics where 0 is not specially located – it can be moved by a translation. Hence things like bankers, or rounding away from 0, which gives special preference to 0 bothers me because if I shift my origin by one all my evens become odd and vice versa and –1 to 0 now becomes 0 to 1 and the midpoint should be rounded differently – it all feels somewhat arbitrary and unnecessarily complicated.

What to do with .5's is always going to be arbitrary but I don't see how bankers avoids this - you could flip the .5 rounding direction around because bankers tends to reduce bias but only removes it completely for particular distribution (0 to 2 inclusive say). So rounding to odd is another valid alternative, which reduces bias and removes it for say 1 to 3 inclusive. To do something more complicated to implement or remember must have a good reason and I don’t feel bankers adds enough for the average person – you can’t guarantee bias removal under all distributions so let statisticians use an estimator appropriate to their needs. Is it called bankers rounding due to a persons name or due to banks? Because I suspect banks round up!

I accept your last point about imprecisions - I was thinking of using multiplication and division. Like you I don’t want to mess with assembly language or bit manipulation and that’s probably the only way to do something specific, quickly and accurately.

mark | 2004.09.25 03:36 PM

Excellent article. I will be passing this on to some analysis people in our department. Thanks for the good work.

Daniel Hemstreet | 2004.11.17 10:47 AM

You're welcome. Thanks for taking the time to comment.

ewbi.develops | 2004.11.20 08:35 PM

Maybe it was the re-install of my PC, maybe that latest service pack, who knows.... But the "Round = CDbl(FormatNumber(Number, NumDigitsAfterDecimal))" function for VB mentioned above does not work for me anymore. I'm pretty sure it did before, but today I noticed that passing Number=20.625 and NumDigitsAfterDecimal=2 returned the all too familiar 20.62.

I have made some modifications to the routine and would appreciate any comments. It involves "manually" replacing the 5 by a 6 if needed before the round. I have tested it over and over and it seems to work OK, both for positive and negative numbers. But I've thought "Eureka!" a few times before, so would love some feedback. Here it is:

Public Function Round(ByVal Number As Double, _
Optional ByVal NumDigitsAfterDecimal As Integer = 0) As Double

Dim strNumber As String
Dim intNumberOfDecimals As Integer

strNumber = CStr(Number)
If InStr(1, strNumber, ",") = 0 Then 'no decimal found in string,
'note that the comma is the decimal seperator in my country
GoTo PROC_ROUND_NORMALLY
End If
'decimal found, count the number of significant digits áfter the decimal seperator
intNumberOfDecimals = Len(strNumber) - InStr(1, strNumber, ",")
'only modify string if intNumberOfDecimals is exactly one higher than
'NumDigitsAfterDecimal and the final digit is a 5
If (Not intNumberOfDecimals = (NumDigitsAfterDecimal + 1)) Or _
(Not Right(strNumber, 1) = "5") Then
GoTo PROC_ROUND_NORMALLY
End If
'replace final digit with a 6
strNumber = Mid(strNumber, 1, (Len(strNumber) - 1)) & "6"
Round = CDbl(FormatNumber(CDbl(strNumber), NumDigitsAfterDecimal))
Exit Function

PROC_ROUND_NORMALLY:
Round = CDbl(FormatNumber(Number, NumDigitsAfterDecimal))

End Function

Thank you in advance for any comments.

Ruben | 2005.01.03 10:10 AM

Hi Ruben,

I sure hate floating point inaccuracies! I'm wondering how you came by the number 20.625? FormatNumber is correctly rounding that to 20.63 for me, so I was wondering if you typed it in or if it was the result of some mathematical expression? If it's the latter, it's probably being represented internally as something like 20.62499999999, which FormatNumber rounds to 20.62. If you're just typing it in, like in the Immediate window, then I don't know what's up!

As an alternative to your approach, which involves multiple type casts (to String, then back to Double), you might try just adding 1/(10 ^ (NumDigitsAfterDecimal + 2)) to the Number parameter before passing it to the FormatNumber function.

I would be interested to hear more about your system's configuration - OS, version, service packs, Office/version, etc. Also, what version of VB/VBA are you using?

Good luck!

ewbi.develops | 2005.01.03 11:07 AM

Thanks for the quick reply. Let me start by answering your last question. I'm running XP Pro with all the bells and whistles (SP2 and every other update that's out there) on a P4 with 512 MB's worth of RAM. My HD crashed about a month ago and I was forced to do a complete re-install. I'm not 100% sure, but I suspect this problem didn't occur until then. I probably would have noticed it at some point. I am absolutely sure that initially, this did not happen (hence my "celebration" post in August 2004).

VB is version 6.0 with SP6, and finally Office is Office 2003 with all available updates (not sure, but I think it involved a SP too). Although I'm not sure I understand why my version of Office is relevant, I should point out that this is the only thing significantly different about my system these days, as I used Office XP before the big crash.

20.625 is the result of calculation. A field of type Currency (!) holds a yearly rate and that gets split into a rate per quarter year for invoicing purposes. I thought Currency fields stop after the fourth decimal?

Anyway, same difference, because hard-coding 20.625 in the source gives the exact same (wrong) result. Guess that makes two of us that don't understand, and though not helping, that is comforting :) I suppose Office is a suspect, but even if that's to blame, the reasons why are way, way beyond me.

I rewrote the routine to (again) solve this headache once and for all. Do we agree that it does what it should do?

Other than performance related, what are your objections against multiple type casts? I personally always prefer to put some more code in modules that are being called all over the place. It makes the actual calls small and simple and reduces the chance of coding errors. I would like to avoid that extra addition every place this routine is called, any performance loss is a price I'm very happy to pay. As long as the routine itself is bug-free of course.

I wonder how many programs "out there" have this problem and people are unaware of it. If Office 2003 is to blame, the thought of maybe having discovered a brand new "MS-bug"... I always told my mother that one day, I would make her proud :)

Once again thank you for your input, it's greatly appreciated.


Ruben | 2005.01.04 11:26 AM

You and I are running mostly the same thing, though with at least one major difference - I'm not yet running XP SP2. This means that you are likely running MSVBVM60.DLL version 6.0.96.90, while I'm still on 6.0.93.30. Don't know whether this might be related to the difference you're reporting.

For the record, start VB 6.0, start a new EXE project, press Ctrl-G to open the Immediate window, paste the following and press Enter:

?FormatNumber(CDbl(CCur(20.625)), 2)

And then do the same thing in Office 2003. Open Word or Excel, start a new document or workbook, press Alt-F11, Ctrl-G, paste and Enter.

In both cases I get 20.63. What do you get?

Performance is part of my concern regarding the type casts, though it's not just for the cost of the casts, it's the subsequent multiple Instr() calls and compound conditional expression. One might also argue that, faster or not, the consistent addition of a simple fudge factor is clearer, as it could be added to the original routine without adding a single line of code.

Being pragmatic, though, I'm not sure I care enough to put up a fight one way or the other. In the end, what's important is whether it works. :)

ewbi.develops | 2005.01.04 12:33 PM

You lost me when you mentioned the fudge factor, no idea what you mean by that, but not unlike yourself I now have a "don't care how it works, as long as it works"-attitude towards this whole issue.

If by fudge factor you mean the "1/(10 ^ (NumDigitsAfterDecimal + 2))"-trick you mentioned, I agree it is defenitely clearer and I will likely adjust the function accordingly. Changing the 5 in a 6 using a string was simply the quickest (so by default also the dirtiest) way I could think of (I am far from a VB expert).

Not surprisingly the immediate window returned 20.62 in VB, Word and also Excel. MSVBVM60.DLL is indeed version 6.0.97.85. Just for the fun of it, I unregisterred it and replaced it with the version closest to yours but older than yours I could find in my archives, which was 6.0.92.37. Re-reg and reboot, but (to my surprise, I guess) both VB and Office still returned 20.62.

I give up. The new routine does the trick, so let's leave it at that. If you or anyone else ever comes up with the cause of all this I'll be very interested. Untill then, I advise you to give the FormatNumber functions an extra test after each of your future MS-upgrades, wahetever they are.

Thank you for your time and effort.

Regards,

Ruben

P.S. Obviously when using the "1/(10 ^ (NumDigitsAfterDecimal + 2))"-trick on negative numbers, one should subtract in stead of add. Just to be precise, should anyone else encounter similar issues.

Ruben | 2005.01.04 02:19 PM

Yes, that's what I meant by "fudge factor", sorry I wasn't clearer. And thanks for pointing out the issue with negatives. That kind of precludes making it a one-liner. :(

So, Ruben, I am now officially freaked out. I can't believe you and I are getting different VBA.Strings.FormatNumber results for an explicitly specified value.

I realize you'd probably like to move on, but, in so much as I can't reproduce your results, if you would be willing, I'd love to explore this a little more. Specifically, I'm interested in two things.

1) The results of your running the following commands in the VB and Word/Excel VBA Immediate windows:

?FormatNumber(CCur(20.625), 2)
?FormatNumber(CDbl(20.625), 2)
?CDbl(20.625) = CCur(20.625)
?CDbl(20.625) = 20.625
?CCur(20.625) = 20.625

I get:

20.63
20.63
True
True
True

2) And, just to be complete, create a new VBS file, paste the following into it, save it, and then double-click it to run it:

'-------------
wscript.echo "1: " & FormatNumber(CDbl(CCur(20.625)), 2)
wscript.echo "2: " & FormatNumber(CCur(20.625), 2)
wscript.echo "3: " & FormatNumber(CDbl(20.625), 2)
wscript.echo "4: " & (CDbl(20.625) = CCur(20.625))
wscript.echo "5: " & (CDbl(20.625) = 20.625)
wscript.echo "6: " & (CCur(20.625) = 20.625)
'-------------

I get this:

1: 20.63
2: 20.63
3: 20.63
4: True
5: True
6: True

Thanks again for bringing this to everyone's attention.

ewbi.develops | 2005.01.04 03:12 PM

The main reason I wanted to move on was beacase I felt I had taken enough of your time. That, plus I had a feeling it was "just me" and this very helpful page was getting clutterred with all my comments. Ofcourse I'm verry happy to do some more investigation. After all, when it comes to rounding, I owe you. A lot.

Let me first point out for the record that my original MSVBVM60.DLL (version 6.0.97.85) is back in place. It didn't seem to be the cause of things and I didn't want to mess things up any further.

On with the testing. I inserted an extra "?FormatNumber(CDbl(CCur(20.625)), 2)" at the beginning of the first test so we have the same set of six statements for each test.

Place your bets people, here are the results of the Curaçao jury:

Office 2003 and VB / immediate window:
1: 20.62
2: 20.62
3: 20.62
4: true
5: true
6: true

VBScript:
1: 20.63 (!!!)
2: 20.63 (!!!)
3: 20.63 (!!!)
4: true
5: true
6: true

Don't know about you, but I just lost a bet :) The Office and VB test basically performed as expected when you consider my earlier problems: CDbl and CCur work fine, and even though you and I probably feel FormatNumber isn't, at least it's doing so consistantly.

The VBScript test (that you only added "just to be complete") blows my mind. Looks like our friends from Microsoft read this page, figured that having a single function that works THE SAME in VB and VBScript was too good to be true. So they decided to fix that. Quietly.

I made absolutely sure there's only one version of MSVBVM60.DLL registerred. As far as I'm aware, FormatNumber for both VB and VBScript is derived from that same file. Anyway, the older version did the same thing, assuming that the VBScript results would be the same as above (we didn't test that). I'm clueless...

Any thoughts? More tests? The number of a good psychiatrist? Let me know how I can be of help.

Ruben | 2005.01.05 04:34 AM

Don't worry about the comments getting cluttered - this stuff adds value. Plus, I like a good mystery. ;)

Those are interesting results. VB and VBScript use separate FormatNumber implementations, so that second test was meant to isolate VB.

If you don't mind, here's one more set to run from the Immediate window in VB only:

?Format(20.625, "#.##")
?Format(20.625, "#.###")
?FormatCurrency(20.625, 2)
?FormatCurrency(20.625, 3)

Once you've run these, I'm going to share all of this with some folks and see what they think.

Hang in there!

ewbi.develops | 2005.01.05 08:34 AM

20.625 is turning out to be a number I will probably remember for the rest of my life :)

OK, here we go:
20.62
20.625
€ 20,62
€ 20,625

So no big surprises there, it's pretty consistent. Not sure what will happen to them when I click 'Post', but the last two results have a euro symbol (my default currency) in front of them.

Good luck, hope your 'folks' can shed some light on this.

Ruben | 2005.01.05 08:57 AM

I'm generating some interest. In the meantime, if you don't mind, let's move around the number some in the VB Immediate window:

?FormatNumber(20.62499999999999, 1)
?FormatNumber(20.62500000000001, 1)

?FormatNumber(6.15, 1)
?FormatNumber(6.25, 1)
?FormatNumber(6.35, 1)
?FormatNumber(6.45, 1)

?FormatNumber(.615, 2)
?FormatNumber(.625, 2)
?FormatNumber(.635, 2)
?FormatNumber(.645, 2)

?FormatNumber(1.615, 2)
?FormatNumber(1.625, 2)
?FormatNumber(1.635, 2)
?FormatNumber(1.645, 2)

?FormatNumber(10.615, 2)
?FormatNumber(10.625, 2)
?FormatNumber(10.635, 2)
?FormatNumber(10.645, 2)

Thanks.

ewbi.develops | 2005.01.05 09:31 AM

Here goes:

20.6
20.6

6.2
6.2
6.4
6.4

0.62
0.62
0.64
0.64

1.62
1.62
1.64
1.64

10.62
10.62
10.64
10.64

As you can see no surprises there. Am I right to assume you made a small error in the first two statements and ment to ask for the following?

?FormatNumber(20.62499999999999, 2)
?FormatNumber(20.62500000000001, 2)

If so, here's those results:

20.62
20.62

Before I posted this issue I had of course done a lot of testing myself. You can pretty much take my word for it: on my system FormatNumber in VB does classic banker's rounding. As troublesome as that is, that is the way it is I'm afraid.

Let me know if you need more data.

Ruben | 2005.01.05 10:49 AM

Yes, I meant to indicate 2 decimal places for the first two FormatNumber tests, thanks.

Well, it certainly looks like banker's rounding. I didn't even consider suggesting tests to confirm this earlier, as it never crossed my mind that these routines, which have *always* done traditional arithmetic rounding, might have changed to bankers. I figured you must be experiencing some sort of floating point inaccuracy, or something. I still can't believe it. I suppose, though, if it is true, it resolves the inconsistency I ranted about above in my post. But, again if true, I'm apt to rant even more about being subjected to this (undocumented?) change this late in life.

I'll keep digging for an explanation. Thanks again for taking the time to share this.

ewbi.develops | 2005.01.05 12:05 PM

No problem. It's defenitely hard te believe. If it will make you feel better I'll be happy to send you some screen shots :)

As fas as 'undocumented' goes, I spent a good deal of time looking for any information about this appearant functionallity change. Found nothing at all.

This is actually a scary thought. Like I mentioned before, there's got to be more people out there with the same problem, if only people who visited this page earlier and, like myself, found your routine useful.

The fact the that there's nothing on the web about this leaves two option in my opinion: either my system configuration is some bizarre combination that would cause this (not likely, especially since the fresh install) or other people have this same issue and are not aware of it.

Either way, good luck. I'll be checking in to see what you came up with. Feel free to e-mail me if you need more test data.

Ruben | 2005.01.05 12:26 PM

Ruben,

I'm talking to some MS folks and they would like to know the version of oleaut32.dll on your machine. Interestingly, this is where the Format, FormatNumber, and FormatCurrency routines are actually implemented. And, apparently, there was a version of oleaut32 that shipped in Windows XP that used banker's rounding. However, the change got backed out when an updated oleaut32 was shipped with Windows XP SP1, so they really want to know which version you've got (and, of course, I do too!).

Thanks.

ewbi.develops | 2005.01.13 10:54 AM

oleaut32.dll is version 3.50.5014.0 on my machine. Like I said before, I'm running XP with SP2 so if what they tell you is true then it should have been replaced with the newer version, right? Unless some other update or installation upgraded it even further and that new version is back to banker's rounding (just guessing here).

I've tried to find a download of a newer version than mine to confirm that this is indeed causing this behaviour but no luck. I could only find older versions so I cannot give it a test. If you have one lying around please send it and I will see if it indeed makes a difference.

Good luck again!

Ruben | 2005.01.14 12:51 PM

Hi Ruben,

I knew something interesting would come of all this.

Thanks to some much appreciated help from Paul Vick [1], Technical Lead on Visual Basic .NET, who I was directed to by the always helpful Eric Lippert [2], we now know these things:

1) The VBA.Strings.FormatNumber function is actually implemented in OLEAUT32.dll.

2) OLEAUT32.dll version 3.50.5014.0, which shipped with Windows XP, incorrectly used banker's rounding in this routine. (Arrgh!)

3) The incorrect banker's rounding in this routine was "backed out" with OLEAUT32.dll version 3.50.5016.0, which shipped with Windows XP SP1. (Yeah!)

4) The version of OLEAUT32.dll shipped with Windows XP SP2 was 5.1.2600.2180.

So, if you're supposed to be running Windows XP SP2, but you've got OLEAUT32.dll version 3.50.5014.0, then you've got a bad Windows configuration. The only suggestion made was that you reinstall Windows XP SP2 and see if that gets you the correct file.

So how interesting was all that?

Ruben, thanks for hanging in there. Please let me know how it turns out for you.


[1] http://www.panopticoncentral.net/
[2] http://blogs.msdn.com/ericlippert/
also see: http://support.microsoft.com/?kbid=321047

ewbi.develops | 2005.01.14 05:18 PM

Well, how about that. Finally, it looks like we can put this to rest (for good?).

I'm not too excited about reinstalling SP2. It took me a goog deal of time to get my system up and running again and everything is working like a charm now. Since the modified Round routine solved my porblems, I don't think I'm going to take any chances. I'm sure you understand. Even if my Windows configuration is bad (which it obviously has to be), it works for me.

However, I'll be visiting a lot of clients next week who all have SP2 so I should be able to get an updated version of OLEAUT32.dll off one of their machines. I will update just that single file and will give you a final confirmation that that was indeed the problem somewhere next week.

Untill then, thanks for all the time you put in this. I'm sure glad we got some sort of closure!

Ruben | 2005.01.15 06:00 AM

I have the SP2 version of OLEAUT32.DLL (5.1.2600.2180) and I am getting the banker's round when using Math.Round.

Here is the workaround I am using:

decimal salesTax = 0.825m;
String salesTaxStr = salesTax.ToString("0.00");
decimal rounded = Convert.ToDecimal(salesTaxStr);
Assert.IsTrue(
rounded.Equals(0.83m),
rounded.ToString() + " is not equal to 0.83"); // NUnit test case.

salesTax = 100.825m;
salesTaxStr = salesTax.ToString("0.00");
rounded = Convert.ToDecimal(salesTaxStr);
Assert.IsTrue(
rounded.Equals(100.83m),
rounded.ToString() + " is not equal to 100.83"); // NUnit test case.

salesTax = -5.825m;
salesTaxStr = salesTax.ToString("0.00");
rounded = Convert.ToDecimal(salesTaxStr);
Assert.IsTrue(
rounded.Equals(-5.83m),
rounded.ToString() + " is not equal to -5.83"); // NUnit test case.

I only have to round in a couple of places so a minor performance hit isn't a major concern to me. Mayhaps the fact that I have the correct dll is what is causing the format string to work correctly.

Joe Herr | 2005.02.05 01:20 PM

Hi Joe,

Thanks for the comment.

I think I should have been a little clearer. OLEAUT32.dll only affects the rounding performed by the VBA.Strings.FormatNumber method. It has no relation to the rounding performed by .NET's Math.Round method or the rounding performed by overrides of the Object.ToString method implemented by various types. The .NET version 1.0/1.1 Math.Round method always does banker's rounding (JScript's Math.round is an exception). The IFormattable-based ToString overrides of various numeric types always use arithmetic rounding.

So, yes, your workaround, which relies on the decimal type's overridden ToString method, will get you arithmetic rounding instead of banker's rounding.

A general purpose override of .NET's Math.Round for decimals might look like this (sorry, but I can't show indentation here):

public static decimal Round(decimal value, int digits) {
StringBuilder mask = new StringBuilder("0.", 2 + digits);
for (int i = 1; i <= digits; i++) { mask.Append("#"); }
return Decimal.Parse(value.ToString(mask.ToString()));
}

Swap "decimal" with "double" to target the double data type.

Hope that clears it up.

ewbi.develops | 2005.02.08 11:15 PM

I just wanted to chime in that I have encountered a need in my Excel based VBA programming for asymetric arithmetic rounding and have (atleast for the time being) found a somewhat usable solution in replacing my vba round function calls with Application.round() I believe that this uses the round function built into excel and it seems to work just fine for me.

Joseph | 2005.04.28 12:43 PM

Good tip, Joseph. I hadn't mentioned that Excel uses asym math rounding and it's easily accessible from VBA via the WorksheetFunction (or Application) objects:

Application.Round(2.05, 1)
WorksheetFunction.Round(2.05, 1)
Application.WorksheetFunction.Round(2.05, 1)

Thanks for taking the time to write!

ewbi.develops | 2005.04.29 10:33 AM

We made following Function for normal math.round function.
Function MyRound ( n)
Dim i
i = CLng (n)
if ((Clng(n * 10) - i * 10) >= 5) Then
MyRound = i + 1
Else
MyRound = i
End If
End Function

jaweed Sarfraz | 2005.06.07 09:57 AM

Hi Jaweed,

Thanks for taking the time to post your function. I've seen similar logic used for whole number rounding. However, care should be taken with this logic as CLng does use banker's rounding and may therefore return unexpected results, particularly if there are multiple fractional digits. It's really kind of confusing, as MyRound works in some cases almost by mistake. ;)

For example, take 2.5. CLng banker rounds that that to 2. In MyRound that means it evaluates 25-20, which is 5, and so 2 will have 1 added to it. Compare that to 1.5. CLng banker rounds that to 2. In MyRound that means it evaluates 15-20, which is not >= 5, and so 2 does not have 1 added to it. While that works, it strikes me as working somewhat by accident (at least given the code's apparent purpose).

For an example of a multi-digit fraction issue, consider 1.455. CLng banker rounds that to 1, and it banker rounds 14.55 to 15. This means that MyRound evaluates 15-10, which is 5, and so 1 has 1 added to it, resulting in 2. However, many/most rounding routines, like my Round replacement above (based on FormatNumber) and even Math.Round, don't roll-up the insignificant digits like this when rounding, and therefore return 1 for 1.455.

Anyhow, thanks again for taking the time to write.

ewbi.develops | 2005.06.17 05:51 PM

Hi,

I have stumbled across your webpage in search of an answer to the rouding problem.

I am importing data from excel to sql server. I am trying to write a view or Stored Procedure to total the lines the same way that Excel does - rounding up.

Unfortunately, the FormatNumber function does not work in sql 2000. Do you know how to do this in sql please???

btw I learned a great deal from the above. Perhaps Microsoft should read it!!

Roger Stephens | 2005.06.20 11:09 AM

Hi Roger,

SQL Server T-SQL includes a Round function. It behaves just like Excel's Round function, which behaves just like VBA's FormatNumber function. They all do asymmetric arithmetic rounding.

Interestingly, the T-SQL and Excel Round functions also support negative precision parameters allowing you to round into ten's, hundred's, thousand's, etc., whereas VBA's FormatNumber function does not. For instance, in T-SQL or Excel you can write this:

ROUND(123456.5, -3)

And get back this:

123000

Hope this answers your question. Good luck!

ewbi.develops | 2005.06.20 12:07 PM

Thanks for the quick reply.

I tried the Round function in a sql view and this is what I got: Round(175.885,2) = 175.88!!

I worked out a way to do it that seems to work:
CAST(CAST(Amount_Pre_Rounded AS char(20)) AS money) AS Amount

The implicit conversion seems to do the trick.

Thanks again.

Roger Stephens | 2005.06.20 01:07 PM

Interesting. If you don't mind me asking, what was the data type, precision, and scale of the column you applied the Round to in your SQL view?

Also, what do you get just executing the following SQL statement in Query Analyzer:

select Round(175.885,2)

I get 175.890.

Thanks for sharing your results.

ewbi.develops | 2005.06.20 01:36 PM

Hi again,

The data that I am rounding comes from a calculation. The column is float type.

I performed the test in Query Analyzer and I got the same result as you 175.8

Interesting!

Roger Stephens | 2005.06.21 12:05 PM

oops!! that should be 175.89 :-)

Roger Stephens | 2005.06.21 12:06 PM

Hi Roger,

Did I mention I hate floating point numbers? Here's an illustration of the issue you've described:

select round(175.885, 2), round(cast(175.885 as float), 2), round(cast(175.885 as decimal(10,3)), 2)

This will get you:

175.890 175.88 175.890

Change the float value to 175.886 and you'll get 175.88999999999999.

I think this may be related to floating point inaccuracies described by Pro Pete in the following SQL Server Magazine forum:

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=80&threadid=41618&enterthread=y

"This is a limitation of trying to use binary fractions to represent decimal fractions. Binary fractions (as used in floating point) can only accurately hold a value that can be represented by combining negative powers of 2, such as 0.5, 0.25, 0.125, 0.625 (= 0.5 + 0.125), etc. "

I suppose, if possible, one ought to consider using the decimal data type.

Thanks for taking the time to point this out. Good luck!

ewbi.develops | 2005.06.21 09:47 PM

Make note of when you export from Query Analyzer's copy function which saves as a csv file. A value of 5.1 will export as 5.0999999999999996 and when re-imported to SQL Server it reads as 5.1. If you note, there are 16 decimal spaces here. If you always add .0000000000000004 to any VB value when you round, it should always round to the correct value.
?round(1.5+.0000000000000004 ),round
?(2.5+.0000000000000004 ),round(2.5),round(1.5)
2 3 2 2

This is similar to what is said above, but I think it is also simpler to remember. Case obviously is different with a 4 byte number than a 2 byte number.

Regards, Jamie

Jamie | 2005.10.01 12:07 PM

I felt the need to test the theory.
Public Const RoundCorrection = 4E-16
Public Function mRnd(ByRef dbl As Double, Optional nSignifDigits As Integer)
dbl = dbl + RoundCorrection
If nSignifDigits = 0 Then
mRnd = Round(dbl)
Else
mRnd = Round(dbl, nSignifDigits)
End If
End Function
'?mRnd(2.000005,5),mRnd(2.5),mRnd(1.5)

Jamie | 2005.10.01 12:16 PM

Hi Jamie,

Thanks for the note. When I get a second I'll check it out, but that makes sense, and is easier to remember. ;)

Also, thanks for the note regarding the SQL Query Analyzer export - I'll have to look again, but I think a similar issue affects Access' export.

ewbi.develops | 2005.10.04 07:48 PM

Thanks, this is an excellent post. I was pulling my hair out trying to figure out why the Round function on an Excel spreadsheet would round 2.135 to 2.14 yet in my Access module it was rounding the same number to 2.13.

Oh and yes, I did find this post through Google.

David | 2005.10.18 08:20 AM

Hi David - Thanks for the comment. Glad it helped!

ewbi.develops | 2005.11.08 03:45 PM

and this is why the internet rules...
my co-worker sent me this blog because he ran into the same issue and we were discussing a workaround. T-sql round() function saved us, however in true internet google/blog fashion he searched and found this blog which he emailed to me and I am now reading this evening while listening to Howard on Sirius. Thanks for posting and with humor...

"What I want, and a lot of folks who go searching on Google and find this blog apparently want, is what's called arithmetic rounding..."

""Huh?! Didn't you just say that VBA and VBScript use banker's rounding? What are you, a moron?" Yes I did, and yes I am.
...

classic

I'm fwding to the rest in the IS dept and maybe to the Finance director who thinks he's a techie.

Chuck | 2006.01.09 07:15 PM

Hi Chuck,

Thanks for braving the TypePad craptcha prompt. To tell the truth, it's kind of hard for me to return to this old post and read through it, as it reflects a kind of flippant too-much-time-on-my-hands way of writing that I rarely exhibit anymore (though I remain a moron). Still, I'm glad it does occasionally fit the bill for someone. Personally, I've found the comments to be more interesting than the post itself.

Oh, and I'm stuck with my damn XM Radio!

ewbi.develops | 2006.01.09 10:10 PM

Great article, thank you. One minor bug though, in 'Truncation as Rounding' you have:

- Int is symmetric, always going down (2.5 to 2, -2.5 to -3), and Fix is asymmetric, down when positive but up when negative (2.5 to 2, -2.5 to -2).

In 'Rounding in .NET' you have:

- A quick test shows this to be true, and also that it is symmetric (-2.5 rounds to -2).


The last one should either be 'asymmetric' or '-2.5 rounds to -3', not sure which!

Ian | 2006.04.27 01:02 AM

Good catch Ian. Thanks for letting me know. I haven't touched this thing since it was posted back in 2003, so I suppose I should also add some updates regarding the new .NET Math.Round overloads. Thanks again.

ewbi.develops | 2006.04.28 06:28 AM

The easiest thing I found to do was this:

Decimal.Parse(TotalOrder.Total.ToString("C"), System.Globalization.NumberStyles.Currency)

Jason | 2006.05.30 11:48 AM

Jason - yep, that's a nice quick way to take advantage of the arithmetic rounding found in .NET's System.Number.FormatDouble method. It's unfortunate that, like with the use of VB/VBA's Format function, it requires a transformation to and from a string.

ewbi.develops | 2006.05.30 01:04 PM

You have written an excellent article hilighting one of the dark recesses of VB.

Another fun one is using aggregates like avg on data from decimal(18,2) sources. When you look at the non-aggregated data from a formula in a query it has full precision say 41.6667%but when you go into aggregate and use avg it it turns into 41%. My guess is that it is truncating the data to 2 decimals since the source data is limited to 2 decimals. Since the percent form is stored as a fraction you only get 2 digits. The work around is to use cdbl(TheNumber) before using it in the aggregate.

Why it would show the number in the non-aggregate with more precision I cannot say.

Ralph | 2006.06.07 12:37 PM

Ralph - Thanks for the comment and the tip.

ewbi.develops | 2006.06.07 12:50 PM

Nice Job Roger

srini | 2006.06.21 04:22 AM

Best explanation of dealing with floating point inaccuracies I've ever read, "Comparing floating point numbers" by Bruce Dawson:

http://www.cygnus-software.com/papers/comparingfloats/Comparing%20floating%20point%20numbers.htm

Includes samples and explanations of comparing floating point numbers with epsilon, and as integers:

"The IEEE float and double formats were designed so that the numbers are 'lexicographically ordered', which – in the words of IEEE architect William Kahan means 'if two floating-point numbers in the same format are ordered ( say x < y ), then they are ordered the same way when their bits are reinterpreted as Sign-Magnitude integers.' This means that if we take two floats in memory, interpret their bit pattern as integers, and compare them, we can tell which is larger, without doing a floating point comparison."

Good stuff.

ewbi.develops | 2006.08.15 10:44 AM

Thanks for the great post

Chris | 2006.09.03 06:42 AM

Hi All,

Here is the VBA code that work for RoundUp. For example, RoundUp(1.4) or RoundUp(1.967,2) which it will always round it up. Hope it would help.


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

Wasan Aphisitphinyo | 2006.09.18 01:33 AM

Thanks for sharing that, Wasan.

ewbi.develops | 2006.09.20 12:01 AM

Function roundup(mynumber, roundto)
roundup = (Int(mynumber / roundto) + 1) * roundto
End Function

Test routine:
Sub testroundup()
testnumber = 1234.5678
For i = -3 To 3 Step 1
Select Case i
Case Is > 0
padleft = String(-i + 3, " ")
padright = " "
Case Is < 0
padleft = " "
padright = String(i + 3, " ")
Case Is = 0
padleft = " "
padright = " "
End Select
Debug.Print "roundup(" & testnumber & ", " & padleft & 10 ^ i & padright & ") = " & roundup(testnumber, 10 ^ i)
Next
End Sub

Output:
testroundup
roundup(1234.5678, 0.001) = 1234.568
roundup(1234.5678, 0.01 ) = 1234.57
roundup(1234.5678, 0.1 ) = 1234.6
roundup(1234.5678, 1 ) = 1235
roundup(1234.5678, 10 ) = 1240
roundup(1234.5678, 100 ) = 1300
roundup(1234.5678, 1000 ) = 2000

Anon | 2007.01.06 05:28 PM

Added an exception if the number does not need to be rounded. The previous post is still valid in cases where you still need to always round up (i.e. minimum $ charge)

Function roundup2(mynumber, roundto)
If mynumber / roundto = Int(mynumber / roundto) Then
addvalue = 0
Else
addvalue = 1
End If
roundup2 = (Int(mynumber / roundto) + addvalue) * roundto
End Function

roundup2(1234, 1) = 1234
roundup2(1234.1, 1) = 1235

This can also be used to round up to the next highest number evenly divisible:
roundup2(1234, 97) = 1261
1261 / 97 = 13

Anon | 2007.01.06 05:56 PM

Anon, thanks for sharing your code. I'm sure someone will find this function helpful. I particularly like the consistency with handling negative numbers (achieved with the symmetric (always down) Int combined with a +1). Thanks.

ewbi.develops | 2007.01.08 09:52 AM

Rounding always makes for an interesting WTF:

http://thedailywtf.com/Articles/Round_and_Round.aspx

Check out the comments. Lots of interesting explanations, lots of bad explanations, lots of misunderstandings, lots of confusion. Nearly a decade into the 21st century and it's still hard to convert 37.535 to 37.54. Sad.

ewbi.develops | 2007.01.20 08:49 AM

hi all i want to change 24 to 24.00 what function i use in vbscript.i want number upto two decimal

other eg. .1 to .10

rahul | 2007.03.07 02:00 AM

rahul, take a look at Format and FormatNumber. The former accepts a format mask (e.g., "#.00"), the latter an explicit parameter dictating the number of post-decimal places. Here's a quick sample from the Immediate window:

?FormatNumber(24, 2)
24.00
?Format(24, "#.00")
24.00


Good luck.

ewbi.develops | 2007.03.07 06:58 AM

hi it is correct but i want to round upto two decimal

like .591 -.59
5.205-5.21

i tried round function also but not getting solution to my problem

rahul | 2007.03.08 05:02 AM

rahul, I'm afraid you need to be more specific about what you are *not getting*, because FormatNumber and Format keep giving me what you seem to be asking for:

?FormatNumber(.591, 2)
0.59
?FormatNumber(5.205, 2)
5.21

ewbi.develops | 2007.03.08 08:39 AM

i do not want *commas* in between digits

formatnumber is working correctly no doubt

but what when i have FormatNumber(123456.205,2)

it will return 123,456.21

rahul | 2007.03.08 11:19 PM

rahul, the last parameter to FormatNumber is named GroupDigits:

"[I]ndicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. See Settings section for values."

So, try this (I'm using 0 for False because I don't know if you're using VBA or scripting):

?FormatNumber(123456.205, 2, , , 0)
123456.21

Good luck.

ewbi.develops | 2007.03.08 11:55 PM

Thanks My problem is solved

rahul | 2007.03.09 12:40 AM

This was very helpful, thanks.

Scott. | 2007.06.15 07:38 AM

VBA function below should work according with arithmetic rules for both positive and negative numbers:
Public Function MyRound(mNum As Double, Optional numdecimalplaces As Integer)
Dim nD As Double
nD = 10 ^ Nz(numdecimalplaces, 0)

MyRound = Int(mNum* nD + 0.5) / nD

End Function

Oleg | 2007.11.12 05:03 PM

Hi Oleg,

Thanks for the code. However, as described in this post and the one that inspired it, your code is not going to work for a large number of values. For instance, your code rounds 1.335 to 1.33 instead of 1.34.

Also, the Nz() function is only native to Access, not VBA or VB in general, and in any case isn't required in this instance because an Optional parameter of type Integer will always default to 0 if not specified.

ewbi.develops | 2007.11.12 09:23 PM

As you've mentioned in your article, it could be fixed with static Epsilon:
Public Function MyRound(mNum As Double, Optional ByVal numdecimalplaces As Integer = 0) As Double
Dim nD As Double

nD = 10 ^ numdecimalplaces

MyRound = Int(mNum * nD + 0.500000000001) / nD

End Function

But function could be even simpler:

Public Function MyRound(mNum As Double, Optional ByVal numdecimalplaces As Integer = 0) As Double

MyRound = Round(mNum + 0.000000000001, numdecimalplaces)

End Function

The only difference - numdecimalplaces could not be negative (becouse of the neture of the Round function)

Oleg | 2007.11.13 07:49 AM

I feel your pain...
I had to be the first to Reply for 2008 :)
Anyway, this is great article thanks for sharing and for what it's worth, iN .Net I use the SqlDecimal.Round Method to get 4/5 rounding results.

JPB | 2008.03.15 08:47 AM

JPB,

Thanks for stopping by and dropping a comment. Hard to believe it's been nearly 5 years since I wrote this thing. I'm afraid it's not aging well, at least for me - it gets a cringe or two from me every time I read it.

SqlDecimal.Round is an interesting suggestion. Of course, we've now got some Math.Round overloads that make this easier, but the SqlDecimal option is really interesting because the effort they go through to preserve the precision of the Double and then round it on 5 is right there to look at with Reflector. Makes for some really interesting reading.

Thanks!

ewbi.develops | 2008.03.17 10:45 AM

Hey, thanks for posting this. I happened to just stumble upon this "error." You saved me a lot of headache of research.

David M | 2008.11.05 07:15 AM

Thanks for a good article. These discussions might be too detailed for some but not for everybody!

I been working with rounding issues all day and a basic random function like the one below!

Well thanks again!

http://vbaexcel.eu/vba-macro-code/rnd-random-function

Edvard | 2009.02.25 10:20 AM

The solution is simple. The following code uses excels arithmetic function instead of the vb round function:

WorksheetFunction.Round(number, precision)

Brandon | 2009.07.17 10:34 PM

Very useful and interesting discussion, thanks for that.

Mark | 2010.01.25 12:28 AM

Thank you for this!! I've been trying to truncate numbers in ArcMap using VBScript. (Truncate doesn't work in ArcMap). Discovered accidentally that Round messed up some numbers. I just replaced 0.500000000001 with 0.000000000001 to truncate rather than round. Thanks again. PS - 2011 and STILL Microsoft can't provide a useful round function?)

Laura | 2012.07.11 03:12 PM

Even in 2012! :) Glad this was some help.

ewbi.develops | 2012.07.11 03:33 PM



Post a Comment

 
  (optional)
  (no html)
 
   


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83421d14653ef

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