ROUND, ROUNDUP, MROUND and CEILING Equivalents in Python

Rounding looks like the one Excel function that needs no translation — Python has round() and pandas has .round(). Then a report rebuilt in Python disagrees with the spreadsheet by a penny here and there, and the reason is that the three do not round the same way. Excel's ROUND rounds halves away from zero: 2.5 becomes 3 and −2.5 becomes −3. Python's round() and pandas round halves to even: 2.5 becomes 2 and 3.5 becomes 4. On top of that, many decimal fractions cannot be stored exactly in binary, so 2.675 is really 2.67499999…, which Python rounds down to 2.67 while Excel shows 2.68. For invoices, VAT and anything reconciled against a spreadsheet, those pennies matter. This page writes Python and pandas equivalents of ROUND, ROUNDUP, ROUNDDOWN, MROUND, CEILING and FLOOR that match Excel, explains each difference, and checks every function against the same formulas recalculated in a workbook. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

LibreOffice (soffice) recalculates the comparison workbook in Step 4; its rounding functions follow Excel's definitions. Everything else uses the standard library's decimal module.

Step 1 — See the three behaviours disagree

import pandas as pd

values = [0.5, 1.5, 2.5, -2.5, 2.675, 1.005, 0.125]
print("Python round:", [round(v) for v in values[:4]], [round(v, 2) for v in values[4:]])
print("pandas round:", pd.Series(values[:4]).round().tolist(), pd.Series(values[4:]).round(2).tolist())
print("repr of 2.675:", f"{2.675:.20f}")

Python and pandas both turn 0.5 into 0, 1.5 into 2 and 2.5 into 2 — banker's rounding, which avoids a systematic upward bias when many halves are rounded, and is the IEEE 754 default. Excel gives 1, 2 and 3. The second problem is visible in the last line: the float closest to 2.675 is slightly below it, so Python's round(2.675, 2) gives 2.67. pandas happens to give 2.68 for that value, because it scales by 100 before rounding and the scaled float lands on the other side — but it still gives 1.0 for 1.005 and 0.12 for 0.125, where Excel shows 1.01 and 0.13. Excel works on the value as displayed, to 15 significant digits, and rounds halves away from zero; a correct equivalent has to do both, and cannot rely on whichever way a particular float happens to fall.

Step 2 — Excel-compatible ROUND, ROUNDUP and ROUNDDOWN

The decimal module rounds exactly, with a choice of rule. Converting the float through its 15-significant-digit representation first removes the binary artefact, the same way Excel does:

from decimal import Decimal, ROUND_HALF_UP, ROUND_UP, ROUND_DOWN

def _dec(x):
    return Decimal(f"{x:.15g}")                       # 2.675 -> Decimal('2.675'), as Excel sees it

def _quantum(digits):
    return Decimal(1).scaleb(-digits)                 # digits=2 -> 0.01, digits=-2 -> 1E+2

def excel_round(x, digits=0, rule=ROUND_HALF_UP):
    q = _quantum(digits)
    d = _dec(x)
    if digits < 0:                                     # round to tens, hundreds, …
        return float((d / q).quantize(Decimal(1), rounding=rule) * q)
    return float(d.quantize(q, rounding=rule))

def excel_roundup(x, digits=0):
    return excel_round(x, digits, ROUND_UP)            # away from zero

def excel_rounddown(x, digits=0):
    return excel_round(x, digits, ROUND_DOWN)          # towards zero, i.e. truncate

print([excel_round(v) for v in (0.5, 1.5, 2.5, -2.5)], excel_round(2.675, 2), excel_round(1.005, 2), excel_round(1234.5, -2))
print(excel_roundup(3.141, 2), excel_roundup(-3.141, 2), excel_rounddown(-3.149, 2))

ROUND_HALF_UP in decimal means "half away from zero", so it handles negatives the way Excel does. The :.15g conversion is the important detail: Decimal(2.675) directly would capture the binary value 2.67499…, while Decimal("2.675") is the number the user typed and Excel displays. Negative digits round to the left of the decimal point — ROUND(1234.5, -2) is 1200. ROUNDUP and ROUNDDOWN in Excel round away from and towards zero respectively, which are ROUND_UP and ROUND_DOWN in decimal — not the same as ceiling and floor for negative numbers: ROUNDUP(-3.141, 2) is −3.15.

Step 3 — MROUND, CEILING and FLOOR

MROUND rounds to the nearest multiple — prices to 5 cents, times to 15 minutes, pack quantities to 12. CEILING and FLOOR round up or down to a multiple:

from decimal import ROUND_CEILING, ROUND_FLOOR

def excel_mround(x, multiple):
    if x * multiple < 0:
        raise ValueError("MROUND needs number and multiple with the same sign (Excel returns #NUM!)")
    m = _dec(multiple)
    return float((_dec(x) / m).quantize(Decimal(1), rounding=ROUND_HALF_UP) * m)

def excel_ceiling_math(x, significance=1):
    s = _dec(abs(significance))
    return float((_dec(x) / s).quantize(Decimal(1), rounding=ROUND_CEILING) * s)

def excel_floor_math(x, significance=1):
    s = _dec(abs(significance))
    return float((_dec(x) / s).quantize(Decimal(1), rounding=ROUND_FLOOR) * s)

print(excel_mround(10.07, 0.05), excel_mround(37, 12), excel_mround(0.125, 0.05))
print(excel_ceiling_math(4.42, 0.25), excel_ceiling_math(-4.42, 0.25), excel_floor_math(4.42, 0.25), excel_floor_math(-4.42, 0.25))

