The €40,000 formula error nobody caught until the year-end close

In March, an exporting house adds two new cost lines to its deal template —a carrier surcharge and fumigation of the goods at origin— and types them into the two rows below the last one. The margin formula is still =SUM(D6:D17) and the new rows are 18 and 19. For nine months those lines are paid, shown on screen and left out of the GP: €400 per container × 100 containers = €40,000 of margin that never existed, found when the year was reconciled with the accounts.

The case is reconstructed: it is not one of our customers or any specific company. We built it around a fault that keeps turning up in trading spreadsheets —the first one in the afternoon audit of your spreadsheet— with illustrative, rounded amounts, so that every figure can be redone with a calculator. Below is how it happened, why Excel gave no warning and the three checks that would have caught it in April rather than January.

The template and the March change

The house is based in Spain, keeps its books in euros, buys pulses from growers in Peru and exports them from Callao to European customers, sold CFR port of destination: about 130 twenty-tonne containers a year. Under CFR, the exporter pays export clearance and freight to the port of destination, surcharges included, and its sales contracts also put on it the fumigation and phytosanitary certificate the destination country requires; whatever happens at destination is paid by the customer.

Each deal is an Excel tab copied from a template, which the house calls the deal sheet. At the top, the sale (D3), the purchase (D4) and the GP in the header (G3); below, twelve cost lines in rows 6 to 17, with the description in column B and the deal amount in column D. The GP is a single formula:

=D3-D4-SUM(D6:D17)

D3: sale   D4: purchase   D6:D17: the twelve cost lines

At the end of March, the head of operations adds two lines to the template that until then went to overheads: the carrier’s emissions surcharge, which travels with the freight (€120 per container in this example), and fumigation at origin with its phytosanitary certificate (€280). They go where almost anyone would put them: in rows 18 and 19, just below the last one. From that day on, every new deal sheet is copied from that template.

Why did Excel not extend the sum?

Because the rows were not added inside the range but after it. A SUM over a range adjusts itself when you insert or delete rows, but Microsoft states the condition on its SUM function page: it updates automatically “as long as you’re not outside of the range referenced in the formula”. Rows 18 and 19 were outside.

• Inserting inside the range: right-click row 17 and choose Insert, and the new row goes in above row 17, inside D6:D17, and the formula becomes =SUM(D6:D18).
• Typing or inserting just below: row 18 is already outside D6:D17. The formula stays as it was and nothing shows an error.
• The in-between cases: on its page about detecting formula errors, Microsoft says a formula “may not” automatically include data inserted between the original range and the cell holding the formula. May not, not will not, and that is the practical conclusion: do not rely on Excel doing it or on it not doing it.

In Google Sheets, the official help does not describe what a plain reference such as D6:D17 does when you add rows below it; it only documents that table references “update when you add or remove data from the table”. Test it on a copy of your template.

And the green triangle? The rule “Formulas which omit cells in a region”, according to Microsoft’s documentation, compares the range with the cells holding data next to the formula’s cell: its example is a =SUM(D2:D4) in D8 with numbers in D5, D6 and D7. Here the GP sat in the header, far from the cost lines, and does not fit that pattern. Besides, many spreadsheets have it switched off.

Nine months later: how it surfaced

At the year-end close, the head of finance reconciles each deal with the accounts: what suppliers invoiced and was allocated to the trade against the cost that goes into the deal sheet’s GP, that is, sale − purchase − GP. Adding up the whole of column D would have reconciled; the cost the GP uses did not: the deal sheets come out €40,000 short, and the gap has three features that give it away:

1. 1It started in April. Deals from January to March reconciled to the euro; every later one did not.
2. 2It was proportional to containers. Every deal was out by exactly €400 per container: it was not a lost invoice, it was a rule.
3. 3It matched two cost lines. 120 + 280 = 400. In any deal sheet from April onwards, both rows were there with the right amounts. One click on G3 was enough to see that the formula ended at row 17.

