A number of people have pointed out to me over the years that VBScript's Round function is a bit weird. It seems like it should be pretty straightforward -- you pick the integer closest to the number you've got, end of story. But what about, say, 1.5? There are **two** closest integers. Do you go up or down?

The Round function goes to the nearest integer, and *if there are two nearest integers then it goes to the even one.* 1.5 rounds to 2, 0.5 rounds to 0.

Why's that? Why not just arbitrarily say that we always round down in this situation? Why round down sometimes and up some other times? There actually is a good reason!

This algorithm is called the ** Bankers' Rounding** algorithm because, unsurprisingly, it's used by bankers. Suppose a data source provides data which is often in exactly split quantities -- half dollars, half cents, half shares, whatever -- but they wish to provide rounded-off quantities. Suppose further that a data consumer is going to derive summary statistics from the rounded data -- an average, say.

Ideally when you are taking an average you want to take an average of the raw data with as much precision as you can get. But in the real world we often have to take averages of data which has lost some precision. In such a situation the Banker's Rounding algorithm produces better results because it does not bias half-quantities consistently down or consistently up. It assumes that on average, an equal number of half-quantities will be rounded up as down, and the errors will cancel out.

If you don't believe me, try it. Generate a random list of numbers that end in 0.5, round them off, and average them. You'll find that Bankers' Rounding gives you closer results to the real average than "always round down" averaging.

The Round, CInt and CLng functions in VBScript all use the Banker's Rounding algorithm.

There are two other VBScript functions which turn floats into integers. The Int function gives you the first integer *less than or equal to its input*, and the Fix function gives you the first integer *closer to zero or equal to its input*. These functions do not round to the nearest integer at all, they simply truncate the fractional part.

UPDATE: What about FormatNumber? See this post.

It's interesting to note another side effect of Bankers' Rounding. Notice any interest paid out is odd, while loans are even? It's to get that extra half after rounding.

Those sneaky petes!

After blogging about this myself (specifically for VB/VBA, not VBScript):

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

I was surprised to see how many folks don't know this, or like me always forget it. While I've gotten no comments or trackbacks, I have had hundreds of hits from Google searches.

The interesting thing I was trying to point out in my post was that the Format function does *not* use banker's rounding. For me, this turned out to be a good thing because it allowed me to catch a problem with my own replacement Round function, which I use when banker's isn't what I want.

Thanks for the explanation.

Quick update to my prior post pointing out that VBScript's FormatNumber, like VBA's Format and FormatNumber, does not apparently use banker's rounding:

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

Thanks again.

i tried format number (to 0 decimal points), and it still rounded up.

I am dividing numbers w/ decimals and need the final result to be rounded down to a whole number, never rounded up.

What am I missing?

gened@axiscomputernetworks.com

Apparently you're missing the last paragraph of this post -- that paragraph where I point out that Int rounds down and Fix rounds positive numbers down, negative numbers up.

Also, the next day's article is about FormatNumber.

http://blogs.msdn.com/ericlippert/archive/2003/09/26/53112.aspx

It's not just for averages.

Suppose you want to round and then sum the following amounts:

$0.7550

$0.2550

I've run into a calculation where I'm dividing a payment between principal and intrest, and without the bankers round the sum of the two would not add up right, making the occasional payment off by a penny.

> It's not just for averages.

Clearly -- Bankers' rounding produces better _averages_ because it produces better _sums_.

The question of mean error accrued per operation is particularly important when dealing with _subtractions_.