Dividing by the multiple, rounding to a whole number and multiplying back works for any multiple, and doing it in Decimal avoids results like 10.049999999 for 10.05. MROUND breaks ties away from zero like ROUND, and returns #NUM! in Excel when the number and the multiple have different signs, mirrored here with a ValueError. CEILING.MATH and FLOOR.MATH round towards positive and negative infinity by default, so the ceiling of −4.42 to a quarter is −4.25, not −4.5. The older CEILING and FLOOR functions differ for negative numbers and are best avoided in new workbooks.

Step 4 — Check every function against a recalculated workbook

import subprocess
from openpyxl import Workbook, load_workbook

cases = [("ROUND", v, d) for v in (0.5, 1.5, 2.5, -2.5, 2.675, 1.005, 0.125, -0.125, 1234.5, 149.99) for d in (0, 2, -1)]
cases += [("ROUNDUP", v, d) for v in (3.141, -3.141, 2.001, -0.0001) for d in (0, 2)]
cases += [("ROUNDDOWN", v, d) for v in (3.149, -3.149, 2.999) for d in (0, 2)]
cases += [("MROUND", v, m) for v, m in ((10.07, 0.05), (37, 12), (0.125, 0.05), (7.5, 5), (-7.5, -5), (1.23, 0.1))]
cases += [("CEILING.MATH", v, s) for v, s in ((4.42, 0.25), (-4.42, 0.25), (2.0, 1), (0.1, 0.05))]
cases += [("FLOOR.MATH", v, s) for v, s in ((4.42, 0.25), (-4.42, 0.25), (2.999, 1))]
PY = {"ROUND": excel_round, "ROUNDUP": excel_roundup, "ROUNDDOWN": excel_rounddown, "MROUND": excel_mround,
      "CEILING.MATH": excel_ceiling_math, "FLOOR.MATH": excel_floor_math}

wb = Workbook()
ws = wb.active
for i, (fn, v, a) in enumerate(cases, start=1):
    name = f"_xlfn.{fn}" if fn.endswith(".MATH") else fn
    ws.append([fn, v, a, f"={name}(B{i},C{i})"])
wb.save("rounding.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "rounding.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/rounding.xlsx", data_only=True).active
mismatch = [(fn, v, a, calc[f"D{i}"].value, PY[fn](v, a)) for i, (fn, v, a) in enumerate(cases, start=1)
            if abs(calc[f"D{i}"].value - PY[fn](v, a)) > 1e-9]
print(f"{len(cases)} cases compared, {len(mismatch)} mismatches")
print("Python's built-in round disagrees on", sum(1 for i, (fn, v, a) in enumerate(cases, 1)
      if fn == "ROUND" and abs(calc[f"D{i}"].value - round(v, a)) > 1e-9), "of the ROUND cases")

Every case — ties, negative numbers, negative digits, binary-unfriendly values such as 2.675 and 1.005, multiples and significances — gives the same result in Python as in the recalculated workbook, while Python's built-in round disagrees with Excel on a good share of the ROUND cases. Keeping such a comparison as a test protects the functions from a well-meant "simplification" back to round() later.

Applying them to a DataFrame

These functions work value by value, which is fine for invoice totals and a few thousand rows. For a large column, apply them with Series.map(lambda v: excel_round(v, 2)); it is slower than pandas' vectorised .round() but correct, and correctness is the point when the figures must match a spreadsheet to the penny. Where exact decimal arithmetic matters throughout — summing many amounts and then rounding — keep the values as Decimal objects in an object column until the final rounding, rather than converting back to float in between, so the rounding happens on the exact total. And decide where to round: rounding each line and summing gives a different total from summing and then rounding, and the spreadsheet you are matching has made one of those choices — reproduce the same one. The related question of displaying versus storing rounded values is covered in Round Numbers for Excel Output: float_format vs Number Formats.

Common pitfalls

• Using round() to match Excel. It rounds halves to even; Excel rounds them away from zero.
• Decimal(2.675) from a float. It captures the binary error; go through f"{x:.15g}".
• Treating ROUNDUP as ceiling. For negatives, ROUNDUP moves away from zero.
• Float arithmetic for multiples. 10.05 can come out as 10.049999…; compute in Decimal.
• Rounding at a different stage. Line-level and total-level rounding give different totals.

Conclusion

Excel's ROUND rounds halves away from zero on the value as displayed, which neither Python's round() nor pandas' .round() does. Convert values with Decimal(f"{x:.15g}") and quantise with ROUND_HALF_UP for ROUND, ROUND_UP and ROUND_DOWN for ROUNDUP and ROUNDDOWN, divide by the multiple for MROUND, and use ROUND_CEILING and ROUND_FLOOR for CEILING.MATH and FLOOR.MATH. Check them against a recalculated workbook, and round at the same stage as the spreadsheet you need to match.

Frequently asked questions

Why does Python round 2.5 to 2? Python's round uses round-half-to-even, also called banker's rounding, so 2.5 rounds to 2 and 3.5 to 4; Excel's ROUND gives 3 and 4.

How do I round like Excel in Python? Convert the value with Decimal(f"{x:.15g}") and call quantize with ROUND_HALF_UP and the required number of decimal places.

Why does round(2.675, 2) give 2.67? 2.675 cannot be stored exactly in binary and is slightly less than 2.675; converting through its 15-digit text form before rounding gives 2.68, as in Excel.

What is the Python equivalent of MROUND? Divide by the multiple, round to a whole number half away from zero, and multiply back, using Decimal to avoid floating-point artefacts.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions in Python.
• Fix Floating-Point Noise in Excel Output — the binary representation problem.
• Round Timestamps into Time Slots with pandas — rounding times to multiples.
• Build a VAT Return Summary from Transactions — where pennies matter.