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:
- Number and multiple must have the same sign. Otherwise, you get the #NUM! error.
- 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.
