Rounding numbers up and down is a common requirement in everyday life and an Excel spreadsheet can help automate this function.

You don’t need to be a mathematician to understand the importance of expressing figures to a sensible number of decimal places. If you asked a friend how far he lived from the town centre, a reply such as “0.9256 miles” might seem strange. Equally, if you asked your accountant how much profit you had made last month, a vague response like “somewhere between one hundred and three million pounds” would probably not provide the level of detail you had in mind.

So knowing how to round numbers up and down in Excel can help ensure your calculated figures are always relevant to the task in hand. This month’s tips will show you how it’s done.

The ROUND function

Excel’s ROUND function takes a number and then rounds it to a specified number of decimal places. You can also use it to round to thousands or millions by specifying a negative number of decimal places. Here is how it works:

Syntax: =ROUND(number, num_digits)

In the formula above, ‘number’ is the number that you want to round and ‘num_digits’ is the number of digits to which you want to round the number.

•  If num_digits is greater than 0 (zero), then the number is rounded to the specified number of
   decimal places.

•  If num_digits is 0, the number is rounded to the nearest integer.

•  If num_digits is less than 0, the number is rounded to the left of the decimal point.

(See examples below)
 

Variations on the ROUND function

The ROUND function will round up or down to the nearest decimal place according to the num_digits value that you specify. For example 0.529 will round up to 0.53 whilst 0.521 will round down to 0.52 if your num_digits value is set to ‘2’ (i.e. 2 decimal places).

With this thought in mind, here are three useful variations on the ROUND function.

1.  If you want to always round up (away from zero), use the ROUNDUP function.

2.  To always round down (toward zero), use the ROUNDDOWN function.

3.  To round a number to a specific multiple (for example, to round to the nearest 0.5), use the
    MROUND function. The third example below demonstrates how this simple formula could be
    used to ensure your prices always round to the nearest 50 pence.