Excel rounding modes — half away from zero, banker's, and four others.
Excel's ROUND always pushes a .5 tie away from zero: 0.5 → 1, 1.5 → 2, 2.5 → 3. That's “half away from zero” — one of six common tie-breaking rules. Most tools default to a different one. Below: a side-by-side comparison, what Excel does per function, and how to get banker's rounding when you need it.
| A — Value | Half away from zeroExcel ROUND defaultMODEHalf away from zeroExcel's default. When the fractional part is exactly .5, push the magnitude up (away from zero). Used by ROUND, most calculators, and the IEEE 754 “round half away from zero” mode. | Half to even (banker's)Python round() / SQL ROUND() defaultMODEHalf to even (banker's)On a .5 tie, round to the nearest even integer. Statistically unbiased — over many rounds, errors cancel out. Default in Python round(), IEEE 754, and many financial standards. Not built into Excel. | Half toward +∞Mathematical “round half up”MODEHalf toward +∞On a .5 tie, always pick the larger value (more positive). Different from “half away from zero” only for negative numbers: -0.5 → 0 here vs -1 in Excel. | Half toward zeroTruncation tie-breakerMODEHalf toward zeroOn a .5 tie, always shrink the magnitude. The mirror of Excel's default. Rare in spreadsheets; closer to TRUNC behaviour but only for the half case. | |
|---|---|---|---|---|---|
| 2 | 0.5 | 1 | 0 | 1 | 0 |
| 3 | 1.5 | 2 | 2 | 2 | 1 |
| 4 | 2.5 | 3 | 2 | 3 | 2 |
| 5 | 3.5 | 4 | 4 | 4 | 3 |
| 6 | −0.5 | −1 | 0 | 0 | 0 |
| 7 | −1.5 | −2 | −2 | −1 | −1 |
What ROUND actually does at a .5 tie
Excel's ROUND uses half away from zero: at exactly .5, push the magnitude up.
Above the half mark (.51, .6, …) every mode agrees: round up. Below the half mark (.4, .49) every mode agrees: round down. The modes only differ at the exact .5 tie — and that's where ROUND's rule fires.
.5 becomes the next integer up. For a negative number, -.5 becomes the next integer down (more negative). The magnitude grows in both cases — that's what “away from zero” means.=ROUND(0.5, 0) → 1=ROUND(1.5, 0) → 2=ROUND(-0.5, 0) → -1=ROUND(-1.5, 0) → -2This is the same behaviour as a pocket calculator, most accounting software, and the IEEE 754 round half away from zero mode. It is not banker's rounding, even though some financial contexts assume otherwise.
Banker's rounding (half to even)
At a .5 tie, round to the nearest even integer. The statistically unbiased choice — and the default in Python, IEEE 754, and many financial standards.
The reasoning: over many rounding operations, half-away-from-zero is biased upward (positive numbers always gain magnitude, negative numbers always lose it in the absolute sense). Half-to-even cancels out — half the ties round up, half round down, depending on parity. For accounting on large ledgers this matters.
0.5 → 0 (0 is even)1.5 → 2 (2 is even)2.5 → 2 (2 is even, even though .5 is closer to 3 by Excel's rule)3.5 → 4 (4 is even)4.5 → 4 (4 is even — diverges from Excel here)Implementing banker's rounding in Excel
Excel has no native banker's switch. The shortest exact formula (for non-negative numbers, rounding to the nearest integer):
=IF(MOD(A2*2, 2)=1, 2*ROUND(A2/2, 0), ROUND(A2, 0))For arbitrary precision, define a named
LAMBDA:BANKER = LAMBDA(x, d, LET(p, 10^d, IF(MOD(x*p*2, 2)=1, 2*ROUND(x*p/2, 0)/p, ROUND(x, d))))Then
=BANKER(2.5, 0) returns 2 and =BANKER(3.5, 0) returns 4.For most personal-finance spreadsheets, ROUND's default is fine. Banker's rounding matters when you're summing hundreds of thousands of values where systematic upward bias would accumulate into a real number.
The four other half-modes
None of these are Excel defaults. Knowing they exist helps when you read other tools' docs or migrate formulas.
| Mode | 0.5 | −0.5 | Where you'll meet it |
|---|---|---|---|
| Half away from zero ← Excel | 1 | −1 | Excel ROUND, calculators, IEEE 754 |
| Half to even (banker's) | 0 | 0 | Python round(), SQL ROUND() (most dialects), IEEE 754 default |
| Half toward +∞ (half up, mathematical) | 1 | 0 | Some Java libraries (HALF_UP), school math textbooks |
| Half toward −∞ (half down) | 0 | −1 | Rare; appears in some statistical packages |
| Half toward zero | 0 | 0 | Mirror of Excel's default; truncation tie-breaker |
| Half to odd | 1 | −1 | Rare; mostly theoretical interest |
For values that aren't exact halves, every mode listed above agrees — they all round to the nearest integer. The mode only matters at the .5 tie.
Which Excel function uses which mode
Excel's rounding family — eight functions, four distinct behaviours.
| Function | Mode | 2.5 | 2.1 | −2.5 |
|---|---|---|---|---|
ROUND(n, digits) | Half away from zero | 3 | 2 | −3 |
ROUNDUP(n, digits) | Always away from zero (every fraction) | 3 | 3 | −3 |
ROUNDDOWN(n, digits) | Always toward zero (truncate) | 2 | 2 | −2 |
MROUND(n, multiple) | Half away from zero, to multiple | 3 | 2 | −3 |
TRUNC(n) | Drop fractional part | 2 | 2 | −2 |
INT(n) | Floor (toward −∞) | 2 | 2 | −3 |
CEILING(n, multiple) | Ceiling (toward +∞ in modern Excel) | 3 | 3 | −2 |
FLOOR(n, multiple) | Floor (toward −∞ in modern Excel) | 2 | 2 | −3 |
Note the contrast between TRUNC and INT: for −2.5, TRUNC gives −2 (drops the .5) but INT gives −3 (floors toward −∞). For positives they agree.
Defaults in Python, JavaScript, SQL
If you're porting an Excel formula to code (or the other way), the tie-breaker often changes silently. Watch for it.
| Tool / language | Default mode for 2.5 | Result |
|---|---|---|
| Excel, Google Sheets, LibreOffice Calc | Half away from zero | 3 |
Python round() | Half to even (banker's) | 2 |
JavaScript Math.round() | Half toward +∞ (asymmetric!) | 3 for 2.5, −2 for −2.5 |
SQL Server, PostgreSQL ROUND() | Half away from zero (matches Excel) | 3 |
MySQL ROUND() | Half away from zero — but rounds halves of doubles erratically | 3 (usually) |
Java BigDecimal default | HALF_UP (away from zero for halves) | 3 |
C/C++ round() (math.h) | Half away from zero | 3 |
The single most common surprise: a Python script that computes round(2.5) as 2 while the matching Excel cell returns 3. Same inputs, different totals over a large dataset. If you have a test suite comparing Excel output to Python, pin the rounding mode explicitly on both sides.
Related
ROUND's main reference and adjacent rounding functions.
FAQ
6.01Does Excel ROUND use half-away-from-zero?▸
Yes. Excel's ROUND always pushes a .5 tie away from zero: 0.5 rounds to 1, 1.5 to 2, 2.5 to 3, and -0.5 to -1. This matches most pocket calculators and the IEEE 754 “round half away from zero” mode. It is NOT banker's rounding (which would give 2.5 → 2).
6.02How do I get banker's rounding in Excel?▸
No native function — write a formula or LAMBDA. Short version for integer rounding of non-negative numbers: =IF(MOD(A2*2, 2)=1, 2*ROUND(A2/2, 0), ROUND(A2, 0)). For production work, wrap the logic in a named LAMBDA so it lives in one place and works for arbitrary precision.
6.03Why does -0.5 round to -1 in Excel but 0 in some other tools?▸
Excel uses half-away-from-zero, which grows the magnitude — so -0.5 becomes -1. Tools using “half toward +∞” (the mathematical convention) return 0 for -0.5 because 0 is the larger value. Python's default round() uses banker's rounding, so -0.5 → 0. Same input, three answers, three modes.
6.04What's the difference between ROUNDUP and “round half up”?▸
ROUNDUP rounds all non-zero fractions away from zero, not just .5 ties. =ROUNDUP(2.1, 0) returns 3, not 2. “Round half up” is a tie-breaking rule that only fires when the value is exactly .5. The Excel function closest to mathematical “half up” is ROUND itself.
6.05Which Excel function uses which rounding mode?▸
ROUND = half away from zero. ROUNDUP = always away from zero. ROUNDDOWN = always toward zero. MROUND = half away from zero (to a multiple). TRUNC = drop fractional part. INT = floor toward −∞ (differs from TRUNC for negatives). CEILING and FLOOR = toward +∞ and −∞ respectively, to a multiple. See §4 above for the full matrix.
6.06Does Google Sheets ROUND behave the same as Excel?▸
Yes. Google Sheets' ROUND is identical to Excel's — half-away-from-zero, same syntax, same edge cases. Same for ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR.