For most users, the ROUND function is pretty self explanatory. A decimal of .5 or above rounds up and anything else rounds down. With ROUND, you can select the number of decimal places. You can also choose negative decimal places.
will return 2,000. The -3 tells Excel to round to the nearest thousand.
That’s useful for factors of 10, but what about lesser factors, like 5. For that, you can use the MROUND function which is part of the Analysis Toolpack Add-in.
will return 20, the closest multiple of 5. If you don’t want to install the ATP, you can achieve the same result with a formula like this
This divides the number by 5, rounds it to no decimal places, then multiplies it by 5.
You can also use MROUND for multiples less than 1. To round a price to the nearest nickel, you can use
to get 10.55. If you’re a shrewd business person, you might want to always round up. For that, use the CEILING function
to get 10.60. Mmmm…profits.
There are a lot of other rounding functions. See help on ROUND if none of the above fill the bill.