FORMULAS & FUNCTIONS
Round to the Nearest 5, 10, 100 or 0.5 in Excel (ROUND, MROUND, CEILING, FLOOR)
Rounding sounds simple until you need something specific: prices to the nearest 0.50, quantities up to a full pack of 5, budgets to the nearest thousand, times to the nearest quarter hour. Excel has a function for each case, and choosing the wrong one gives numbers that look fine but are off by a few cents or units. This guide shows exactly which formula to use for each kind of rounding, with a free practice file and real examples you can check.

Quick answer: which rounding formula to use
| You want to round | Formula | 1,234.5678 or 47.30 becomes |
|---|---|---|
| To 2 decimals | =ROUND(A1,2) | 1,234.57 |
| To a whole number | =ROUND(A1,0) | 1,235 |
| To the nearest 10 | =ROUND(A1,-1) | 1,230 |
| To the nearest 100 | =ROUND(A1,-2) | 1,200 |
| To the nearest 1,000 | =ROUND(A1,-3) | 1,000 |
| To the nearest 5 | =MROUND(A1,5) | 45.00 |
| Up to the next 10 | =ROUNDUP(A1,-1) | 1,240 |
| Up to the next 5 | =CEILING(A1,5) | 50.00 |
| Down to the previous 5 | =FLOOR(A1,5) | 45.00 (for 47.80) |
| Down to the previous 100 | =ROUNDDOWN(A1,-2) | 1,200 |
Step 1: ROUND to decimals or a whole number
ROUND takes the number and the number of digits to keep:
=ROUND(A5,2)
Two digits gives cents, 1,234.5678 becomes 1,234.57. Zero digits gives a whole number, 1,235. A value exactly halfway rounds away from zero: 2.5 becomes 3 and -2.5 becomes -3 (rows 19 and 20 of the practice file).

Step 2: Round to the nearest 10, 100 or 1,000
Use a negative number of digits to round to the left of the decimal point:
=ROUND(A8,-2)
| Digits | Rounds to | 1,234.5678 becomes |
|---|---|---|
| -1 | Nearest 10 | 1,230 |
| -2 | Nearest 100 | 1,200 |
| -3 | Nearest 1,000 | 1,000 |
This is the cleanest way to present budgets and forecasts in round numbers while keeping a formula behind them.

Step 3: MROUND to the nearest 5, 0.5 or 0.05
When the step is not a power of 10, use MROUND with the multiple you want:
=MROUND(A10,5)
| Value | Formula | Result |
|---|---|---|
| 47.30 | =MROUND(A10,5) | 45.00 |
| 47.80 | =MROUND(A11,5) | 50.00 |
| 3.74 | =MROUND(A12,0.5) | 3.50 |
| 12.37 | =MROUND(A13,0.05) | 12.35 |
47.30 is closer to 45 than to 50, so it rounds down; 47.80 rounds up. The multiple 0.05 rounds to the nearest 5 cents, which is useful where the smallest coin is 5 cents. MROUND needs the number and the multiple to have the same sign; for a negative value, use a negative multiple. If MROUND returns #VALUE!, the number may be stored as text (see how to fix numbers stored as text).

Step 4: Always round up, or always round down
Sometimes the nearest value is not what you want. Packaging, staffing and pricing usually need to round up; commissions or allowances may need to round down.
| Function | Direction | Example | Result |
|---|---|---|---|
| ROUNDUP(number, digits) | Up, by digits | =ROUNDUP(A14,-1) | 1,240 |
| CEILING(number, multiple) | Up, to a multiple | =CEILING(A15,5) | 50.00 |
| FLOOR(number, multiple) | Down, to a multiple | =FLOOR(A16,5) | 45.00 |
| ROUNDDOWN(number, digits) | Down, by digits | =ROUNDDOWN(A17,-2) | 1,200 |
MROUND also works with times, because Excel stores a time as a fraction of a day. To round 9:23 AM to the nearest 15 minutes:
=MROUND(A18,TIME(0,15,0))
The result is 9:30 AM. Use CEILING with the same TIME(0,15,0) to always round up to the next quarter hour, which is common for billing time. For timesheets, see the hours worked formula with breaks.

