BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

Posted by Michael Allsop on 23 October 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Rounding functions in Excel

*Rounding numbers* involves replacing a number with one of
approximately the same value but with fewer digits. Rounded numbers do therefore
inherently introduce a certain level of inaccuracy into calculations which can
become an issue, for example when completing a business model spreadsheet, so at
the end of the blog some of the alternatives to using a rounding function will
be discussed.

There are however several functions that can be used to round numbers in Excel and which one you pick depends on whether you want to round up, down or just to the nearest integer or chosen multiple. This blog will look at the variety of rounding functions, how to use them and what results you can expect to get!

## The Integer function

This isn't really a rounding function at all in that it simply removes
any decimals from a number - i.e., both 17.34 and 17.98 would end up as 17 (this
is also known as *truncation*).
The syntax for the **INT** function is very straightforward and the
only argument needed is the number or the cell reference to the number that
needs to be converted to a whole number:

**=INT(Number)**

Certainly decimal places could be shown again by using a suitable number format
(as below) but the output of the **INT** function can only ever be
a whole number:

The INT function effectively rounds down to the nearest whole number.

## The Round function

**ROUND** is a more sophisticated way of rounding numbers and
works on the accepted principle that 0.5 and above rounds up while anything less
than 0.5 rounds down (this is also known as the *round half up* rule). The **
ROUND** function consists of two arguments, the number to be
rounded and the decimal place to which rounding is required:

**=ROUND(Number,Num_digits)**

A value of 0 for **Num_digits** would return the nearest
integer, a value of 1 returns to the nearest single decimal place, 2 to the
second decimal place and so on:

**Num_digits** has nothing to do with cold fingers.

If you needed to round to the nearest multiple of 10 or 100 you can use -1 or -2
in the **Num_digits** argument to achieve that result:

If you added -0.01 to the end of the formula, all your prices would be rounded to the nearest £XXX9.99!

## Rounding up or rounding down?

If you need the ability to specify decimal accuracy
but want to force the calculation to either always round up or round down then
consider the **ROUNDUP** and **ROUNDDOWN**
functions.

These two functions are pretty self-explanatory and use the same arguments as
**ROUND**,
i.e. the number to be rounded and the required decimal accuracy. In the example
below both functions have a **Num_digits** value of 0 so that they
return the appropriate integer:

**ROUNDUP** and **ROUNDDOWN** override the round half up rule.

## Rounding to a given multiple

Sometimes you might wish to round to a multiple of a specified amount that
isn't 1, 10, 0.1 and so on (which can be achieved with the standard **
ROUND** function shown above). If you wished to round to the nearest
multiple of, say, 2 then the best function for this would be **MROUND**.

Like **ROUND**, **MROUND** also works on the round
half up principle. This would mean that if you wanted to round to the nearest
multiple of 15, then 7.5 would round up to 15 whereas 7.49 would round down to
0. Similarly 22.5 would round to 30 and 22.49 down to 15. The syntax for the
**MROUND** function is as follows:

**=MROUND(Number,Multiple)**

In the example below a rounding multiple of 2 has been used:

The effect of the formula in this example is to round to the nearest even integer.

## Up to the ceiling or down to the floor

The last pair of rounding functions combine multiple rounding with the
ability to decide whether all numbers should be rounded up or rounded down to
the given multiple (known as the multiple of *Significance* in
these functions).

The **CEILING** and **FLOOR** functions consist of
the same two arguments as the **MROUND** function above but will
round up or down respectively. The syntax for these functions is shown below:

**=CEILING(Number, Significance)**

=FLOOR(Number, Significance)

=FLOOR(Number, Significance)

In use, these functions would give the following series of results:

Both these functions round to a specified multiple.

## Problems with rounded numbers

As mentioned at the beginning of the article, errors can quickly creep into calculations if several rounded numbers are used in sequence. The table below may be short but proves how inaccuracies could rapidly build up, making column totals or other calculations meaningless:

Note that the
**SUM** calculation isn't itself rounded, but the numbers it is adding up are.

The effect of rounding in the above table is admittedly made worse by the choice
of numbers. When a column consists of many numbers, some of which will be
rounded up and some of which will be rounded down based on the round half up
principle, the sum of those numbers may well be not too inaccurate. Of course,
if a totally asymmetric rounding function is employed, such as **ROUNDUP**
or **ROUNDDOWN**, then the errors are greatly increased. Summing is
only part of the story though, and if multiplier values were rounded and then
used several times within a calculation it is easy to see how considerable
inaccuracies can occur.

Consequently, it could be regarded as best practice to avoid using rounding functions completely, or at least reserving them for the final figure, and instead utilising some number formatting tricks to show the style of number required without affecting the underlying value.

## Using number formatting to round the visible number

The alternative to using a function to round a number would be to change its
format. An obvious example of this would be when using the **Currency** or
**Accounting** formats which, by default, round to two decimal places.
While this can still lead to visual errors, the full number is held in the
spreadsheet so that mathematical errors will not accumulate:

While the total for the formatted column doesn't look like it has added up correctly (1p short!), the answer is in fact more accurate that the total based on the rounded numbers in column D.

As well as using the preset number formats in Excel, a common use of custom number formatting would be to show large numbers in thousands (k) or millions (m). By using such formatting, the number £25,478,329.12 could be shown to be approximately £25.5m, though the original number would still be used in other calculations. If the number had been rounded to that figure with a function, or combination of functions, it is clear that the potential inaccuracies could be huge.

The example below shows the custom format discussed above:

Follow the steps below to create this format.

1. Right click on the cell that needs to be formatted and open the **
Format Cells** dialog box

2. Click on the **Number** tab.

3. Choose the **Custom** category.

4. Enter the format shown in the **Type** field. Each comma is a
thousands separator, so the last two lots of thousands are not shown, and the
text to be added must be bounded by speech marks.

You can see from the formula bar in the screen capture above that the full un-rounded number is still held in the spreadsheet for further calculations.