Excel Round to Nearest X: The Complete Formula Guide

Formulas & functions · Updated November 22, 2026

To round a number to the nearest specified multiple in Excel, use the MROUND function: =MROUND(number, multiple). For example, =MROUND(17, 5) returns 15, and =MROUND(18, 5) returns 20. This works for any positive or negative multiple, including decimals like 0.25 or 10.

Why MROUND Is the Right Tool

Excel has several rounding functions, and each serves a different purpose. ROUND rounds to a number of decimal places, ROUNDUP and ROUNDDOWN force direction, and CEILING/FLOOR round to a multiple but always up or down. MROUND is the only one that rounds to the nearest multiple, meaning it can round up or down depending on the remainder. This makes it ideal for pricing to the nearest 0.99, grouping quantities into batches of 10, or converting minutes to quarter-hour increments.

How MROUND Works (With Examples)

The syntax is =MROUND(number, multiple). Both arguments are required. The function rounds the first argument to the nearest multiple of the second. Key rules:

  • If the remainder is exactly half of the multiple, Excel rounds away from zero. For example, =MROUND(25, 10) returns 30, not 20.
  • If the multiple is negative, the number must also be negative; otherwise you get a #NUM! error.
  • If the multiple is zero, the result is zero.

Practical examples:

  • =MROUND(7.3, 0.5) → 7.5 (nearest half)
  • =MROUND(123, 25) → 125 (nearest 25)
  • =MROUND(-8, -3) → -9 (nearest -3, away from zero)

For rounding to the nearest 1000, use =MROUND(A1, 1000). For nearest 5 cents, use =MROUND(A1, 0.05).

How to Choose the Right Rounding Function

Your choice depends on the direction you need:

  • Nearest multiple (up or down): MROUND
  • Always up to a multiple: CEILING(number, significance) – e.g., =CEILING(17, 5) → 20
  • Always down to a multiple: FLOOR(number, significance) – e.g., =FLOOR(17, 5) → 15
  • Nearest decimal place: ROUND(number, num_digits) – e.g., =ROUND(3.14159, 2) → 3.14
  • Always up/down to a decimal place: ROUNDUP and ROUNDDOWN

For example, if you’re calculating shipping boxes that hold 6 items each and you need to order enough boxes, you’d use =CEILING(orders, 6) to avoid under-ordering. If you’re calculating a discount to the nearest whole dollar, =MROUND(price, 1) works well.

Common Problems and Errors

Three errors trip up most users:

  • #NUM! error: Occurs when the number and multiple have different signs (e.g., =MROUND(10, -5)). Fix: make both positive or both negative.
  • #VALUE! error: Occurs if either argument is non-numeric text. Check that your cells contain numbers, not text.
  • Unexpected rounding direction: Remember that MROUND rounds half away from zero. If you need half to round down, use FLOOR or add a tiny adjustment like =MROUND(A1-0.0001, 5).

Another subtle issue: floating-point precision. For example, =MROUND(2.675, 0.01) might return 2.68 in some versions, but due to binary representation it could return 2.67. If you need exact decimal rounding, consider using =ROUND(2.675, 2) first, or format the cell to show the desired number of decimals.

MROUND vs. CEILING and FLOOR

These three functions are often confused. The difference is direction:

  • MROUND rounds to the nearest multiple, either up or down.
  • CEILING always rounds up (away from zero) to the next multiple.
  • FLOOR always rounds down (toward zero) to the previous multiple.

Example with number 17 and multiple 5: MROUND → 15, CEILING → 20, FLOOR → 15. With 18: MROUND → 20, CEILING → 20, FLOOR → 15. For negative numbers, CEILING and FLOOR behave differently: =CEILING(-17, 5) returns -15 (toward zero), while =FLOOR(-17, 5) returns -20 (away from zero). MROUND with negative numbers requires a negative multiple, so =MROUND(-17, -5) returns -15.

Rounding in Real-World Scenarios

Here are three common uses with formulas:

  • Pricing to the nearest 0.99: To round a price up to the next dollar and subtract a cent, use =CEILING(A1, 1)-0.01. For example, 19.50 becomes 19.99.
  • Time to quarter-hour: To round minutes to the nearest 15-minute increment, use =MROUND(A1*1440, 15)/1440 where A1 contains a time value. The 1440 converts days to minutes.
  • Inventory to case packs: If items come in cases of 12, and you need to order enough for 100 items, use =CEILING(100/12, 1) to get 9 cases (108 items).

For budgeting, you might round expenses to the nearest $10 using =MROUND(A1, 10). For statistical data, rounding to the nearest 0.5 can simplify charts.

Keyboard Shortcuts and Formatting Tips

There’s no dedicated shortcut for MROUND, but you can quickly apply rounding to a cell using the Decrease Decimal button on the Home tab (Home > Number > Decrease Decimal). This only changes the display, not the underlying value. To actually change the value, you must use a formula or Paste Special.

To replace original values with rounded ones: copy the formula cell, then use Paste Special > Values (Ctrl+Shift+V on Windows, Cmd+Shift+V on Mac). This overwrites the original with the rounded number. Always keep a backup of your original data if you do this, because the change is permanent unless you undo.

Frequently Asked Questions

How do I round to the nearest 5 in Excel?
Use =MROUND(A1, 5). For example, 12 becomes 10, 13 becomes 15. If you need to round up to the nearest 5, use =CEILING(A1, 5).

What’s the difference between MROUND and ROUND?
ROUND rounds to a specified number of decimal places (e.g., 2 decimal places), while MROUND rounds to a specified multiple (e.g., 0.25 or 10). Use ROUND for currency to cents, and MROUND for grouping into units.

Why does MROUND return #NUM!?
This error appears when the number and multiple have opposite signs. For example, =MROUND(10, -5) is invalid. Make both arguments positive or both negative.

Can I round to the nearest 1000 in Excel?
Yes, use =MROUND(A1, 1000). For example, 12,345 becomes 12,000, and 12,500 becomes 13,000 (since half rounds away from zero).

How do I round up to the nearest 10 in Excel?
Use =CEILING(A1, 10). For example, 23 becomes 30. If you need to round down, use =FLOOR(A1, 10).

Does MROUND work with decimal multiples like 0.25?
Yes, MROUND works with any positive multiple, including decimals. =MROUND(1.3, 0.25) returns 1.25, and =MROUND(1.4, 0.25) returns 1.5.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.