Round to the Nearest 5, 10 or 0.5 in Excel

To round to the nearest 5 in Excel, use =MROUND(A2,5). It rounds to the closest multiple of any number you choose: 5, 10, 0.5, 0.25 or 15 minutes. If you'd rather stick to ROUND, =ROUND(A2/5,0)*5 gives the same result. This guide covers both, plus how to always round up or down to a multiple with CEILING and FLOOR.

How to round to the nearest 5 with MROUND#

MROUND rounds a number to the nearest multiple of another number. It has two arguments:

=MROUND(number, multiple)

The examples use these prices in B2:B6:

A value exactly halfway, like 52.5, rounds away from zero, so it goes up to 55. MROUND is part of Excel 2007 and later on Windows and Mac, and of Excel for the web.

How to round to the nearest 5 with ROUND#

Divide by 5, round to a whole number, then multiply by 5 again.

=ROUND(B2/5,0)*5

Here is how it works on the Lamp (23):

1. B3/5 gives 4.6: how many fives fit into 23.
2. ROUND(4.6,0) rounds that to 5.
3. 5*5 turns it back into the price: 25.

The results are identical to MROUND for every row in the table. This trick works for any multiple: =ROUND(B2/25,0)*25 rounds to the nearest 25. The ROUND function guide explains the digits argument in detail.

How to round to the nearest 10, 100 or 1,000#

Use ROUND with a negative number of digits. Negative digits round to the left of the decimal point.

=MROUND(A2,10) also works for the nearest 10, but ROUND is shorter and works in any spreadsheet. The article on ROUND, ROUNDUP and ROUNDDOWN has more examples of negative digits.

How to round to the nearest 0.5, 0.25 or 0.05#

For a multiple smaller than 1, MROUND is the easiest choice. The ROUND trick also works: multiply by how many steps fit into 1, round, then divide.

Rounding to 0.05 is common for cash prices in countries without 1-cent coins. Rounding to 0.5 is useful for hours worked and grades. To round time to the nearest 15 minutes, use =MROUND(A2,"0:15"), because Excel stores times as fractions of a day.

How to always round up or down to a multiple#

MROUND picks the nearest multiple. To force the direction, use CEILING to round up and FLOOR to round down.

CEILING suits packaging and shipping: if boxes hold 5 items, 21 items need room for 25. FLOOR suits whole bundles you can complete. Excel 2013 and later also have CEILING.MATH and FLOOR.MATH, which handle negative numbers more predictably. The comparison of ROUND vs ROUNDUP vs ROUNDDOWN explains the same up/down idea for decimal places.

A popular pricing trick combines CEILING with subtraction: =CEILING(A2,1)-0.01 turns 23.40 into 23.99.

Rounding prices after a discount#

Discounted prices often land on awkward numbers, so you can round inside the same formula. With the price in B2 and the discount in C2, this rounds the sale price to the nearest 5 cents:

=MROUND(B2*(1-C2),0.05)

Take a price of 43 with 12% off. The raw sale price is 43 × 0.88 = 37.84. MROUND moves it to the closest multiple of 0.05, which is 37.85. A price of 26 with 12% off is 22.88, which becomes 22.90.

To round the sale price to a whole 5 instead, change the multiple: =MROUND(B2*(1-C2),5) turns 37.84 into 40, and =FLOOR(B2*(1-C2),5) keeps it at 35 so the customer never pays more than the discount promised. See how to calculate discounts and sale prices for the full method.

Which rounding formula should you use?#

Pick the function by asking two questions: what step do you round to, and does the direction matter?

If the file will be shared with people on older or other spreadsheet software, the ROUND-based versions are the safest because every program supports ROUND. If readability matters more, MROUND, CEILING and FLOOR say what they do in their names.

Common rounding mistakes#

Check these when a result surprises you:

1. MROUND with mixed signs. =MROUND(-23,5) returns #NUM!. Use =MROUND(-23,-5), which gives -25.
2. Using formatting instead of a function. Formatting changes only what you see. The cell still holds 23, not 25.
3. Expecting halves to round to even. Excel's ROUND and MROUND round 2.5 up to 3 and 52.5 up to 55. They don't use banker's rounding.
4. Mixing up CEILING and ROUNDUP. ROUNDUP works in decimal places; CEILING works in multiples.

For splitting a number into whole fives and a remainder, such as 23 into four fives plus 3, use MOD and INT. Rounding also matters for interest: round only the final result, as shown in the compound interest formula, or small errors add up.

Practise it#

Rounding questions appear in almost every Excel test, often with a twist like "nearest 5" or "always up". Practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet: you type the formula and it checks it instantly. Then try the exercise on this page to round to the nearest 5 using only ROUND.