MROUND rounds a number to the nearest multiple you choose. It handles increments such as 5, 0.25, 12 units, or 15 minutes.
To round 23 to the nearest 5, use =MROUND(23,5). The tested result was 25.
Round to the Nearest 5 or Another Increment
=MROUND(23,5)Multiples of 5 around 23 are 20 and 25. Since 25 is closer, MROUND returned 25.
The first argument can be a cell reference. For a value in A2, use =MROUND(A2,5) and fill the formula down.
MROUND Function Syntax and Midpoint Rule
=MROUND(value,factor)- value is the number to round.
- factor is the increment whose nearest multiple should be returned.
When a value sits exactly between two multiples, Sheets returns the multiple with the greater absolute value.
The tested formulas =MROUND(22.5,5) and =MROUND(-22.5,-5) returned 25 and -25.
Round Prices to the Nearest 0.25
The factor can be a decimal. This formula rounds a price in A2 to the nearest quarter:
=MROUND(A2,0.25)In the test, =MROUND(7.63,0.25) returned 7.75.
Apply a currency number format when the result represents money. Formatting changes how the number displays, not the rounded value.
Round Times to 15-Minute Blocks
Times are stored as fractions of a day. Use TIME to create a 15-minute factor:
=MROUND(TIME(0,38,0),TIME(0,15,0))The tested effective value was 0.03125, which equals 45 minutes. With an h:mm format, Sheets displays 0:45.
For a time or duration in A2, use =MROUND(A2,TIME(0,15,0)).
Round Inventory to Pack Sizes
For items packed in cases of 12, use:
=MROUND(A2,12)A requirement of 47 rounds up to 48. A requirement of 25 rounds down to 24.
Handle Negative Numbers and Zero Factors
The value and factor must have the same sign. In the test, =MROUND(-23,5) returned #NUM!.
Use a negative factor with a negative value, such as =MROUND(-23,-5).
A zero in either argument returns zero. The tested formulas =MROUND(0,5) and =MROUND(23,0) both returned 0.
Choose MROUND, CEILING, FLOOR, or ROUND
- Use MROUND for the nearest multiple.
- Use CEILING to move up to the next multiple.
- Use FLOOR to move down to the previous multiple.
- Use ROUND for a chosen number of decimal places.
See Google’s MROUND reference for the official sign and midpoint rules.
Other Google Sheets articles you may also like