The business rule was one line. Any discount of 20% or more is a markdown.
My markdown totals were low, my growth rate was high, every formula in the sheet was correct, and nothing on the screen looked wrong.
What the week was
Week five of i.c.stars was Geek Week. Fifteen tasks against a sales database, each with an expert time attached. Thirty seconds for the shortest, three and a half minutes for the longest. Hitting the time was the only way to pass, so finishing was not the bar. Finishing fast enough was.
During the timed runs: no AI, no mouse, no trackpad, no copy and paste inside Excel. Everything through keyboard shortcuts. You could use whatever you wanted while learning. In the chair, it was you and the keys.
I finished all fifteen inside the expert times by Friday.
The dataset is Northwind, Microsoft's sample database, so anyone can pull it down and run every query in this post. Orders, products, categories, discounts, and the employee who made each sale.
The framing is a company whose sales grew 73% while gross profit did not follow. The CEO suspects discounting is eating the margin and is weighing whether to lay off one of the sales reps. The analysis is supposed to tell her whether she is right.
I spent two years as a sales rep carrying 55 accounts before any of this. I have been the person in that data, discounting to close something before the end of a month. So the question was not abstract to me.
The number that mattered
Not discounts in general. Markdowns specifically, because the business rule splits them: 5 to 9% is low, 10 to 19% is medium, and 20% or higher is a markdown.
A lookup table does the sorting and VLOOKUP applies it to every row. The TRUE at the end is the part I had to look up. It means range lookup, so the table only needs the bottom of each band rather than every possible value:
=VLOOKUP(E2,discountcategory,2,TRUE)
Then a report sums only the rows that came back labeled markdown:
=SUM(IF(Order_Date >= B$1,
IF(Order_Date <= B$2,
IF(Category_Name = $A3,
IF(discount = "markdown", SaleDiscount)))))
Run it and the answer is loud. Markdown dollars last quarter came to about $12,200, up 557% over the same quarter the year before.
The CEO's instinct was right. The answer to her other question is no. A later task ranked discounts by the employee who gave them over the last twelve months, and the three biggest were King, Peacock, and Fuller. Fuller is the Vice President of Sales. When the head of sales is near the top of the list, that is not a rep you fire. That is a pricing problem.
That is the number the whole week is built to produce. And the first version I had was wrong.
Why 20% is not always 20%
Here is one real row from my data. A Beverages line, $14.40 a unit, quantity 45. Sale price $648.00, discount $129.60. That is exactly 20% off.
To get the percentage, I divided the discount by the price:
=D2/B2 -> 0.19999999999999998
Excel stores numbers in binary, and 129.6 has no exact binary form. It is held as 129.599999999999994..., so the division lands a hair under 0.2. Northwind stores Discount as REAL, but that was not what bit me. The division was.
That value is less than 0.20. So VLOOKUP checks it against the table, correctly finds it below the markdown band, and files it as medium. The row is real, the discount is real, and it is missing from the one total the executive team asked for.
It was not one row. 30 of 315 markdown order lines landed under the line, and $2,325 in markdowns got filed as medium. A fresh copy of Northwind gives 31. Different copies round differently.
The fix is one function, rounding the percentage before it reaches the lookup so the comparison happens at the precision the business rule actually cares about:
=ROUND(D2/B2,2) -> 0.2
And it was wrong in a way I did not expect. Without the fix, the totals came out too low, but the growth rate came out too high: 585% instead of 557%. The early quarters lost proportionally more than the recent ones, so the baseline shrank faster than the top and the growth inflated. One bug, wrong in both directions.
None of this is an Excel bug. It is how binary floating point works everywhere, and it has been written down since 1991: What Every Computer Scientist Should Know About Floating-Point Arithmetic, David Goldberg, ACM Computing Surveys.
I did not catch this one myself. The AI I was learning with flagged it before I ever ran the report: round first, or some 20% discounts come out as medium. A month ago I would have pasted that in and moved on. This time I did not take its word for it. I went back to the data and checked: 30 rows, $2,325, and a growth rate that was too high.
There is no version of this where I read the formula and spot it. The formula is fine. The error lives in the gap between what a number is and how it is stored, and that gap does not appear on screen.
It kept happening
Once I started looking for that shape, the week was full of it.
A letter where a zero should be. One task wanted the discount formatted as a percentage. I wrote 'PO' with a capital O instead of 'P0' with a zero:
FORMAT(od.Discount, 'PO') -- every row returns the text "PO"
FORMAT(od.Discount, 'P0') -- 0.15 -> 15%
Not an error. Not a blank. A column full of confident nonsense.
Rows in the wrong order. My results had every correct row and still did not match the expected output. Sorting by category only controls the order of the categories. Inside each one, the database can return rows in any order it likes unless you give it another key. Adding product ID and order ID to the sort made it match. The query was never wrong. It was underspecified.
A number that was true and misleading. One employee showed over 3,000% growth in a quarter. That is accurate. He went from about $500 to about $16,000. When the starting number is tiny, the percentage explodes. The math is correct and the conclusion you would draw from it is not, which is its own category of wrong.
Learning to read the errors
The errors that did announce themselves turned out to be the easy half, once I stopped treating every failure as one event called "it broke."
Before I could read them, every failure cost me minutes of guessing. After, most cost seconds. Nearly all of my practice time went here rather than into learning new formulas.
The ones that scared me were the ones with no error at all.
What the time limit was actually testing
I assumed a thirty second limit was testing whether I knew the function. It is not. Thirty seconds is barely enough time to type it. It tests whether you can do it without stopping to think, which is a different thing and you only get it from repetition.
The useful discovery was that rushing made me slower every single time. Every hurried run produced a mistake that cost more to find than the seconds it saved. I hit the times by doing each step deliberately and correctly until the sequence stopped needing my attention, and the speed showed up on its own after that.
The other half is that most tasks did not start from zero. They started from the last report. One array formula, built once, becomes a different report by swapping a single name with Find and Replace. Sales by category becomes discounts by category becomes discounts by employee becomes sales growth by employee. Four reports, three keystroke sequences.
The no-mouse rule is what forced that. Taking the mouse away removed the option of doing things the slow familiar way, so I had to learn Ctrl+G to jump to a named range, Ctrl+Enter to fill a whole selection at once, Ctrl+H to rewrite every formula on a sheet in one pass. I am now faster with those than I would have been if I had been allowed to point at things.
The part I want on the record
Last week I published a piece about shipping something I could not personally defend, because a model wrote the code and I had not verified it.
This week I could use AI to learn, but not in the chair. And when it told me about the bug that mattered, the one that would have handed an executive team the wrong number about the only thing they asked about, I did not just accept it. I measured it.
That is a smaller claim than "I found the bug," and it is the true one. The thing I am actually practicing is not catching every error myself. It is refusing to pass along a number I have not seen come out of the machine, no matter who handed it to me.
Last month I picked a threshold by reasoning about it instead of measuring it. This week I measured something I was told. That is the direction I want this going.
What I would tell somebody doing this next
The dangerous errors are the ones that produce output. A crash tells you where to look. A column of "PO", a total that is low by exactly the rows that got misfiled, a growth rate of 3,000% that is technically accurate, a 20% discount sitting in the medium band: none of those tell you anything. They sit there looking finished.
So the habit worth building is not writing better formulas. It is running the thing and checking whether the answer is the size you expected, before anyone else sees it.
What is the last number you shipped that was wrong and did not look wrong?
I'm back on the client project now.
Top comments (1)
The measured row count is the strongest part of this example. One extra guard: make the rounding precision part of the business rule, not just the display format.
ROUND(D2/B2,2)rounds a fraction to whole percentage points, so a genuine 19.6% discount also becomes 20% and moves into the markdown band.If the source rates are defined to 0.01 percentage point,
ROUND(D2/B2,4)preserves 19.60% while turning the floating-point value just below 0.2 back into 0.2. That precision still needs to be agreed with whoever owns the pricing rule. A small regression table with values just below, exactly at, and just above each threshold would catch both the original error and accidental over-rounding. Also worth keeping the lower bounds sorted: approximateVLOOKUPdepends on that.