Match Excel Rounding in pandas

A finance team compares the Python report with the old workbook and finds VAT amounts a cent apart on a handful of lines. Nothing is broken; the two tools round differently. Excel's ROUND sends exact halves away from zero. pandas, NumPy and Python's built-in round send them to the nearest even digit, and floating-point storage makes some values that look like halves fall slightly short. This guide, part of Excel Formula Equivalents in pandas, shows the differences, gives vectorised versions of ROUND, ROUNDUP, ROUNDDOWN and MROUND that match a spreadsheet exactly, and fixes totals that no longer add up after rounding.

Prerequisites

pip install pandas numpy openpyxl

The values used throughout — halves, negative halves, and two decimals that floating point cannot store exactly:

import numpy as np
import pandas as pd

values = pd.Series([0.5, 1.5, 2.5, -2.5, 2.675, 1.005, 0.125, 1234.5, -0.125])
print(values.round(0).tolist())
# [0.0, 2.0, 2.0, -2.0, 3.0, 1.0, 0.0, 1234.0, -0.0]
print(values.round(2).tolist())
# [0.5, 1.5, 2.5, -2.5, 2.68, 1.0, 0.12, 1234.5, -0.12]

The spreadsheet results for the same values, recalculated with ROUND, are 1, 2, 3, -3, 3, 1, 0, 1235 and 0 for zero decimals, and 2.68, 1.01, 0.13 and -0.13 for the interesting two-decimal cases.

Step 1 — Understand the two differences

There are two separate effects. The first is the rule for exact halves. pandas uses round-half-to-even, so 0.5, 1.5 and 2.5 become 0, 2 and 2: half the halves go down, half go up, which avoids a statistical upward drift in large sums. Excel uses round-half-away-from-zero: every half goes up in magnitude.

The second is binary storage. 1.005 cannot be represented exactly; the stored value is 1.00499999999999989… Any rounding rule applied to that number rounds down. Excel sidesteps this by treating numbers as having at most 15 significant digits, so to Excel the value is exactly 1.005, and it rounds up. Python's built-in round shows the same storage effect even for 2.675, which pandas happens to round up:

print(round(2.675, 2), values.round(2)[4])          # 2.67 2.68

To match Excel you need to fix both: use the half-away-from-zero rule, and apply it to the value Excel thinks it has.

Step 2 — A vectorised ROUND

Scale the absolute value, round away the floating-point dust at nine decimals, add a half and take the floor, then restore the sign:

def excel_round(x, digits=0):
    x = np.asarray(x, dtype=float)
    factor = 10.0 ** digits
    scaled = np.round(np.abs(x) * factor, 9)        # 100.49999999999999 -> 100.5
    return np.sign(x) * np.floor(scaled + 0.5) / factor + 0.0

print(excel_round(values, 0).tolist())
# [1.0, 2.0, 3.0, -3.0, 3.0, 1.0, 0.0, 1235.0, 0.0]
print(excel_round(values, 2).tolist())
# [0.5, 1.5, 2.5, -2.5, 2.68, 1.01, 0.13, 1234.5, -0.13]
print(excel_round([1234.5], -1).tolist())           # [1230.0]

Every result matches the spreadsheet. The intermediate np.round(..., 9) is what handles 1.005: 1.005 * 100 is 100.49999999999999, and rounding that to nine decimals gives 100.5, which then rounds up. Negative digits work as in Excel, rounding to tens, hundreds and so on. The final + 0.0 turns the negative zero produced by np.sign(-0.125) * 0 into a plain zero, so it is not written to Excel as "-0".

Step 3 — Check it against an exact reference

A vectorised trick deserves a test. Python's decimal module can round exactly with ROUND_HALF_UP (which, despite its name, rounds halves away from zero), provided each Decimal is built from the value's 15-significant-digit text — the number Excel sees:

from decimal import ROUND_HALF_UP, Decimal

def decimal_round(v, digits=2):
    return float(Decimal(format(v, ".15g")).quantize(Decimal(1).scaleb(-digits), rounding=ROUND_HALF_UP))

rng = np.random.default_rng(1)
sample = np.round(rng.uniform(-1000, 1000, 50_000), 3)
exact = np.array([decimal_round(v) for v in sample])
print(int((excel_round(sample, 2) != exact).sum()))     # 0

Fifty thousand random values with three decimals — about one in ten of which end in a 5, the case where the rules differ — agree completely. The Decimal version is exact but runs in a Python loop, so it is best kept as the reference in a test rather than used on large columns.

Step 4 — ROUNDUP, ROUNDDOWN and MROUND

Excel's ROUNDUP rounds away from zero and ROUNDDOWN towards zero, whatever the next digit is. MROUND rounds to the nearest multiple, with halves away from zero:

def excel_roundup(x, digits=0):
    x = np.asarray(x, dtype=float)
    factor = 10.0 ** digits
    return np.sign(x) * np.ceil(np.round(np.abs(x) * factor, 9)) / factor + 0.0

def excel_rounddown(x, digits=0):
    x = np.asarray(x, dtype=float)
    factor = 10.0 ** digits
    return np.sign(x) * np.floor(np.round(np.abs(x) * factor, 9)) / factor + 0.0

