tel 01732 833085

tonbridge · kent · UK

Numbers in the round

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.

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, -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 under arithmetic rounding.

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

**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. Eg. 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. Eg. 2.5 to 2.0 and
-2.5 to -2.0

**Bankers 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.

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.

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:

You might choose to use Bankers 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 bankers, or alternate, rounding.

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

Excel's ROUND() function performs arithmetic rounding. It rounds numbers up and in so doing moves them away from zero, eg 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:

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:

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():

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

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 DMW's example illustrates.

As far as DMW knows, there are no functions in Excel for alternate, banker’s or random rounding.

CByte(), CInt(), CLng(), CCur(), and Round() all perform banker’s rounding:

Note, therefore, that the VBA Round() function differs in its action from the Excel's ROUND() function applied to the same set of numbers:

Excel-like Rounding in Access has details of DMW's 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 DMW is aware.