In data analysis, inventory management, and pricing calculations, rounding values to standard mathematical thresholds—such as nearest integers or fixed decimal places—often fails to reflect practical constraints. Many operational workflows require rounding values to specific increments, such as rounding retail figures to nearest £0.05, batch orders to dozens (multiples of 12), or logged working hours to nearest 15-minute intervals. Google Sheets provides the dedicated MROUND function to calculate the nearest multiple dynamically.
Syntax and Argument Structure
The MROUND function takes two numeric inputs: the target value to evaluate and the desired multiple factor to enforce:
=MROUND(value, factor)- value: The numeric expression or cell reference containing the starting number to round.
- factor: The numeric base or interval to which the value must be rounded. Both
valueandfactormust share the same sign (both positive or both negative); otherwise, Google Sheets returns a#NUM!error.
When the remainder of dividing value by factor is exactly equal to or greater than half of factor, MROUND rounds away from zero to the next multiple. When the remainder is less than half, it rounds towards zero.
Comparing MROUND, CEILING, and FLOOR
Selecting the appropriate rounding function depends on whether business logic demands strict upward rounding, strict downward truncation, or nearest-distance calculation:
| Function | Direction | Input (=F(14.2, 5)) | Result | Primary Use Case |
|---|---|---|---|---|
MROUND |
Nearest multiple (arithmetic midpoint) | =MROUND(14.2, 5) |
15 | Pricing, cash change optimisation, and normal scheduling. |
CEILING |
Always rounds up away from zero | =CEILING(14.2, 5) |
15 | Capacity quotas, packaging minimums, and shipping containers. |
FLOOR |
Always rounds down towards zero | =FLOOR(14.2, 5) |
10 | Completed billable blocks, discount eligibility tiers, and allowances. |
Step-by-Step Practical Implementation
- Rounding Retail Pricing to Commercial Increments: To round arbitrary raw cost-plus pricing in cell
A2to the nearest 25p (£0.25) increment, enter:
If=MROUND(A2, 0.25)A2holds £12.11, the formula outputs £12.00; ifA2holds £12.13, it rounds to £12.25. - Rounding Timestamps to 15-Minute Billing Intervals: Because time values in Google Sheets are internally represented as fractional days (where 24 hours equals 1.0), 15 minutes is represented as
15 / (24 * 60)orTIME(0, 15, 0):
Alternatively, using numeric fractions:=MROUND(A2, "00:15:00")
A timestamp of=MROUND(A2, 1/96)09:07:42rounds to09:15:00, streamlining timesheet audits. - Array-Enabled Bulk Processing: To transform an entire column of raw order figures without dragging formulas down individual rows, pair
MROUNDwithARRAYFORMULA:
This instantaneously converts units into nearest dozen lots across thousands of records.=ARRAYFORMULA(IF(ISBLANK(A2:A), , MROUND(A2:A, 12)))
Handling Sign Mismatch Errors
If financial sheets contain occasional negative adjustments, supplying a static positive factor like 5 results in a calculation failure. Wrap the factor in the SIGN function to dynamically match positive and negative values:
=MROUND(A2, 5 * SIGN(A2))This guarantees mathematical continuity without manual formula branching across ledger entries.