How to round to the nearest 5

Rounding to a multiple is not the same as rounding to a place value; for example, to round to the nearest 5, you use a different approach. The formula round(N / S) × S handles any multiple, and Excel's MROUND function is built for it. Understand that tie-breaking rules and negative numbers change the result depending on the tool.

Every multiple rounding problem follows the same math: divide the original number by the multiple you want, round that quotient using your chosen tie rule, then multiply back. For rounding to the nearest 5, 0.5, 0.25 or 15 minutes, this identity holds. The catch is the tie rule. A spreadsheet's MROUND uses half away from zero; Python's round() uses half even. Two tools, one formula, different results when the quotient lands exactly on .5.

Use this formula when your software lacks a dedicated function. In plain arithmetic: 12.3 ÷ 5 = 2.46, round to 2, × 5 = 10. The number 12.5 ÷ 5 = 2.5, round half away from zero to 3, × 5 = 15. Half even would round 2.5 to 2, giving 10. The multiple stays the same; the tie rule decides the outcome.

Rounding to the nearest 5 or 25 is common in retail pricing, inventory counts and survey scales. The process is identical: pick the multiple, apply the formula. For 37 to the nearest 5: 37 ÷ 5 = 7.4, round to 7, × 5 = 35. For 38 to the nearest 5: 38 ÷ 5 = 7.6, round to 8, × 5 = 40. The halfway case, 37.5 to the nearest 5, lands on 7.5 after division, and the result is 40 under half away from zero, 35 under half even.

Multiples Cheat Table

Original value: 42. Nearest 5 → 40. Nearest 25 → 50. Original value: 37.5. Nearest 5 (half away) → 40. Nearest 5 (half even) → 35. Nearest 25 → 25. The table shows that a single number can produce different answers when the tie rule changes. Always check which rule your tool uses before reporting a rounded result.

Rounding to the nearest 0.5 or quarter (0.25) follows the same structure. For 1.8 to the nearest 0.5: 1.8 ÷ 0.5 = 3.6, round to 4, × 0.5 = 2.0. For 1.7 to the nearest half: 1.7 ÷ 0.5 = 3.4, round to 3, × 0.5 = 1.5. For 1.125 to the nearest quarter: 1.125 ÷ 0.25 = 4.5. Under half away from zero, that rounds to 5, × 0.25 = 1.25. Under half even, 4.5 rounds to 4, × 0.25 = 1.0. A tie on the division step changes the result by a full quarter.

Excel's MROUND handles this directly. MROUND(1.125, 0.25) returns 1.25 because MROUND uses half away from zero. The same operation in Python using round(1.125 / 0.25) × 0.25 returns 1.0. Neither is wrong; they just apply different tie rules. Verify which one your context expects.

Use MROUND in Excel: MROUND(2.3, 0.5) returns 2.5. MROUND(2.2, 0.5) returns 2.0. For ties, MROUND(2.25, 0.5) returns 2.5. The same calculation by hand using the formula round(N / 0.5) × 0.5 produces consistent results only if you apply the same tie rule. The failure case is relying on a default tie rule without knowing what it is.

Use MROUND: MROUND(1.3, 0.25) returns 1.25. MROUND(1.4, 0.25) returns 1.5.The manual formula round(N / 0.25) × 0.25 produces the same result only when the tie rule matches MROUND's half away from zero. For financial or scientific reporting, confirm the tie rule before using a rounded quarter value.

MROUND is the dedicated function for rounding any number to any multiple. Its syntax is MROUND(number, multiple). If either argument is 0, MROUND returns 0. If the number and multiple have different signs, MROUND returns #NUM!. This sign rule prevents rounding a positive number to a negative multiple and vice versa. CEILING.MATH and FLOOR.MATH handle directed rounding to a multiple: CEILING.MATH rounds up toward positive infinity, FLOOR.MATH rounds down toward negative infinity. CEILING.MATH(1.1, 1) returns 2. FLOOR.MATH(1.1, 1) returns 1. CEILING.MATH has an optional mode argument; a non-zero mode makes it round toward zero for negative numbers. FLOOR.MATH has the same mode argument. These are documented in Microsoft's MROUND, CEILING.MATH, and FLOOR.MATH support pages.

Rounding time to the nearest 15 minutes is rounding to the nearest multiple of 0.25 hours. For 0.4 hours: 0.4 ÷ 0.25 = 1.6, round to 2, × 0.25 = 0.5 hours (30 minutes). For 0.3 hours: 0.3 ÷ 0.25 = 1.2, round to 1, × 0.25 = 0.25 hours (15 minutes). The tie at exactly 0.375 hours (22.5 minutes) produces 0.5 hours under half away from zero and 0.25 hours under half even. In Excel, MROUND(0.375, 0.25) returns 0.5. The same calculation in a Python script using round() returns 0.5. Always round time values with an explicit method, not a tool default.

When the task is to always round up to a multiple, use CEILING.MATH in Excel: CEILING.MATH(1.1, 1) returns 2, CEILING.MATH(1.9, 1) returns 2, CEILING.MATH(1.0, 1) returns 1. For always rounding down, use FLOOR.MATH: FLOOR.MATH(1.9, 1) returns 1, FLOOR.MATH(1.1, 1) returns 1. For directed rounding to a multiple of 0.5, CEILING.MATH(1.1, 0.5) returns 1.5, FLOOR.MATH(1.1, 0.5) returns 1.0. These functions are built for the case where the nearest multiple is not the answer because a minimum or maximum bound applies. The failure case is using MROUND for directed rounding; MROUND rounds to the nearest, not up or down.

MROUND is the primary function. In Google Sheets, MROUND behaves identically to Excel MROUND: half away from zero, same sign rules, same zero behavior. For directed rounding, CEILING.MATH and FLOOR.MATH work the same in both applications. The formula round(N / S) × S works in any spreadsheet cell but depends on the tie rule of the spreadsheet's ROUND function. Excel's ROUND uses half away from zero. Google Sheets' ROUND also uses half away from zero. Use MROUND in a formula bar. ROUNDUP and ROUNDDOWN handle place-value rounding, not multiple rounding. The failure case is using ROUND(N / S) × S without confirming that the spreadsheet's ROUND uses the same tie rule as the expected result. For negative numbers, ROUND in Excel rounds away from zero, so -1.5 rounds to -2, not -1.

Function	Direction	Tie Rule	Sign Rule
MROUND	Nearest	Half away from zero	Same sign required
CEILING.MATH	Up (toward positive infinity)	N/A	Optional mode for negative
FLOOR.MATH	Down (toward negative infinity)	N/A	Optional mode for negative

Multiple rounding suits primary and middle-school students (grades 3-5) learning place-value rounding for Common Core standards 3.NBT.A.1, 4.NBT.A.3, and 5.NBT.A.4, as well as high-school chemistry and physics students applying significant-figure rules from OpenStax Chemistry 2e (section 1.5) and NIST SP 811. Office and finance users who need to understand how Excel's MROUND, CEILING.MATH, and FLOOR.MATH functions behave, and programmers who need to know the tie-breaking behavior of their language's built-in functions, will find the worked examples useful. Anyone looking for a philosophical or historical treatise on approximation should skip this, it is a tool and rulebook. Users needing advanced numerical-analysis techniques for error propagation in Monte Carlo simulations should consult IEEE 754-2019 directly. Those who need to round currency for tax or legal compliance in a specific jurisdiction should check their local revenue authority's published rules. The single thing that most often goes wrong is assuming every tool uses the same tie rule; half even and half away from zero produce different results for ties, and the difference can cost a full multiple.