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.

Excel MROUND formula rounding 47.30 to the nearest 5 as 45.00, with 1,234.5678 rounded to the nearest 10 as 1,230 and to the nearest 100 as 1,200, and 3.74 to the nearest 0.5 as 3.50.
MROUND and ROUND cover almost every rounding job.

Quick answer: which rounding formula to use

You want to roundFormula1,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
Free download: Get the rounding practice file (.xlsx). It has every example from this guide, a pricing sheet and a totals sheet that shows why rounding and formatting are not the same. Works in Excel 2010 and later, Microsoft 365 and Excel for the web.

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).

Excel ROUND formula rounding 1,234.5678 to 2 decimals as 1,234.57 and to a whole number as 1,235.
ROUND with 2 and 0 digits.

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)
DigitsRounds to1,234.5678 becomes
-1Nearest 101,230
-2Nearest 1001,200
-3Nearest 1,0001,000

This is the cleanest way to present budgets and forecasts in round numbers while keeping a formula behind them.

Excel ROUND with negative digits rounding 1,234.5678 to the nearest 10, 100 and 1,000 as 1,230, 1,200 and 1,000.
Negative digits: tens, hundreds, thousands.

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)
ValueFormulaResult
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).

Excel MROUND rounding 47.30 to 45.00 and 47.80 to 50.00 at the nearest 5, 3.74 to 3.50 at the nearest 0.5 and 12.37 to 12.35 at the nearest 0.05.
MROUND rounds to any multiple.
Try it: Change A10 from 47.30 to 47.50. MROUND returns 50.00, because a value exactly halfway rounds up. Then try 47.49: it returns 45.00.

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.

FunctionDirectionExampleResult
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.

Excel ROUNDUP, CEILING, FLOOR and ROUNDDOWN examples, with 47.30 up to the next 5 as 50.00, 47.80 down to 45.00 and 9:23 AM rounded to 9:30 AM.
Up, down and to the nearest 15 minutes.
Negative numbers: CEILING and FLOOR treat negative numbers differently across Excel versions. The practice file uses positive values. In Microsoft 365, CEILING.MATH and FLOOR.MATH let you choose whether negatives round toward or away from zero.

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
ProductCostRaw priceUp to next 0.50Ending in .99Nearest 5
Desk organizer8.3711.718012.0011.9910.00
Notebook, A52.153.01003.503.995.00
Stapler11.8016.520017.0016.9915.00
Office chair88.60124.0400124.50124.99125.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.

Excel pricing table that rounds cost plus a 40 percent markup up to the next 0.50 and to prices ending in .99 with CEILING, with a lowest markup after rounding of 41.1 percent.
Round prices up so the markup is protected.

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:

MethodCalculationTax
Round each lineROUND(10.10 x 7.5%, 2) = 0.76, three times2.28
Round the totalROUND(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.

Excel totals showing that number formatting hides decimals while ROUND changes them, and that rounding tax per line gives 2.28 against 2.27 on the invoice total.
Formatting hides decimals; ROUND removes them.

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.
Practice file: Download the rounding practice file. Change the yellow values and watch each formula respond.

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.