def excel_mround(x, multiple):
    return np.round(excel_round(np.asarray(x, dtype=float) / multiple, 0) * multiple, 10)

print(excel_roundup([2.675, -0.125], 1).tolist())     # [2.7, -0.2]
print(excel_rounddown([2.675, -0.125], 1).tolist())   # [2.6, -0.1]
print(excel_mround([0.125, 2.675], 0.05).tolist())    # [0.15, 2.7]

Note that ROUNDUP is not np.ceil: for negative numbers np.ceil(-0.12) goes towards zero, while ROUNDUP(-0.12, 1) gives -0.2. Working on the absolute value and restoring the sign reproduces Excel. MROUND with a price step of 0.05 is the usual "round to the nearest five cents" rule; the final np.round(..., 10) removes the tiny error that multiplying by 0.05 introduces. Excel's MROUND returns an error when the number and the multiple have different signs; this version does not check, so add a guard if negative prices can occur.

Step 5 — Keep rounded parts adding up to the total

Rounding each line and then summing can disagree with rounding the sum. Three lines of 10.005 each round to 10.01, summing to 30.03, while the unrounded total 30.015 rounds to 30.02. On an invoice that one-cent gap is a question from the customer. Allocate the difference to the lines with the largest remainders:

def allocate(amounts, digits=2):
    amounts = np.asarray(amounts, dtype=float)
    factor = 10 ** digits
    target = int(excel_round(amounts.sum(), digits)[()] * factor + 0.5 * np.sign(amounts.sum()))
    floors = np.floor(np.round(amounts * factor, 9)).astype(int)
    shortfall = target - floors.sum()
    order = np.argsort(-(np.round(amounts * factor, 9) - floors), kind="stable")
    floors[order[:shortfall]] += 1
    return floors / factor

lines = [10.005, 10.005, 10.005]
print(excel_round(lines, 2).sum().round(2), allocate(lines).tolist(), allocate(lines).sum().round(2))
# 30.03 [10.01, 10.01, 10.0] 30.02

Each line is rounded down to the cent, and the missing cents go one at a time to the lines that lost the most. The parts now add up to the correctly rounded total. This version assumes positive amounts, which covers invoice lines and cost allocations; handle credits separately.

Step 6 — Round at the right moment

Excel users often round only in the display — a number format with two decimals — while the cell keeps full precision, and later formulas use the full value. Rounding a pandas column early and then calculating with it gives different results. Decide where rounding belongs:

unit_price = pd.Series([0.125, 2.675])
qty = pd.Series([1000, 400])
line_full = excel_round(unit_price * qty, 2)
line_rounded_price = excel_round(excel_round(unit_price, 2) * qty, 2)
print(line_full.tolist(), line_rounded_price.tolist())   # [125.0, 1070.0] [130.0, 1072.0]

Rounding the unit price to cents before multiplying by the quantity adds five euros to the first line and two to the second. Neither is wrong in itself — some price lists really are defined in whole cents — but the script and the workbook must make the same choice. Match the workbook: if its formulas wrap ROUND around intermediate results, round at the same points; if it only formats cells, keep full precision and round when writing — or leave the values unrounded and set a number format, as shown in Format Numbers in Thousands and Millions in Excel.

Common pitfalls

• Using Python's round in a loop. It is half-to-even and also sees the stored binary value. Neither matches Excel.
• Rounding before aggregating. Sums of rounded values drift from rounded sums. Aggregate first unless the business rule says otherwise.
• Comparing floats with ==. After rounding, compare with a tolerance or compare integer cents.

Conclusion

pandas rounds exact halves to even and works with the stored binary value; Excel rounds halves away from zero and treats values as 15 significant digits. Reproduce Excel with a vectorised function that removes floating-point dust before applying the half-up rule, test it against Decimal, use sign-aware versions of ROUNDUP, ROUNDDOWN and MROUND, and allocate pennies by largest remainder when rounded parts must add up to the total.

Frequently asked questions

Why does pandas round 2.5 to 2? pandas and NumPy use round-half-to-even, also called banker's rounding, so exact halves go to the nearest even digit — 0.5 to 0, 1.5 to 2 and 2.5 to 2. Excel's ROUND always rounds halves away from zero, giving 1, 2 and 3.

Why does Python round 2.675 to 2.67? The float 2.675 is stored as slightly less than 2.675, so a naive rounding goes down. Excel compensates by working with 15 significant digits. Rounding the scaled value to a few decimals before applying the half-up rule reproduces Excel's 2.68.

Does Decimal with ROUND_HALF_UP match Excel? Yes, if you build each Decimal from the value's 15-significant-digit text rather than from the float itself. It is exact but slow for large columns, so use it to test a vectorised version.

Why do my rounded line items not add up to the rounded total? Each line loses or gains a fraction of a cent, and those errors accumulate. Allocate the difference to the lines with the largest remainders so the parts add up to the rounded total exactly.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions translated to pandas.
• Running Totals and Year-over-Year Growth in pandas — calculations where rounding order matters.
• AVERAGEIFS, MAXIFS and MINIFS Equivalents in pandas — more formula translations checked against the workbook.
• Convert Excel Text Columns to Numbers with pandas — getting clean numbers to round in the first place.