DMW logo
(t) +44 (0)1732 833085
(e) david@consultdmw.com

Rounding Numbers in Microsoft® Excel, Access and VBA

Understanding the various forms of numerical rounding and how Microsoft Access, Excel and Visual Basic for Applications (VBA) deploy them.

Last updated on 2021-10-23 by David Wallis.


The Nature of Rounding

Consider the task of rounding a number that contains a fraction to, say, a whole number. The process of rounding in this circumstance is to determine which whole number best represents the number you are rounding.

In common, or arithmetic rounding, it is clear that 2.1, 2.2, 2.3 and 2.4 round to 2.0; and 2.6, 2.7, 2.8 and 2.9 to 3.0.

Excel ROUND rounding to nearest whole number

That leaves 2.5, which is no nearer to 2.0 than it is to 3.0. It is up to you to choose between 2.0 and 3.0, either would be equally valid.

To round 3.5, the Excel ROUND() function would choose 4.0:

For minus numbers under arithmetic rounding, -2.1, -2.2, -2.3 and -2.4, would become -2.0; and -2.6, 2.7, 2.8 and 2.9 would become -3.0 .

For -2.5, a choice is needed between -2.0 and -3.0. The Excel ROUND() function would choose -3.0.


Other Forms of Rounding

Rounding Up takes any number with decimal places and makes it the next “whole” number. Thus not only do 2.5 and 2.6 round to 3.0, but so do 2.1 and 2.2.

Rounding up moves both positive and negative numbers away from zero. E.g. 2.5 to 3.0 and -2.5 to -3.0.

Rounding Down truncates numbers by chopping off unwanted digits. This has the effect of moving numbers towards zero. E.g. 2.5 to 2.0 and -2.5 to -2.0

Banker’s Rounding — in its most common form — takes the .5 to be rounded and rounds it either up or down so that the result of the rounding is always an even number. Thus 2.5 rounds to 2.0, 3.5 to 4.0, 4.5 to 4.0, 5.5 to 6.0, and so on.

Alternate Rounding alternates the process for any .5 between rounding down and rounding up.

Random Rounding rounds a .5 up or down on an entirely random basis.


Symmetry and Asymmetry

Excel ROUND's symmetric rounding

A rounding function is said to be symmetric if it either rounds all numbers away from zero or rounds all numbers towards zero.

This is Excel’s ROUND() function performing symmetric rounding:

A function is asymmetric if it rounds positive numbers towards zero and negative numbers away from zero. Eg 2.5 to 2.0; and -2.5 to -3.0.

Also, asymmetric is any function that rounds positive numbers away from zero and negative numbers towards zero. Eg. 2.5 to 3.0; and -2.5 to -2.0.


Errors from Rounding

Any rounding introduces an error associated with the resulting rounded number. If you are rounding a series of numbers, then the overall accumulated error will usually skew your result:

Rounding numbers error

You might choose to use banker’s rounding in your attempts to balance out the effect of rounding errors, thereby reducing the skew.

Random rounding is another way of attempting to offset the skew.

Note: You should not assume that random rounding necessarily gives less error than banker’s, or alternate, rounding.

Also, there is the consideration that random rounding may give different results for the same set of figures.


Excel Rounding Functions

Excel’s ROUND() function performs arithmetic rounding and is symmetric. It rounds numbers up and in so doing moves them away from zero, e.g. ROUND() rounds -2.5 to -3.0.

CEILING() rounds a number to the nearest multiple of a significant number of your choice. In doing so it moves positive numbers away from zero, and negative numbers towards zero:

Excel CEILING function

CEILING.MATH() rounds a number to the nearest multiple of a significant number of your choice. Additionally, by way of its Mode argument CEILING.MATH() allows you to choose between rounding towards, or away from, zero:

Excel CEILING.MATH function

ROUNDDOWN() round numbers down and in so doing move them towards zero. Eg Excel rounds down -2.5 to -2.0.

FLOOR() moves the result in the opposite direction to CEILING():

Excel FLOOR function

FLOOR.MATH() rounds down, and like CEILING.MATH() offers you choice of rounding towards, or away from, zero:

Excel FLOOR.MATH function

The INT() function moves positive numbers towards zero, and negative numbers away from zero, as it rounds a number to the nearest whole-number (integer) equivalent.

You use the FIXED() function to return a number as text. In making the conversion the function rounds in the same way as ROUND().

You may find MROUND() easier to use than ROUND() for certain calculations, as the Microsoft Excel ROUND and MROUND Functions to Round to Nearest 5 page illustrates.

As far as I know, there are no functions in Excel for either alternate, banker’s or random rounding.


Rounding in Access and VBA

Excel ROUND examples

Excel-like Rounding in Access has details of my custom function that brings Excel-like rounding to Access/VBA..

VBA’s Int() function behaves in the same way as Excel’s INT().

VBA’s Fix() function behaves like Int() for positive numbers; but when applied to a negative number it rounds towards zero.

There are no functions in VBA for alternate or random rounding, so far as I’m is aware.


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

“Round numbers are always false.”

Samuel Johnson (1709 – 1784)