Office – Rounding Numbers in Access, Excel or VBA

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.

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. The Excel ROUND() function would choose 3.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.

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. 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.
  • Banker's rounding — in its most common form—the .5 to be rounded is rounded 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

  • A rounding function is said to be symmetric if it either rounds all numbers away from zero or rounds all numbers towards zero.
  • A function is asymmetric if 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.

Excel's ROUND() function is symmetric, rounding away from zero for .5s and above and towards zero for the below .5s.

Errors from Rounding

Any rounding introduces an error associated with the resulting number. If you are rounding a series of numbers using arithmetic rounding, rounding up or rounding down, the overall accumulated error will always skew your result.

You use banker's rounding and alternate rounding in an attempt balance out the effect of rounding errors, thereby reducing the skew.

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

Example

To illustrate the effect of accumulated error consider a list of numbers 2.5, 3.5, 4.5, 5.5, 6.5. They total 22.5.

  • Round these numbers using arithmetic rounding and they become 3, 4, 5, 6, and 7 respectively. They total 25. That represents an accumulated error of just over 11%.
  • Rounding them up, they become 3, 4, 5, 6, 7; total 25; and accumulate an 11% error.
  • Rounding them down, they become 2, 3, 4, 5, 6; total 20; and accumulate an 11% error.
  • Applying banker's rounding, they become 2, 4, 4, 6, 6; total 22; and accumulate a 2% error.

Applying alternate rounding (assuming you start the rounding sequence with a round up), they become 3, 3, 5, 5, 7; total 23; and accumulate a 2% error.

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.

ROUNDUP() and CEILING() round numbers up and in so doing move them away from zero. Eg Excel rounds up -2.5 to -3.0.

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

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

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

Rounding in Access and VBA

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 ROUND() function.

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.

SOURCE

LINK

LANGUAGE
ENGLISH