How to round a number off to the nearest multiple in Excel

If you need to round a number off to a specified multiple, for example; rounding a selling price to the nearest 5cents or rounding a time to the nearest 15 minutes—then MROUND is the function to use. Below we explain how that can be done.

Applies To: Microsoft® Excel® for Windows 2010,2013,2016

If you want to calculate what your selling price should be after adding a markup of 15%, you might want to round the price off to the nearest 5 cents.

With the MROUND function, the first argument, “number” refers to the cell containing the value to be rounded and the “multiple” is the value to use when rounding.

You can also use the MROUND function to round time to a specified increment. For example, you might need to round the time to the nearest 15 minutes.

When using the MROUND function, both arguments in the function must have the same sign, i.e. both either positive or negative.  If you have a table containing both positive and negative numbers, then you will need to multiply the second argument by the sign of the first argument using the SIGN function.

In the below example, the SIGN function returns a 1 if the sign is positive and -1 if the sign is negative.

By rounding these digits off, it is easier to describe and understand them. Calculations are also simplified this way, hence spending less time on formulas.

Sage © Sage South Africa Pty Ltd 2016 . All Rights Reserved.