If rounding tends to bias subtractions one way or another, and you perform MANY subtractions on subrahends that are close in size, then the net bias grows as the number of operations grows. That can be very, very bad. (And if its money, pretty soon you've got a salami attack on your hands!)

Doesn't this still introduce a small amount of bias since you round up all the odd values(1,3,5,7,9) and round down the evens(2,4,6,8) and there is one more odd than even? It is significantly better than always rounding up but is there a procedure that can prevent all rounding bias?

Kathleen, you've forgotten to list zero as an even number. Then there are as many evens as odds and your posited bias disappears.

PingBack from http://www.datapoohbah.com/tech/?p=353

Hmmmm. Seems to me that the Round function is inconsistent here. The round function works differently in Excel VBA than it does in Word VBA. Maybe someone up there in geniusland should have created a separate BRound (Banker's Round) function for Bankers and kept the Round function working consistently across all Office products. Seems to me that VBA programmers would like the same function with the same function name to work the same way across ALL products. Isn't consistency important anymore?

I am unable to reproduce the reported problem -- I just tried Round in Excel and in Word and they both implemented round-to-even, aka, banker's rounding.

Can you give me a clear repro scenario?

And to answer your rhetorical question -- if this is the largest inconsistency you can find between Word and Excel, then you're not looking very hard. The object models are inconsistent in many, many ways. But since they share the same VBA engine code, the VBA behaviour should be very consistent.

But more generally, I simply reject the premise of your question. "Isn't consistency important anymore?" implies that it once was important and now it is not. Clearly, designing consistent object models across products was _not_ a very high priority for the various Office teams in the past.

Over the years, we've paid a high price for that inconsistency, both in terms of customer confusion and in the fact that these inconsistencies are a barrier to our ability to bring the office object models into the managed code world.

Is consistency important now? Yes, very much so. I think you'll find that the managed Framework Design Guidelines give clear guidance on how to design object models that are consistent with the general principles of the .NET framework.

I stand corrected. The round function is consistent (banker's round) across VBA for Excel and Word. What I meant to say was that the round function in Excel, =ROUND(0.5,0) = 1 is inconsistent with it's VBA counterpart (round(0.5,0) = 0). This is inconsistent, no?

May I suggest that the Round function VBA help file be enhanced to include the fact that Banker's round is being used and not an arithmetic round. I think this would have saved me and maybe others considerable time.

What is your evidence for the assertion that bankers use "Banker's rounding"? I have never met a banker that has ever heard of this method of rounding. IRS rules specify the 5-up method, as does Euro conversions, ...

The method is valid, and IMHO is best practice, hence it would be useful if it were available as a cell formatting option (without physically changing the underlying values) in Excel. It has been the ASTM standard standard since the early 1940's; but bankers???

However it works it would be nice if the documentation actually said what method was adopted rather than leaving us to find out the hard way.....

Actually, I think its flawed for certain numbers. I'm embroiled in a row elsewhere, and I'm researching how to do the 'tricky' numbers. Try this

dim i

i = 50/111.111

wscript.echo i

wscript.echo round(i,2) 'should be 0.46

wscript.echo round(i,1) 'should be 0.4

Oops, sorry typo! second one should be .5, as the input is 0.45000045000045

The actual output for the last two is 0.45 and 0.5, and both are correct.

I am very confused why you think this is a bug. Why do you think that it would round to 0.46? Obviously the input is much, much, much closer to 0.45 than 0.46. It would be a pretty perverse implementation of Round which rounded to the one farther away!

but,

round function:

0.0451 -> 0.05

0.0450 -> 0.04

??

Again, I do not understand why this is a question.

0.0451 could be rounded to 0.04 or 0.05. 0.05 is closer. We round to the closest one. It would be stupid to round to the one farther away.

0.0450 could be rounded to 0.04 or 0.05. Neither is closer than the other. We round to the "even" one.

Many thanks for this gem...

I will go with rounding up as i am providing comparisons.

Well worth knowing though...

Thank goodness that the .Net Framework team thought to put in an overload for Math.Round that includes the MidpointRounding.AwayFromZero so we could bypass this ridiculous concept.

Math.Round should represent mathematics principles, not accounting or banking principles. I agree with the commenter above that suggested a BRound function or perhaps a BankersMath.Round or Accounting.Round. In any and all cases, Math.Round should have followed mathematic standards.

In any case, thanks for the description of what is happening and why my Round calls weren't working.

Dale

Kathleen Barnes said:

"Doesn't this still introduce a small amount of bias since you round up all the odd values(1,3,5,7,9) and round down the evens(2,4,6,8) and there is one more odd than even?"

yes is can still add bias but not for that reason. while Eric is right that the number of odd and even numbers is equal, what really matters is the distribution of odd and even numbers in *your sample* when doing repeated rounding (or at least what distribution you might expect when choosing how to round). clearly if you are rounding and summing a series of only odd numbers you will see a bias.

there are many more types of rounding with different properties and trade offs as well of course e.g. stochastic rounding

@Dale, it may be called bankers rounding but this is sound maths used in many scientific and engineering uses e.g. DSP not just accounting (essentially its probably the right thing to do unless you know your samples are *not* uniformly distributed). conversely rounding down is one of the easiest things to do but is seldom correct.

Why not make an standardization of this situation?