To round a number to the nearest quarter (0.25) in Excel, use the formula =MROUND(A1, 0.25). This rounds up or down to the closest multiple of 0.25, so 1.23 becomes 1.25, and 1.10 becomes 1.00. For rounding up only, use =CEILING(A1, 0.25); for rounding down only, use =FLOOR(A1, 0.25).
Why Round to Quarter Values?
Quarter rounding is common in time billing, inventory, and pricing. For example, if you bill clients in 15-minute increments, a 1.23-hour task rounds to 1.25 hours. In manufacturing, parts may be sold in quarter-unit quantities. Excel’s MROUND function handles this precisely, but it’s important to understand how it treats negative numbers and what happens with exact multiples.
Using MROUND for Nearest Quarter
MROUND returns a number rounded to the desired multiple. The syntax is =MROUND(number, multiple). Here’s how it works:
- If the remainder is less than half the multiple, it rounds down.
- If the remainder is greater than half, it rounds up.
- If the remainder is exactly half, it rounds away from zero (so 1.125 becomes 1.25, and -1.125 becomes -1.25).
Example with values in A2:A4:
In cell B2, enter =MROUND(A2, 0.25). If A2 contains 1.23, B2 returns 1.25. If A2 contains 1.10, B2 returns 1.00. If A2 contains 1.125, B2 returns 1.25.
Rounding Up or Down with CEILING and FLOOR
Sometimes you need to always round up (e.g., billing any partial quarter as a full quarter) or always round down (e.g., discounting). Use:
=CEILING(number, significance)– rounds up to the nearest multiple of significance. For quarters,=CEILING(A2, 0.25).=FLOOR(number, significance)– rounds down.=FLOOR(A2, 0.25).
Note: CEILING and FLOOR have a second argument that must be nonzero. For positive numbers, they work as expected. For negative numbers, CEILING rounds toward zero, and FLOOR rounds away from zero (in Excel 2010 and later). If you need different behavior, consider using MROUND with a sign adjustment.
Handling Negative Numbers and Zero
MROUND with a positive multiple (0.25) works fine for negative numbers, but the rounding direction follows the rule above. For example, =MROUND(-1.23, 0.25) returns -1.25 because -1.23 is closer to -1.25 than to -1.00. If you want to round negative numbers toward zero (e.g., -1.23 to -1.00), you can use =MROUND(ABS(A2), 0.25)*SIGN(A2) or use a combination of IF and SIGN. For zero, MROUND(0,0.25) returns 0.
Formatting Results as Fractions or Time
After rounding, you may want to display the result as a fraction (e.g., 1 1/4) or as a time value (e.g., 1:15). To show quarters as fractions, apply a custom number format: # ?/? (e.g., 1.25 shows as 1 1/4). To show as time, multiply by 24 (since Excel stores time as fractions of a day) and format as [h]:mm. For example, if A2 contains 1.25 (hours), use =MROUND(A2,0.25)/24 and format as [h]:mm to get 1:15.
Common Errors and Troubleshooting
If MROUND returns #NUM!, the multiple is zero or the arguments have different signs (e.g., number positive, multiple negative). Ensure the multiple is 0.25 or a positive number. If you see #VALUE!, check that the number is numeric. Also, note that MROUND is not available in older Excel versions (before 2007). For those, you can use =ROUND(A2*4,0)/4 to round to nearest quarter, or =CEILING(A2*4,1)/4 for rounding up.
Alternatives and Related Functions
Other rounding functions include ROUND, ROUNDUP, ROUNDDOWN, and INT. For rounding to a specific number of decimal places, use ROUND. For rounding to a multiple of 10, 100, etc., use MROUND with that multiple. For rounding to the nearest 15 minutes in time calculations, you can use MROUND with 1/96 (since a day has 96 quarter-hours). For example, to round a time in A1 to the nearest 15 minutes: =MROUND(A1, 1/96).
Frequently Asked Questions
How do I round to the nearest quarter in Excel for a column of data?
Enter the formula =MROUND(A2, 0.25) in a new column next to your data, then drag the fill handle down to apply it to all cells. If you want to replace the original values, copy the formula results and use Paste Special > Values.
What’s the difference between MROUND and ROUND?
ROUND rounds to a specified number of decimal places (e.g., ROUND(A1,2) rounds to 2 decimal places). MROUND rounds to a specified multiple, which can be any number, not just powers of 10. For quarters, MROUND is more direct.
Can I round to the nearest quarter in Google Sheets?
Yes, Google Sheets also has MROUND, CEILING, and FLOOR functions with the same syntax. The formulas work identically, though the menu paths differ if you’re applying formatting.
How do I round up to the nearest quarter in Excel?
Use the CEILING function: =CEILING(A1, 0.25). This always rounds up to the next multiple of 0.25, so 1.00 becomes 1.00, 1.01 becomes 1.25, and so on.
Why does MROUND give a #NUM! error?
The #NUM! error occurs when the multiple is zero or when the number and multiple have opposite signs (e.g., number positive, multiple negative). Make sure the multiple is 0.25 and both arguments have the same sign, or use a positive multiple.
Is there a way to round to the nearest quarter without using a formula?
You can use the ROUND dialog: select the cells, go to Home > Number > Increase/Decrease Decimal, but that only changes display, not the underlying value. For true rounding, you need a formula or Paste Special with a calculation.
Related
- Free Excel Invoice Template: XLS Download Guide
- Free Excel Inventory Tracking Sheet Template? Yes—Here’s How
- Excel Inventory Tracker Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Insert Images in Excel With the IMAGE Formula Step by StepTo insert an image in an Excel cell with a formula, use the IMAGE function with a direct…
- COUNTIF Excel Text in Cell: Exact or Partial MatchesTo count cells that contain particular text anywhere in their contents, use =COUNTIF(A2:A100,"*apple*"); the asterisks allow any characters…
- Sort by a Given List in Excel: Custom Order SortingTo sort Excel data in the order of a list you specify, select the full table, choose Data…
- How to Create a Function in DatabricksCreating a user-defined function (UDF) in Databricks lets you encapsulate custom logic in Python, SQL, or Scala and…
- Oracle SQL String Length Function: LENGTH ExplainedThe Oracle SQL function for string length is LENGTH, and you use it as LENGTH(string) to return the…
- Average Equation in Excel: Formulas, Uses, and ExamplesThe average equation in Excel is =AVERAGE(range). For example, with numbers in B2:B6, enter =AVERAGE(B2:B6) in another cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.