A price list in Excel: the figures that are not what they look like

13 October 2026 · Questa pagina è disponibile in inglese.

Kristel Mägi

A price list in Excel looks like a table and behaves like one until a machine reads it. The prices carry four decimals, the codes are sometimes text, one code appears twice at two pack sizes, and the file's own dates are often invented by the program that saved it.

Price Double Check reads these files for a living: a buyer forwards a list and an invoice, and the email that comes back names the lines where the two disagree. Everything below is a reason that reply has to be arithmetic rather than a diff, and that it has to be run on the file the supplier actually sent.

The workbooks behind this service are ordinary. Three price lists from one manufacturer, all built on the same sheet, the column headings sitting in row seven and the goods running from row eight to row 255, of which 177 rows carry an item code. Nothing exotic, no macros, no pivot tables. Every problem below came out of files that a buyer would describe as clean.

The number in the cell is not the number on the screen

The first one is the expensive one. A cell showing 1.176 can hold 1.1760000000000002, because that is what a binary floating-point number does to a decimal fraction, and the workbook stores the value rather than the appearance. Compare two lists cell by cell without rounding at the point of reading and the comparison reports changes that nobody made: across a single pair of these files it produced 43 price changes that did not exist.

Forty-three is worth sitting with. A buyer who opens a report of 43 increases, spot-checks three, finds all three identical in both files and closes the report has learned, correctly, that the tool is wrong.

The fix has to be unconditional: round every price as it is read, before anything is compared with anything.

Four decimals in the list, two on the invoice

These lists quote to four places — 0.40425, 1.173375 — and invoices are issued to two. So the comparison is never equality. It is equality after rounding, half-up, with a cent of tolerance per unit, and the rounding rule is a decision rather than a detail of reading the file. Truncate instead of rounding half-up and one line in the set, listed at 2.205 and billed at 2.210, becomes an overcharge on every invoice it appears on. Three false alarms per invoice is the naive version of this service, and the method that avoids them is set out in how to check an invoice against a price list.

A code is not always a number

The item code is the only key worth joining on, because the descriptions do not match: 335 of 442 invoice lines in this set carried wording that differed from the price-list row describing the same goods. So everything depends on the code, and the code has its own ways of hiding.

The second row of that table is a single cell in a single workbook. One code was stored as a string beginning with a tab character rather than as a number, so a shaker that appeared on invoices would never have found its price-list row. It was found by asking why one line out of 177 had no basis, which is the only way such things are found.

One code can mean two products

Two codes in this list appear twice: once near the top of the sheet and once about 180 rows further down, at the same price but with different packaging — 12 units against 13 tablets — and a different number of units per case, one against twelve. Taking the first match is safe here only because the two prices agree. When they disagree it is a coin toss presented to the buyer as a finding, and the honest output is to say that the list answers the question twice.

There is no single discount to derive

A manufacturer's sheet usually carries more than one price column: a recommended retail price, a price for retailers, a price for distributors. The temptation is to treat one as a percentage of another and derive the rest. Across the 177 coded rows of this list the ratio between the distributor column and the retailer column takes at least six distinct values: 0.7350 on 29 rows, 0.7875 on 29 rows, something close to 0.735 with rounding noise on about a hundred more, and then 0.79154, 0.89224 and 0.8931 on their own.

There is no contract discount in that file. The column is the price, absolutely stated, row by row. Which column is yours is a fact about your arrangement with the supplier and has to be configured, not inferred from who sent the file — and a distributor forwarding a list to its own retailers normally deletes its buying column first, so the file you hold is not the file the manufacturer wrote.

The file does not know when it was sent

Spreadsheet software records a modification timestamp in the workbook, and reading tools report it. When the property is absent — which happens whenever the file was written by something other than Excel — the reader supplies the current time instead. Seven of the ten workbooks in this set had no stored date, so a tool that trusted the property would have dated all seven lists to the day it read them.

That matters because the whole question is which list was in force when the goods moved, as the list in force on the delivery date sets out. A list whose date is today is a list that is always current and never wrong. Compare the stored property against the file's own timestamp on disk and take the earlier of the two.

What the list does not say at all

The gaps are as structural as the traps. None of these three price lists declares:

• the currency — euros had to be established from the contract and the invoices;
• a VAT rate, so every figure has to be treated as net until something else says so;
• a quantity threshold, so a volume price cannot be told from a flat one;
• a validity period or a version number, so two files are ordered only by their arrival;
• a discount percentage, or a per-line delivery term — one footer reads EXW and nothing else.

The invoice is no more generous: it prints no packaging and no barcode. A supplier that reduces a pack size and keeps the price the same is therefore invisible on any single invoice, and visible only by comparing one price list against the next. Four such reductions sat in this set.

The file name is not evidence

One workbook in the set is named for a month and a version and is, at the same time, an order with quantities filled in and a newer price list than any file called a price list. Another, a stock proposal sent in July, already carried the price that only reached the official list in October. Both are price-bearing documents with the wrong name on them, which is why a reader has to look for price columns in every attachment a buyer forwards and judge a file by what is in it rather than by what it is called.

Forward one invoice and the price list it should have been billed on, in whatever state the supplier sent them, and the comparison comes back by email with the lines that differ and what each one costs.