Rounding and Formatting Numbers for Finance Readers

A finance reader opens a workbook and checks three things in the first minute: do the columns add up, do the totals match what they already know and are the numbers formatted the way they are used to? A report that fails the first check by one cent on a line loses credibility on the other thousand lines. Rounding is where that happens, and it happens because formatting and rounding are separate operations that look the same on screen. This article separates them, shows where to round and how to format, and handles the awkward case of a displayed total that does not equal the sum of its displayed parts. It supports building Excel reports from SQL queries people trust.

Format is not rounding

A number format controls how a stored value is displayed. The cell holding 12.3456 with the format 0.00 displays 12.35 and still contains 12.3456. A formula that sums a column of such cells adds the stored values, not the displayed ones. That is why three cells holding 0.4 and formatted as whole numbers show 0, 0 and 0, while their total shows 1. The arithmetic is correct and the page looks wrong.

There are two honest responses. Treat the stored value as the truth and say so ("totals are calculated on unrounded values"), or round the stored value so that what you see is what you add. The second is only right when the business rule says the rounded figure is the real amount. An invoice line, for example, is legally an amount in whole cents, and the invoice total is the sum of the rounded lines. A forecast is not, and rounding it early adds error.

Store exact values

Choose a storage type that does not introduce error of its own. Binary floating point cannot represent most decimal fractions, so 0.1 + 0.2 is 0.30000000000000004. For money:

• In PostgreSQL, use numeric(12,2) (or a larger precision where needed), never real or double precision.

• In Python, use decimal.Decimal for calculations, or integers of minor units (cents).

• In a data frame, a float64 column is acceptable for display of results you rounded, and dangerous for accumulating amounts.

Writing cents as integers is the most dependable approach for transaction data, and a number format can show them as currency by dividing in the format only if you keep a consistent scale, which Excel formats cannot do. So convert to decimal units when writing the sheet, and round at that point.

from decimal import Decimal, ROUND_HALF_UP

def money(cents: int) -> Decimal:
    return (Decimal(cents) / 100).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)

Round once, at a named point

If a figure is rounded, round it in one place and say where. The usual choices:

In the query, with round(x, 2) on numeric values. PostgreSQL rounds numeric half away from zero, so round(2.345, 2) is 2.35 and round(-2.345, 2) is -2.35. The rounding of a double precision value is different and platform dependent, which is one more reason to use numeric.

In the generator, with Decimal.quantize and an explicit rounding mode.

In the workbook, with ROUND(x, 2) formulas. Use this only when the sheet is the system of record for that number, and be consistent about whether the total is a sum of rounded parts or a rounded sum.

Do not round at several stages. Rounding a rate, then multiplying, then rounding the product compounds the error and makes the result depend on the order of steps. Keep full precision until the last step, then round.

Know the rule that applies

"Round half up" is what most people expect and not what every tool does. Python's built-in round() uses banker's rounding (half to even): round(2.5) is 2, round(3.5) is 4, and round(0.125, 2) is 0.12 partly because of binary representation. Excel's ROUND rounds half away from zero. PostgreSQL's round(numeric) also rounds half away from zero. A report generated in Python and checked in Excel can differ on exact halves unless you choose the rule in code, as with ROUND_HALF_UP above.

Test a tie case. Put 2.345 and -2.345 through the path and confirm that the output is 2.35 and -2.35 if that is the rule. Finance auditors ask which rule applies, and the answer should be in the README (rounding rules that survive audit goes deeper on defensible rules).

Pick a format per column type

Consistency matters more than a particular choice. A set that readers find familiar:

The trailing comma in the last format divides the displayed value by one thousand; state the unit in the header ("USD thousands"), because a reader who does not see it will misread by a factor of a thousand. The Microsoft documentation on number format codes lists the full syntax.

Use the same number of decimals within a column. A column that mixes 12, 12.5 and 12.50 looks careless and is hard to scan. Align numbers to the right, which is the default for numeric cells, and leave headers aligned with their content.

Show negatives the way your readers expect. Accountants often prefer parentheses; others prefer a minus sign. Pick one for the whole pack. Color negatives red only if the red is also backed by the sign, so the meaning survives a black and white print.

Apply formats with the writer, not with strings

Formatting a number into a string, f"{x:,.2f}", produces text that cannot be summed, sorted correctly or used in a formula. Keep the cell numeric and apply a format:

money = wb.add_format({"num_format": "#,##0.00;[Red](#,##0.00)"})
ws.set_column("D:F", 16, money)

Reserve string formatting for the places where the output really is text, such as a Markdown table (converting Excel to Markdown and HTML tables) or an email body.

When the displayed parts do not add up

Suppose a revenue split across three products is 33.333...% each, and you display whole percents: 33%, 33%, 33% and a total of 100%. Readers add the parts and get 99. The same happens with currency allocated across lines.

There are three approaches, and you should choose one deliberately:

1. Note it. Add "Figures may not sum due to rounding" under the table. It is honest and the standard in published reports, and it costs a finance reader's confidence if used on a small table where they can see the problem.

2. Round the parts, derive the total. The total is the sum of the rounded parts. This makes the page add up and the total can drift from the true figure by up to half a unit per line.

3. Allocate the remainder. Round each part down, compute the difference to the rounded total and give the leftover units to the lines with the largest fractional parts (the largest remainder method). The page adds up and the total is exact.

def allocate(parts, total_cents):
    floors = [int(p * total_cents) for p in parts]          # parts sum to 1
    remainders = sorted(range(len(parts)), key=lambda i: -(parts[i] * total_cents - floors[i]))
    for i in remainders[: total_cents - sum(floors)]:
        floors[i] += 1
    return floors

Allocation is what invoicing systems do to split a discount or tax across lines, and it is the right tool when a legally exact total is divided. For a management pack, option one is usually enough, provided it is stated.

Check it

Build checks into the job: the sum of the displayed column equals the displayed total where the policy says it must, within a tolerance of one unit of the last decimal where it says otherwise. Compare the workbook total to the ledger total to the cent. Include a tie case in your test data, such as 2.345, 0.125 and -2.345, and assert the output values. Read the saved workbook back with openpyxl and check that numeric cells are numeric and that the format strings are what you set (testing a generated Excel report).

When displayed numbers must not be rounded

Rounding for display is wrong when the number is an identifier: account numbers, invoice numbers and long IDs are text, because a number format on a 20-digit value silently loses digits beyond fifteen (Excel number precision and the 15 digit limit). It is wrong in data sent to other systems, which should receive full precision and do their own presentation. And it is wrong for rates and small quantities, such as 0.0004 per unit, where two decimals would show zero. Choose the decimals the data needs, not the ones the template had. For the money handling in the database side, see the PostgreSQL documentation on numeric types.