Step 5: Clean prices that never fall below cost plus markup
The Pricing sheet adds a 40% markup to each cost (=B5*(1+$B$2)), then rounds the raw price three ways. Up to the next 0.50 with =CEILING(C5,0.5), and to a price ending in .99:
=CEILING(C5,1)-0.01
| Product | Cost | Raw price | Up to next 0.50 | Ending in .99 | Nearest 5 |
|---|---|---|---|---|---|
| Desk organizer | 8.37 | 11.7180 | 12.00 | 11.99 | 10.00 |
| Notebook, A5 | 2.15 | 3.0100 | 3.50 | 3.99 | 5.00 |
| Stapler | 11.80 | 16.5200 | 17.00 | 16.99 | 15.00 |
| Office chair | 88.60 | 124.0400 | 124.50 | 124.99 | 125.00 |
Because CEILING always rounds up, the price never drops below cost + markup: the lowest markup after the .99 rounding is 41.1%. MROUND to the nearest 5 is different: it rounds the stapler down to 15.00, below the 16.52 raw price. Markup and margin are easy to confuse here; see gross margin vs markup in Excel.

Step 6: Rounding vs formatting, and penny differences
Number formatting (Ctrl+1, see open Format Cells quickly) changes only what you see. On the Totals sheet, three cells of 1.4 formatted with no decimals show 1, 1 and 1, but their SUM is 4.2 and shows 4. ROUND changes the values, so the rounded cells add up to 3.
The second example is the classic invoice problem. Three lines of 10.10 with 7.5% sales tax:
| Method | Calculation | Tax |
|---|---|---|
| Round each line | ROUND(10.10 x 7.5%, 2) = 0.76, three times | 2.28 |
| Round the total | ROUND(30.30 x 7.5%, 2) | 2.27 |
Neither is wrong, but they differ by 0.01. Pick one method, use it everywhere, and make it match your invoicing system. For more on tax formulas, see the sales tax formula in Excel; for averages of rounded numbers, see weighted averages with SUMPRODUCT.

Common rounding mistakes
- Using formatting when you needed ROUND, so totals do not add up to the visible numbers.
- Using MROUND when you needed CEILING, so prices or quantities round down below the minimum.
- Rounding in the middle of a calculation instead of at the end, which compounds the error.
- Mixing round-per-line and round-the-total methods on the same invoice.
- Expecting MROUND to work with a negative number and a positive multiple.
Frequently asked questions
How do I round to the nearest 5 in Excel?
Use =MROUND(A1,5). To always round up to the next 5, use =CEILING(A1,5); to always round down, use =FLOOR(A1,5).
How do I round to the nearest 100 in Excel?
Use =ROUND(A1,-2). The negative digits round to the left of the decimal point: -1 for tens, -2 for hundreds, -3 for thousands.
How do I round to the nearest 0.5 in Excel?
Use =MROUND(A1,0.5). For example, 3.74 becomes 3.50 and 3.76 becomes 4.00.
What is the difference between ROUNDUP and CEILING?
ROUNDUP rounds up by a number of digits (decimals, tens, hundreds). CEILING rounds up to a multiple of any number, such as 5, 0.5 or 15 minutes.
Why does my total not match the rounded numbers?
The cells are probably formatted to show fewer decimals but not rounded. SUM adds the full values. Wrap the values in ROUND if the total must match what you see.
Final checklist
- Nearest step: ROUND for powers of 10, MROUND for any other step.
- Always up: ROUNDUP or CEILING. Always down: ROUNDDOWN or FLOOR.
- Times: MROUND or CEILING with TIME(0,15,0).
- Totals: ROUND the values, do not just format them.
- Invoices: one rounding method, used consistently.
Get your numbers exactly right
Browse the OneXcel Tutorial Hub for more formulas, explore ready-to-use Excel templates for pricing and invoicing, or build a dashboard with the OneXcel Dashboard Builder.
