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:
ROUNDUPandROUNDDOWN
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
FLOORor 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)/1440where 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
- Excel Mobile Repair Grand Rapids: Fast Phone Fixes
- Excel Month of Date as Text: 5 Easy Ways
- Free Excel Mileage Log Templates: 7 Real Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Count Unique Values in an Excel ColumnTo count distinct, nonblank values in an Excel column, use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))) in Excel for Microsoft 365 or Excel…
- Weighted Average in Excel: Formula, Steps and ExamplesWith values in B2:B4 and their corresponding weights in C2:C4, calculate a weighted average in Excel with =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4).…
- IF Statements for Dates in Excel: Examples and RulesTo write an IF statement for dates in Excel, compare a cell containing a real date with a…
- SUMIF Positive Numbers in Excel: Formula and ExamplesTo sum positive numbers in Excel, use SUMIF with the criterion ">0". If the numbers are in B2:B10,…
- Create an Invoice in Excel With Formulas and TotalsCreate an invoice in Excel by setting up columns for descriptions, quantities, unit prices, and line amounts, then…
- Excel Formula for Next Year Calendar?The simplest Excel formula for a next-year calendar is =DATE(YEAR(TODAY())+1,1,1), which returns the first day of next year…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.