The Cowboy Function ROUNDUP, and its Pals ROUNDDOWN, ROUND and MROUND

Nothing to do with Cowboys really (nor Monsanto either), just couldn’t resist the pun.

ROUND(number, num_digits) rounds to the nearest digit (up or down as required)
ROUNDUP(number, num_digits) always rounds up* to the nearest digit
ROUNDDOWN(number, num_digits) always rounds down* to the nearest digit

The functions take the following arguments:

• Number (required): Any real number that you want rounded.
• Num_digits (required integer): The number of decimals to which to round the number.  0 for integers.  -1 to round to tens, -2 for hundreds, -3 for thousands, and so on.  If it is not an integer, only the integer part is used.

Examples:

Here, Number and Num_Digits are named ranges.

The number in the top row varies, to show how num_digits (row 2) affects the result.

*Notice the last column, J, where number is negative: Rounding “up” in this case means “away from zero”.  Rounding “down” means “towards zero”.

When to use the ROUND, ROUNDUP, or ROUNDDOWN Functions

Use these functions to round to a given number of decimal digits, integers, tens, hundreds, thousands, and so on.

Or use MROUND instead of ROUND:

If you want to round to a multiple, say to the nearest half or quarter, then use

MROUND(number, multiple) which rounds to the indicated multiple:

As you can see, =MROUND(number, 0.01) produces the same answer as =ROUND(number, 2).  =MROUND(number, 0.1) produces the same answer as =ROUND(number, 1).  =MROUND(number, 100) produces the same answer as =ROUND(number, -2), and so on.

Two quirks of MROUND:

1. Number and multiple must have the same sign. Otherwise, you get the #NUM! error.
2. Unlike ROUND, ROUNDUP, and ROUNDDOWN in the top example, MROUND will not use multi-cell named ranges (this is probably a bug).

There are no equivalents of ROUNDUP and ROUNDDOWN for MROUND.