Excel MROUND: nearest multiples, signs and midpoint limits

MROUND returns a number rounded to the nearest multiple of a specified step. Its Polish name is ZAOKR.DO.WIELOKR. I use it when values should fall on a defined grid, such as multiples of five units. First, however, I establish whether the requirement is nearest or always in a particular direction.

That distinction matters for capacity. The nearest multiple may be below the required quantity. A rounding function does not know what the worksheet is meant to achieve and cannot promise that the resulting capacity will hold all the items.

Syntax and availability

=MROUND(number,multiple)

Both arguments are required. The first supplies the value; the second defines the step. These examples assume English function names, argument commas and a decimal point. In typical Polish notation, use =ZAOKR.DO.WIELOKR(liczba;wielokrotność) with a decimal comma.

The Microsoft reference covers Microsoft 365 and Excel releases including 2016, 2019, 2021 and 2024. Other spreadsheet applications require their own documentation. A similar function name alone does not confirm identical boundary behaviour.

Finding the nearest multiple by hand

All data below is synthetic. With a step of five and a value of 27, compare 25 and 30. Their distances are two and three, so 25 is nearer. For 28, the nearer candidate is 30. The second argument describes a multiple, not a count of decimal places.

For a negative number, away from zero means a more negative result. I therefore use that wording instead of saying that a tie always selects the larger number. The latter would incorrectly suggest -20 rather than -30 in the last example.

Negative values require a negative multiple

The previous Polish text incorrectly claimed that a negative value and positive step could produce the rounded negative result. Microsoft’s contract requires the two arguments to have the same sign. For example, =MROUND(-17,5) returns #NUM!, shown as #LICZBA! in Polish Excel.

The corrected formulas are:

=MROUND(-17,-5)
=MROUND(-22,-10)

They return -15 and -20 respectively. In the first case, -15 is two units away while -20 is three away. In the second, -20 is nearer than -30. The sign of the step is part of the function’s rules rather than an optional presentation detail.

I would not remove negative signs from source data merely to suppress an error. A minus sign may represent a correction or direction. Losing it changes the interpretation. Decide what those records mean before constructing a rounding expression for them.

Decimal steps have a midpoint limitation

For a synthetic value of 3.14 and step of 0.05, the nearest multiple is 3.15: =MROUND(3.14,0.05). That ordinary case does not establish a dependable rule for every decimal midpoint.

Microsoft explicitly lists a known limitation: the rounding direction at midpoints is undefined when the multiple is decimal. Its documentation illustrates different directions for 6.05 and 7.05 with a step of 0.1. I therefore do not present this function as a universal settlement rule where midpoint handling must follow an exact policy.

If the process needs such a policy, define the units, representation and required rule with its owner. Displaying two decimal places does not remove calculation limitations. Presentation and changing the underlying value are separate operations.

Nearest and always upward answer different questions

Suppose you have 27 items and boxes holding five. MROUND(27,5) returns 25, which is insufficient capacity. For positive quantities and a positive step, CEILING.MATH rounds upward to a multiple; the required capacity for 27 with a step of five is 30.

“How many whole boxes?” is another question. Its result has units of boxes rather than items. Divide the required items by box capacity, then apply the agreed upward rounding rule to that count. Do not describe 30 as the number of boxes when it represents available item capacity.

If the task concerns a number of decimal places instead of a multiple, see ROUNDUP. For language differences, the formula translation guide covers names, separators and complete expressions.

Exercise and result checks

Write down 24, 25 and 26 with a step of ten. The expected results are 20, 30 and 30. For their negative counterparts with a negative step, expect -20, -30 and -30. These cases cover both sides of the boundary and an exact midpoint with an integer step.

Next, deliberately pair a negative number with a positive step and identify the documented error. Finally, return to the business requirement: should the output be nearest, or must it never be too small? That decision determines whether the chosen function is appropriate in the first place.