use excel ROUND and MROUND functions to round to nearest 5

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 the nearest 5 using the ROUND function

The formula you need in cell B2, copied down the other cells in column B, to achieve the rounding is this:

=ROUND(A2/5,0)*5

and using excel's MROUND function

You may find the MROUND function easier to set up than ROUND. But unlike ROUND, MROUND is not built in to Excel. To be aboe to use it you must apply the Excel Add-in named Analysis ToolPak.

This is the formula to put in cell B2:

=MROUND(A2,5)

disclaimer

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.