The Banded Price List That Charges the Wrong Band at the Boundary
A regional distributor publishes a banded price list: 0–99 units at $12.40, 100–499 at $11.85, 500–999 at $11.10, 1,000–2,499 at $10.60, and 2,500+ at $9.95. The list sits on a sheet named Rates in A1:C7 - a header row plus five bands - with the maximum quantity of each band in column A and the unit price in column C.
On the Orders sheet, 400 rows carry an order number in column A, a customer in column B, a product in column C and the ordered quantity in column D. E2 contains =VLOOKUP(D2,Rates!$A$2:$C$7,3,TRUE) and is filled down to E401.
Two symptoms are reported. First, 58 rows show #N/A, and every one of them has a blank or zero quantity. Second, 23 rows are priced one band high - the customer is charged the cheaper-quantity band's price - and all 23 have a quantity of exactly 100, 500, 1,000 or 2,500. The prices themselves have been checked and are correct. The workbook raises invoices, and the most recent price revision added the 2,500+ band.
1. Diagnose both symptoms. Name the exact mechanism behind each, and explain why only boundary quantities are mispriced.
Read the worked answer
The table is indexed on the wrong edge of every band. Rates stores 99, 499, 999, 2499 and 99999 - the maximum quantity of each band - but VLOOKUP(...,TRUE) does not ask "which band contains this quantity?". It runs a binary search over column A and returns the row with the largest value that is less than or equal to the lookup value.
Symptom 1 - 58 rows of #N/A. Those rows have a blank or zero quantity. Zero is smaller than every entry in the lookup column (the smallest is 99), so the search runs off the bottom of the table and Excel reports #N/A. The blank cells are data-quality failures that the formula has converted into an error instead of flagging.
Symptom 2 - 23 rows one band high. These are quantities of exactly 100, 500, 1,000 or 2,500. For a quantity of 100, the largest stored value ≤ 100 is 99, so the row returned is the 0–99 band and the customer is charged $12.40 instead of $11.85. Every exact boundary resolves to the band beneath it. A quantity of 250 or 700 never touches a boundary and is priced correctly, which is why the faulty rows are scattered but always on round numbers.
Both faults are structural: no price is wrong, and re-checking the arithmetic will never find them. The invoice risk is the second symptom - every affected customer has been overcharged.
2. Redesign the rate table and the formula so the bands are unambiguous, blanks do not become errors, and the open-ended top band stays correct when a 12,000-unit order arrives.
Read the worked answer
Store the lower bound of each band and drop the sentinel.
| A MinQty | B Band | C UnitPrice |
|---|---|---|
| 0 | 0–99 | 12.40 |
| 100 | 100–499 | 11.85 |
| 500 | 500–999 | 11.10 |
| 1000 | 1000–2499 | 10.60 |
| 2500 | 2500+ | 9.95 |
The top band needs no 99,999 row: with TRUE, any quantity ≥ 2,500 matches the last row, so a 12,000-unit order is priced correctly without maintenance.
Formula in E2, filled down:
=IF(COUNT(D2)=1, VLOOKUP(D2,$A$2:$C$6,3,TRUE), "CHECK QTY")
COUNT(D2)=1 is true only for a genuine number, so it traps blanks, spaces and text that ISBLANK alone would miss. Convert the range to a table (Rates[MinQty], Rates[UnitPrice]) or a defined name, so the reference grows when a band is added instead of silently excluding the new row.
XLOOKUP(D2,Rates[MinQty],Rates[UnitPrice],,-1) removes the column-count argument and reads better, but it does not fix anything on its own: with match mode −1 against a table of upper bounds it reproduces exactly the same off-by-one-band error. The fix is the layout, not the function.
Finally, sort the MinQty column ascending and keep it sorted - approximate match assumes it, and nothing in Excel enforces it.
3. Describe how you would prove all 400 rows are now correct in under ten minutes, and what evidence you would keep.
Read the worked answer
Use an independent recomputation, not a second copy of the same lookup.
- Helper columns on Orders:
G2: =MAXIFS(Rates!$A$2:$A$6,Rates!$A$2:$A$6,"<="&D2)- the band minimum that should apply.H2: =IFERROR(INDEX(Rates!$C$2:$C$6,MATCH(G2,Rates!$A$2:$A$6,0)),"NO BAND")- the price that minimum implies.I2: =IF(COUNT(D2)=1,IF(E2=H2,"OK","MISMATCH"),"BAD QTY").
=COUNTIF(I2:I401,"OK")must equal the count of numeric quantities; every other value is a row to open.- Test the edges deliberately rather than sampling: 0, 1, 99, 100, 499, 500, 999, 1,000, 2,499, 2,500, 12,000 and one blank, pasted into a 12-row test block with expected prices beside them.
=SUMPRODUCT(--ISERROR(E2:E401))must be 0, and no returned price may fall outside $9.95–$12.40.- Cross-check the money: total invoiced value before and after the fix should differ by exactly the sum of the 23 overcharges.
Evidence to keep: the 81 affected order numbers (58 + 23), the credit or re-invoice value, the test-block output with the date and the formula used, and a note describing the table restructure so the next editor knows why column A is a minimum. Re-run steps 2–4 after every price revision - this failure returns the moment somebody appends a band without re-sorting, and the test block is what catches it.