MROUND function

The MROUND function in Excel rounds a number to the nearest multiple of a specified value. It is commonly used when you need to round numbers to a specific increment, such as rounding to the nearest 5, 10, 0.5, or other multiples.

Syntax

=MROUND(number, multiple)

Parameters

• number: The number that you want to round.
• multiple: The multiple to which you want to round the number. This is typically a positive or negative number that determines the rounding increment.

Return Value

The MROUND function returns the number rounded to the nearest multiple of the multiple. If the number is exactly halfway between two multiples, Excel rounds up to the next multiple.

How It Works

• If the remainder when dividing the number by the multiple is less than half of the multiple, the number is rounded down.
• If the remainder is greater than or equal to half, the number is rounded up.

Example Usage

Example 1: Round to the Nearest Multiple of 5

To round 17 to the nearest multiple of 5:

=MROUND(17, 5)

The result will be:

15

Explanation: 17 is closer to 15 than to 20, so it’s rounded to 15.

Example 2: Round to the Nearest Multiple of 10

To round 123 to the nearest multiple of 10:

=MROUND(123, 10)

The result will be:

120

Explanation: 123 is closer to 120 than to 130, so it’s rounded to 120.

Example 3: Round to the Nearest Multiple of 0.5

To round 7.8 to the nearest multiple of 0.5:

=MROUND(7.8, 0.5)

The result will be:

8

Explanation: 7.8 is closer to 8 than to 7.5, so it’s rounded to 8.

Example 4: Negative Numbers

The MROUND function works with negative numbers as well.

To round -13 to the nearest multiple of 5:

=MROUND(-13, 5)

The result will be:

-15

Explanation: -13 is closer to -15 than to -10, so it’s rounded to -15.

Example 5: Rounding to a Negative Multiple

You can also round to a negative multiple. For instance, if you want to round to the nearest -10:

=MROUND(37, -10)

The result will be:

40

Explanation: 37 is closer to 40 than to 30, so it rounds up to 40.

Key Notes

• If multiple is 0, the MROUND function returns a #DIV/0! error.
• The MROUND function is different from the ROUND function because MROUND rounds to a specified multiple, while ROUND simply rounds to a specified number of decimal places or significant digits.
• If the number is already a multiple of the multiple, the number remains unchanged.

Common Uses

• Financial Calculations: Rounding to the nearest cent (0.01) or to the nearest dollar (1).
• Time Calculations: Rounding to the nearest minute, hour, or day.
• Units of Measurement: Rounding to the nearest specified unit, such as rounding product quantities to increments of 5 or 10.

Example in a Real-World Scenario:

To round the total cost (in A1) of an item to the nearest 0.5 for packaging, you can use:

=MROUND(A1, 0.5)

This ensures that the cost is rounded to a value that makes sense for packaging or shipping increments.