Excel’s ROUND rounds a .5 away from zero (2.5 → 3). When a rule requires rounding halves down instead (2.5 → 2), a small ROUNDUP/subtraction trick does it.
The example
2.5 rounds down to 2, not up to 3.
| A | B | |
|---|---|---|
| 1 | Value | Half down |
| 2 | 2.5 | 2 |
| 3 | 2.6 | 3 |
The formula
The formula:
How it works
How it works:
- Standard
ROUND(2.5, 0)gives 3 — halves round away from zero. - To round halves down, use
ROUNDUP(value - 0.5, 0): 2.5 − 0.5 = 2.0, which rounds up to 2. - Values above the half (2.6 → 2.1 → 3) still round normally; only the exact .5 changes.
- This matters for tax tables, scoring rules, and regulations that specify “round half down” explicitly.
Rounding conventions: ROUND = half away from zero; this trick = half down; MROUND(value, 2)/2-style or banker’s rounding = half to even. Always confirm which rule a standard requires — the differences only show on exact halves but can matter for audited totals.
Try it: interactive demo
Enter a value (try x.5).
Variations
Half up (default)
Standard ROUND:
Half to even (banker’s)
Ties to even:
Half down to cents
2 decimals:
Pitfalls & errors
Only the exact .5 differs. All other values round the same as normal ROUND.
Negatives need care. Test the trick on negative inputs if your data has them; the half-down direction can flip.
Know your standard. Confirm whether the rule is half-down or half-to-even before relying on either.
Practice workbook
Frequently asked questions
How do I round 0.5 down instead of up in Excel?
What is banker's rounding?
Does this change non-half values?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class