Lines outside the range = 120 + 280      =    €400/container
Containers affected     = 33 + 33 + 34     =    100
Overstated margin       = 400 × 100       = €40,000

Per tonne (20 t per container): €40,000 ÷ 2,000 t = €20/t

Twenty euros a tonne looks small until you set it beside the margin. In the quinoa container broken down line by line, the GP is €88 a tonne: an error of €20/t is more than a fifth of it. The invoices were paid: what was wrong was the margin in the report, and any quote worked out with that template.

The three checks that would have caught it

None of them requires a new tool, and each one attacks the fault from a different side.

1. A check in the deal sheet: count the lines and sum another way

One cell next to the GP that asks two questions: whether the number of named lines in column B matches the number of amounts that go into the total, and whether column D summed over a generous range gives the same as the GP’s sum. If either answer is no, the deal sheet says “CHECK”.

=IF(AND(COUNTA(B6:B40)=COUNT(D6:D17),SUM(D6:D40)=SUM(D6:D17)),"OK","CHECK")

The range D6:D17 must be the same one the GP uses. In this case, the first April deal sheet, with four containers, would have shown 14 named lines against 12 amounts in the total, and a €1,600 gap between the two sums: €400 for each container in the deal. Two details: type 0 in lines with no cost instead of leaving them empty, because COUNT does not count blank cells, and remember that D6:D40 is a range too: give it plenty of room.

2. A table with a total row instead of a range

The real fix is to have no range that can fall short. Select the cost lines, turn them into a table with Ctrl+T and switch on the Total Row. According to Microsoft, its cells start empty and, when you choose Sum from the column’s drop-down, Excel writes SUBTOTAL(109,…) with a reference to the table column, not to a block of cells. The GP becomes =D3-D4-SUM(CostTable[Amount]), and those structured references adjust on their own when the table gains rows. The condition is to add new cost lines inside the table, with Tab in the last cell or with “Insert table rows”: a line typed below the total row stays outside, as before. In Google Sheets, Insert → Table and =SUM(CostTable[Amount]) do the same job: according to its help, table references update when data is added or removed.

3. Reconcile each deal at settlement, not the year at the close

The fault surfaced in January because that was the first time anyone compared the deal sheets with outside data. That reconciliation can be done deal by deal, as soon as the invoices arrive. It is the same reconciliation that separates the margin in your spreadsheet from the one you collect, and here it would have fired as soon as the first April deal was settled: with four containers, a €1,600 gap, exactly the two cost lines.

What Granel does with this, and what it does not

In Granel, a deal’s cost lines are not rows in a sheet but a list, and the GP is worked out by going through all of it: a line you add —by hand or with the standard template of twelve— goes into the margin, because there is no range to fall short. Each line keeps the estimated amount and, at settlement, the actual one, and the settlement tab shows them side by side. The P&L calculator, free and with no sign-up, does the same sum with up to 30 cost lines.

What Granel does not do is reconcile your deals with your accounts or read your invoices: someone enters the actual amount. Nor does it repair a broken spreadsheet on import: if your deal sheet works out the GP with a SUM below the cost lines, the importer counts only the rows that formula covers; a line the SUM left out stays out, and the preview does not flag it because both GPs match. Before importing, run check 1.

Frequently asked questions

Why does my Excel SUM not include the new rows?

Because you added them outside the range it sums, usually just below the last row. Excel extends the reference when you insert rows inside the range, not when you type after it. Turn the cost lines into a table and sum the table column.

How do I check that the cost total includes every line?

Compare the number of named lines (COUNTA over the descriptions column) with the number of amounts that go into the sum (COUNT over the same range as the SUM). If they differ, something is missing from the total.

Does the same happen in Google Sheets?

Google’s help does not document how a plain reference behaves when you add rows below the range. It does document that table references update when data is added or removed, so the fix is the same: use a table.

How often should deal costs be reconciled with the accounts?

Deal by deal, as each one is settled, not once a year. A template error repeats in every deal sheet copied from it: the sooner one is compared with the invoices, the fewer deal sheets carry the same fault.