How to round to the nearest 0.05 in Excel (MROUND, CEILING and FLOOR)

MROUND: the nearest multiple

=MROUND(number, multiple) returns the multiple of multiple that is closest to number. With 0.05 that means the nearest 5 cents:

• 18.792 → 18.80 (18.80 is 0.008 away, 18.75 is 0.042 away)
• 13.3249 → 13.30
• 24.968 → 24.95
• 3.333 → 3.35

A value exactly halfway between two multiples is rounded away from zero, like ROUND does. Change the multiple and the same formula rounds to anything: =MROUND(A2,0.25) to the nearest quarter (13.3249 → 13.25), =MROUND(A2,5) to the nearest 5 dollars (247 → 245).

Worked example: shelf prices to the nearest 5 cents

Rows 2 to 6 are calculated prices of a fictional coffee roaster, rounded three ways. Rows 9 to 15 apply the same functions to other multiples.

Worked example: shelf prices to the nearest 5 cents

	A	B	C	D	E
1	Item	Exact price	Nearest 0.05 (MROUND)	Up (CEILING)	Down (FLOOR)
2	House blend 250 g	18.7920	18.80	18.80	18.75
3	Decaf 250 g	10.5120	10.50	10.55	10.50
4	Espresso 250 g	13.3249	13.30	13.35	13.30
5	Filter 1 kg	24.9680	24.95	25.00	24.95
6	Sample bag	3.3330	3.35	3.35	3.30
7
8	Other roundings	Value	Result	Formula used
9	Nearest quarter (0.25)	13.3249	13.25	MROUND(x,0.25)
10	Nearest 5 dollars	247	245	MROUND(x,5)
11	Charm price ending .99	16.66	16.99	CEILING(x,1)-0.01
12	Bags in cases of 12	41	48	CEILING(x,12)
13	Hours billed per started quarter hour	1.78	2.00	CEILING(x,0.25)
14	Refund, MROUND with a negative multiple	-3.17	-3.15	MROUND(x,-0.05)
15	Refund, sign-safe ROUND version	-3.17	-3.15	ROUND(x/0.05,0)*0.05

Prices and quantities are fictional. Column D of rows 9 to 15 shows the formula pattern used in column C.

Why ROUND is not enough

ROUND works with decimal places, not multiples. =ROUND(10.512,2) gives 10.51, the nearest cent, and =ROUND(10.512,1) gives 10.50, the nearest 10 cents. Neither gives you 5-cent steps. If you prefer ROUND, divide by the step, round, and multiply back:

=ROUND(A2/0.05,0)*0.05

This gives the same result as MROUND for positive numbers and also works for negative numbers without any sign rules. In older templates you will also see =ROUND(A2*20,0)/20, which is the same idea, because there are 20 steps of 0.05 in one unit.

CEILING and FLOOR: always up or always down

Nearest is not always what you want. =CEILING(A2,0.05) rounds up to the next multiple and =FLOOR(A2,0.05) rounds down. On the decaf price of 10.512, MROUND gives 10.50, CEILING 10.55 and FLOOR 10.50. Use CEILING when the result must never be below the calculated value, such as a minimum selling price, and FLOOR when it must never be above, such as a discount cap.

CEILING is also the tool for packs and time:

• Full cases: 41 bags in cases of 12 → =CEILING(41,12) = 48. MROUND would give 36 and leave you 5 bags short.
• Started quarter hours: 1.78 hours of work → =CEILING(1.78,0.25) = 2.00 hours billed. For real time values use TIME(0,15,0) as the multiple.

Charm prices ending in .99 or .95

Retail prices often end in .99. Round up to the next whole dollar and subtract one cent: =CEILING(A2,1)-0.01 turns 16.66 into 16.99. For .95 endings, subtract 0.05 instead. Rounding up first guarantees the price never falls below the calculated one, which FLOOR or ROUND would not. If you set prices from a cost and a margin, the guide on markup vs margin shows how to get the calculated price first.

Negative numbers and the #NUM! error

MROUND needs the number and the multiple to have the same sign. =MROUND(-3.17,0.05) returns #NUM!. For refunds and credit notes either use a negative multiple, =MROUND(-3.17,-0.05) = −3.15, or a formula that works for both signs:

• =MROUND(A2,0.05*SIGN(A2)) flips the multiple with the sign of the number.
• =ROUND(A2/0.05,0)*0.05 needs no sign rule at all.

CEILING and FLOOR have their own rules for negatives. CEILING.MATH and FLOOR.MATH accept a positive multiple for negative numbers and have a mode argument that decides whether to round toward or away from zero.

Round the value, not just the display

Number formatting only changes what you see. A cell formatted to two decimals still holds 18.792, and a total of such cells can differ from the sum of the prices on the screen. MROUND, CEILING and FLOOR change the stored value, so the totals match what customers see. On invoices, round each line first and then add the rounded lines, as in calculating VAT from a gross amount.

Common mistakes

• Using ROUND with 2 decimals. That rounds to the nearest cent, not to 5 cents. Use MROUND(x,0.05) or ROUND(x/0.05,0)*0.05.
• MROUND on negative numbers. A positive multiple with a negative number returns #NUM!. Use a negative multiple, SIGN, or the ROUND version.
• MROUND for packs. Nearest can round down and leave you short. Order quantities and billed time need CEILING.
• Formatting instead of rounding. A two-decimal format hides the extra digits but keeps them in every sum. Round the value with a formula.
• Charm prices from FLOOR. FLOOR(x,1)-0.01 can put the price below the calculated one. Round up with CEILING first.
• Comparing rounded results with =. 0.05 is not exact in binary, so a result can be 3.1500000000000004. Wrap comparisons in ROUND(…,2).