Excel ROUND and MROUND Functions to Round to Nearest 5
Last updated on 2018-08-01 by David Wallis.
In this spreadsheet, consider taking the numbers in column A and rounding them to the nearest five in column B:
Perhaps the simplest way to round a number, whole or decimal, to the nearest five is to use the MROUND function. However, with a bit of tweaking, you achieve the same results using Excel’s ROUND function.
Rounding to Nearest 5 Using ROUND Function
The formula you need in cell B2, copied down the other cells in column B, to achieve the rounding is this:
Rounding to Nearest 5 Using MROUND Function
You may find the MROUND() function easier to set up than ROUND(). But unlike ROUND(), MROUND() is not built in to all versions of Excel. To be able to use it you may need to apply the Excel Add-in named Analysis ToolPak.
This is the formula to put in cell B2:
Other Rounding Functions
Excel has a number of other functions that round numbers in one way or another. These include CEILING(),
ROUNDDOWN(), ROUNDUP(), FLOOR() and FLOOR.MATH().
Also, there are Excel functions, including INT(), that have the effect of truncating numbers.
Rounding Numbers in Microsoft Excel, Access and VBA has descriptions of these functions and some examples.
Your Support for dmw TIPS
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.
DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.