Banker’s Rounding Explained (and Why Excel and Python Disagree)

{
“title”: “Banker’s Rounding Explained (and Why Excel and Python Disagree)”,
“slug”: “bankers-rounding-explained”,
“excerpt”: “A comprehensive guide to banker’s rounding (round half to even), its rules, standards, and why Excel and Python implement it differently. Learn the correct usage and common pitfalls.”,
“seo_title”: “Banker’s Rounding Explained: Excel vs Python | Precision Reference”,
“meta_description”: “Understand banker’s rounding (round half to even), its standards, and why Excel and Python differ. Get clear examples, tables, and expert guidance.”,
“content”: “

Banker’s rounding, also known as round half to even or Gaussian rounding, is a rounding convention that eliminates systematic bias in statistical and financial calculations. Unlike the more common half-up rounding, banker’s rounding ties the decision to the nearest even digit when the fractional part is exactly 0.5. This article explains the rule, its mathematical rationale, and why popular software tools like Excel and Python handle it differently. As part of our precision and rounding reference, we provide authoritative guidance based on international standards.

nn

Rule Statement

n

When the digit immediately to the right of the rounding position is 5 and there are no additional non-zero digits (i.e., the number is exactly halfway between two candidates), round to the candidate whose last retained digit is even. If the digit is less than 5, round down; if greater than 5, round up. For all other cases, standard rules apply.

n

For example, rounding to one decimal place:

n

n
• 2.35 → 2.4 (3 is odd, so round up to 4, which is even)

n

• 2.45 → 2.4 (4 is even, so leave it)

n

• 2.55 → 2.6 (5 is odd, round up to 6)

n

n

The rule is symmetric for negative numbers: −2.35 → −2.4, −2.45 → −2.4.

nn

Worked Examples

n

Let’s walk through several cases with step-by-step reasoning.

n

Example 1: Rounding 3.5 to Integer

n

Candidates: 3 and 4. The fractional part is exactly 0.5. The last retained digit for 3 is 3 (odd), for 4 is 4 (even). Choose 4 because it is even.

n

Example 2: Rounding 4.5 to Integer

n

Candidates: 4 and 5. The last retained digit for 4 is 4 (even), for 5 is 5 (odd). Choose 4.

n

Example 3: Rounding 2.675 to Two Decimal Places

n

In binary floating-point, 2.675 is not exactly 2.675 (it’s a repeating binary fraction). When rounding to two decimals, the exact value is 2.67499999999999982236431605997495353221893310546875, so the digit after the second decimal is 4, not 5. Thus, banker’s rounding yields 2.67. This illustrates the floating-point precision pitfall.

n

Example 4: Negative Numbers

n

Round −1.5 to integer. Candidates: −1 and −2. The last retained digit for −1 is 1 (odd), for −2 is 2 (even). Choose −2.

nn

Counter-Examples

n

Common mistakes arise when applying half-up rounding out of habit. Here are typical errors:

n

n
• Incorrect: Rounding 2.5 to 3 (half-up). Correct: 2 (banker’s).

n

• Incorrect: Assuming Excel’s ROUND uses banker’s rounding. Excel actually uses half-up (away from zero) for positive numbers and half-down toward zero for negative? Let’s check: Excel’s ROUND(2.5,0) returns 3, ROUND(-2.5,0) returns -3 (away from zero). So it’s half-away-from-zero.

n

• Incorrect: Using banker’s rounding when the digit after 5 is non-zero. The rule applies only when the digit is exactly 5 with no following non-zero digits. For example, 2.51 rounds to 3 regardless of convention.

n

• Incorrect: Applying banker’s rounding to measurements that require half-up per industry standards (e.g., pharmaceutical dosing). Always check the governing standard.

n

nn

Convention Comparison Table

n

n

n

n

n

n

n

n

n

n

n

Banker’s rounding is preferred in statistical and financial contexts because it reduces cumulative error when rounding many numbers.

nn

Standards Citation

n

Several international standards address rounding conventions. While none mandate banker’s rounding universally, they acknowledge its use in specific fields.

n

n
• ASTM E29-22 (Standard Practice for Using Significant Digits in Test Data) specifies half-up rounding in Section 6.1.1, but notes that other conventions may be used if clearly stated.

n

• ISO 80000-1:2009 (Quantities and units – Part 1: General) Annex B recommends rounding to the nearest even when the discarded digit is exactly 5, to avoid bias in calculations. See B.2.2.

n

• NIST SP 811 (Guide for the Use of the International System of Units) Section 7.3 discusses rounding and suggests that “when the digit to be dropped is exactly 5, round to the nearest even number” to minimize bias.

n

• GUM (JCGM 100:2008) (Evaluation of measurement data – Guide to the expression of uncertainty in measurement) Section 7.2.6 recommends rounding uncertainty values to one or two significant figures, but does not specify a tie-breaking rule; however, the annex G.4.2.2 mentions the use of even rounding for statistical consistency.

n

n

These standards highlight that banker’s rounding is a recognized, unbiased alternative, especially when processing large datasets.

nn

Common Mistakes

n

n
1. Confusing banker’s rounding with half-up. Always verify the tie-breaking rule in your context.

n

2. Assuming all software uses the same rounding. Excel’s ROUND, Python’s round(), and JavaScript’s Math.round() all differ.

n

3. Ignoring floating-point representation. Values like 2.675 may not be exact in binary, so the tie condition rarely occurs. Use decimal arithmetic or careful formatting when testing.

n

4. Applying banker’s rounding to measurements that require a different convention per industry standards. For example, ASTM E29 mandates half-up for test data unless otherwise specified.

n

5. Using banker’s rounding for display purposes without considering significant figures. Rounding to a fixed number of decimal places is different from rounding to significant figures. See our significant figures rules.

n

nn

Software Behavior Note

n

Why do Excel and Python disagree? Let’s examine their default rounding functions.

n

Excel (Microsoft)

n

The ROUND function uses half-away-from-zero: it rounds 0.5 up for positive numbers and down for negative numbers (i.e., away from zero). For example, =ROUND(2.5,0) returns 3, and =ROUND(-2.5,0) returns -3. Excel does not offer a built-in banker’s rounding function, but you can implement it with formulas or VBA.

n

Python

n

The built-in round() function uses banker’s rounding (round half to even) for floating-point numbers. For example, round(2.5) returns 2, and round(3.5) returns 4. However, note that Python’s round() behaves differently for integers and floats, and its behavior with floats is subject to binary representation. For decimal arithmetic, use the decimal module with ROUND_HALF_EVEN.

n

Other Languages

n

n
• JavaScript: Math.round() uses half-up (toward positive infinity) for positive numbers and half-down for negatives? Actually it rounds to nearest integer, with .5 rounding up (toward +∞). So Math.round(2.5) → 3, Math.round(-2.5) → -2 (since -2.5 rounds to -2? Actually -2.5 rounds to -2 because -2 is closer? Wait, -2.5 is exactly halfway between -2 and -3, and Math.round rounds to -2 because it rounds toward +∞ for .5? Let’s check: According to spec, Math.round(x) returns the integer closest to x, with .5 rounded up to the next integer in the direction of +∞. So -2.5 → -2. So it’s half-up toward positive infinity.

n

• R: round() uses banker’s rounding by default (round half to even).

n

• MATLAB: round() uses half-away-from-zero by default, but you can specify ‘decimals’ and ‘significant’ with tie-breaker options.

n

n

Always check the documentation for your specific tool.

nn

Quick Reference Table

n

n

n

n

n

n

n

n

n

n

n

n

This table illustrates the systematic difference when the fractional part is exactly 0.5.

nn

Related Rules

n

Banker’s rounding is one of several rounding methods. Explore these related topics:

n

n
• Half-Up Rounding – The most common convention in everyday arithmetic.

n

• Round Half Down – The opposite of half-up.

n

• All Rounding Methods – A comprehensive comparison.

n

• Significant Figures – How rounding interacts with precision.

n

nn

FAQ

n

Why is banker’s rounding called “banker’s”?

n

It is believed to have originated in banking and accounting to avoid systematically inflating or deflating totals when rounding many transactions. By rounding ties to the nearest even number, the cumulative error tends to cancel out.

n

Does banker’s rounding always produce unbiased results?

n

Not always, but it minimizes bias compared to half-up or half-down when the data are uniformly distributed. For skewed distributions, other methods might be more appropriate.

n

How do I implement banker’s rounding in Excel?

n

Excel doesn’t have a built-in function, but you can use a formula like =IF(MOD(INT(A1*10^n),2)=0, ROUNDDOWN(A1,n), ROUNDUP(A1,n)) for a tie, or use VBA’s Round function (which is banker’s rounding).

n

Is banker’s rounding required by any standard?

n

ISO 80000-1 and NIST SP 811 recommend it for unbiased rounding, but it is not mandatory. ASTM E29 uses half-up as default, but allows alternatives if specified.

nn

For further reading, see our sources and significant figures calculator to practice with precise rounding.

“,
“categories”: [
“Banker’s Rounding”,
“Rounding Methods”,
“Excel”,
“Python”,
“Rounding Rules”,
“Significant Figures”,
“Precision”,
“ASTM E29”,
“ISO 80000”,
“GUM Guide”
],
“tags”: [
“round half to even”,
“banker’s rounding”,
“Excel ROUND”,
“Python round”,
“floating point”,
“rounding bias”,
“significant figures”,
“ISO 80000”,
“NIST SP 811”,
“GUM”
],
“image_prompt”: “A detailed infographic illustrating banker’s rounding vs half-up rounding. Show a number line with points at 2.5, 3.5, 4.5, and arrows indicating where each method rounds. Use a clean, professional style with blue and orange colors. Include a small table comparing Excel and Python behavior. The image should be suitable for a technical reference website.”,
“quick_facts”: [
{
“label”: “Definition”,
“value”: “Round half to even: when the digit after the rounding position is exactly 5 with no following digits, round to the nearest even number.”
},
{
“label”: “Alternative names”,
“value”: “Gaussian rounding, round half to even, unbiased rounding.”
},
{
“label”: “Excel ROUND”,
“value”: “Uses half-away-from-zero: ROUND(2.5,0) = 3, ROUND(-2.5,0) = -3.”
},
{
“label”: “Python round()”,
“value”: “Uses banker’s rounding: round(2.5) = 2, round(3.5) = 4.”
},
{
“label”: “Primary benefit”,
“value”: “Reduces cumulative bias when rounding many numbers, especially in financial and statistical calculations.”
},
{
“label”: “Key standard”,
“value”: “ISO 80000-1:2009 Annex B recommends round half to even to avoid bias.”
},
{
“label”: “Common pitfall”,
“value”: “Floating-point representation can make exact ties rare; e.g., 2.675 is not exactly 2.675 in binary.”
},
{
“label”: “Implementation”,
“value”: “Python’s decimal module with ROUND_HALF_EVEN, or VBA’s Round function in Excel.”
}
],
“related_terms”: [
{
“term”: “Half-up rounding”,
“definition”: “A rounding method where 0.5 is always rounded up (away from zero for positive numbers). Also called round half up or arithmetic rounding.”
},
{
“term”: “Significant figures”,
“definition”: “The digits in a number that carry meaning contributing to its measurement precision. Rounding to significant figures preserves the number of meaningful digits.”
},
{
“term”: “Floating-point arithmetic”,
“definition”: “A computer representation of real numbers that approximates values using a fixed number of binary digits, leading to precision limitations.”
}
],
“references”: [
“ASTM E29-22, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications, ASTM International.”,
“ISO 80000-1:2009, Quantities and units – Part 1: General, International Organization for Standardization.”,
“NIST SP 811, Guide for the Use of the International System of Units (SI), National Institute of Standards and Technology.”,
“JCGM 100:2008, Evaluation of measurement data – Guide to the expression of uncertainty in measurement (GUM), BIPM.”,
“Python documentation: Built-in Functions round() and decimal module.”
],
“faq”: [
{
“question”: “Why is banker’s rounding called ‘banker’s’?”,
“answer”: “It is believed to have originated in banking and accounting to avoid systematically inflating or deflating totals when rounding many transactions. By rounding ties to the nearest even number, the cumulative error tends to cancel out.”
},
{
“question”: “Does banker’s rounding always produce unbiased results?”,
“answer”: “Not always, but it minimizes bias compared to half-up or half-down when the data are uniformly distributed. For skewed distributions, other methods might be more appropriate.”
},
{
“question”: “How do I implement banker’s rounding in Excel?”,
“answer”: “Excel doesn’t have a built-in function, but you can use a formula like =IF(MOD(INT(A1*10^n),2)=0, ROUNDDOWN(A1,n), ROUNDUP(A1,n)) for a tie, or use VBA’s Round function (which is banker’s rounding).”
},
{
“question”: “Is banker’s rounding required by any standard?”,
“answer”: “ISO 80000-1 and NIST SP 811 recommend it for unbiased rounding, but it is not mandatory. ASTM E29 uses half-up as default, but allows alternatives if specified.”
}
],
“related_articles”: [
“Half-Up Rounding: The Most Common Convention”,
“Round Half Down: The Opposite of Half-Up”,
“Significant Figures: Rules and Examples”,
“Floating-Point Precision in Numerical Calculations”
]
}