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?
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 (allegedly) 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.
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.
This article generated a lot of feedback pointing out:
FormatNumberdo not use Bankers’ Rounding.
- A great resource for people trying to understand how pre-.NET Visual Basic proper does rounding is here
- Bankers’ Rounding has an additional nice property that I did not mention. Suppose we have a principal payment of $123.755 and an interest payment of $1.245. The true sum is $125.00. If we round off the quantities first, then we get $123.76 and $1.24, and the sum is still $125.00. Other rounding algorithms would produce an error of a penny.
- Bankers’ Rounding also has nice properties when dealing with the sums of quantities that have been computed by a round-then-subtract operation.
- There was some debate over whether Bankers’ Rounding is “fair” because there are five possible odd digits: 1, 3, 5, 7 and 9, but only four possible even digits, 2, 4, 6 and 8. I leave it as an exercise to the reader to determine whether this objection has merit. (The commenter later returned to say that what they meant was that the distribution of odd and even digits was not necessarily uniform.)
ROUNDfunction in Excel may not use Bankers’ Rounding; there was some confusion on this point and I have never bothered to actually check.
- Bankers do not actually use Bankers’ Rounding, apparently; rounding ties “upwards” is the standard method. I do not know if “upwards” means “towards positive infinity” or “away from zero”, since those are different for negative numbers.
There was also an irritating back-and-forth with readers who believed they had found “flaws” in the tie-breaking algorithm; in every case, the algorithm was not tie-breaking because there was a clearly “closer” value, and that was the value chosen.
Thank goodness, the .NET
Round method takes an argument so you can say what kind of rounding you want, rather than hoping for the best.