This note describes a really irritating difference between the Excel ROUND function and the Visual Basic Round function. Read on to find out more. The examples shown here are from the Rounding function example spreadsheet (XLS 41 KB). See also the other articles on rounding.
An astute student (thanks Maka!) drew our attention recently to differences between the ROUND functions in Excel and Access. She noticed that the equivalent formula gave slightly different answers when rounding was performed in the two applications. This sparked our interest in exploring ROUNDing in Excel a little further. Forgetting about Access for now, we wanted to see if using VBA (Visual Basic for Applications) from Excel, to generate rounded values, gave answers that were consistent with the Excel ROUND worksheet function. What we found was scary. Depending on just how you did it, VBA gave the same or different answers!
Using the Excel ROUND function
The ROUND (number, num_digits) function rounds number, to the specified num_digits (usually decimal places but see Excel Help for rounding to the left of the decimal point). The rule used is the one we were taught in maths classes:
- Decide which is the last digit to keep
- Increase the last digit by 1 if the next digit is 5 or more (this is called rounding up)
- Leave it the same if the next digit is 4 or less (this is called rounding down)
(Source: http://en.wikipedia.org/wiki/Rounding viewed on 29th May 2008)
The tricky cases are those ending in 5 and negative numbers. Examples of these are shown below. The answers are exactly what we would expect to get.
Using the VBA Round function
The VBA Round function can be illustrated via a user-defined function using VBA code:
Function Rounded(Numb, NoPlaces)
' round the specified Numb to NoPlaces decimal places using VBA Round function
Rounded = Round(Numb, NoPlaces)
End Function
We used the Rounded function to Round the same values we illustrated earlier. In some situations, highlighted below in yellow, the results are different from the previous page. Why are these cases different?
The answer is that in VBA (and VB.NET and also in Access) the Round function uses the round-to-even method.
It is identical except when the digit(s) following the rounding digit starts with a five and possibly trailing zeroes; then change the last digit to the nearest even digit. That is, increase the rounded digit if it is currently odd; leave it if it is already even.
(Source: http://en.wikipedia.org/wiki/Rounding viewed on 29th May 2008)
The idea behind this method is to remove the slight upward bias, caused by always rounding fives up, of the traditional method. While we understand this issue we don’t like the fact that within one software product we can get different answers using what feels like the same function!
Can you make the VBA Round function behave like the Excel ROUND function?
Well not exactly but you can actually use the Excel ROUND function in VBA. This is what happens if the code below is used.
Function RoundedAgain(Numb, NoPlaces)
' round the specified Numb to NoPlaces decimal places using ExcelROUND function
RoundedAgain = Application.WorksheetFunction.Round(Numb,NoPlaces)
End Function
Instead of using the VBA round function the Excel Worksheet function is used instead. (Many, but not all, Excel worksheet functions can be used in a similar way in VBA.) The answers produced are the same as those produced using the conventional Excel formula shown on the first page.
Is there an Excel function that behaves like the VBA Round function?
You may well know that Excel has quite a family of rounding functions. Not only has it ROUND, ROUNDDOWN, and ROUNDUP but it also has FLOOR, CEILING and MROUND. There are also some functions that can only be used to generate integer (i.e. whole number) values: TRUNC, INT, EVEN and ODD.
None of the Excel rounding functions is a round-to-even function. However, MROUND can be coaxed into rounding in this manner! This function rounds to the nearest multiple. We used 2 for rounding positive numbers to 0 decimal places and -2 for negative numbers. Similarly, use multiples of 0.2 and -0.2, to produce rounded-to-even values with 1 decimal place.
=MROUND(10.5,2) produces 10, =MROUND(-12.5,-2) produces -12, =MROUND(0.5,0.2) produces 0 and =MROUND(-2.5,-0.2) produces -2
How do other spreadsheets round?
Well we haven’t had a very thorough look but OpenOffice.org Calc, not surprisingly, has the same worksheet functions as Excel and they appear to work exactly the same. A quick look at OpenOffice.org Basic’s functions didn’t reveal any Round function at all! Maybe the developers have avoided the problem.
Back to Microsoft Access
Access has a single rounding function defined as Round («number», «precision»). This function uses the round-to-even method just like the VBA Round function. Hence the difference, that our student Maka noticed, between the values generated in Excel and Access!