The Bonus Model That Breaks on Fill-Down and Won't Reconcile
You maintain Q3_Bonus_2024.xlsx for a 40-person sales unit. Column A holds names, B department, C base salary, D performance score (0–100, one decimal), and E payout rate. The rate bands live on a separate sheet named Rates, in A2:B6: 0→2%, 80→4%, 90→7%, 95→10%, 100→15%. Employee data occupies rows 2 through 41. Cell E2 holds =VLOOKUP(D2,Rates!$A$2:$B$6,2,TRUE) and F2 holds =C2*E2; both were filled down to row 41. Three symptoms appeared this quarter: E41 returns #N/A even though D41 shows 97.5; an employee with a score of exactly 90.0 receives 4% instead of 7%; and F42 =SUM(F2:F41) returns 41,300 while Finance reports 41,120 for the same population. Last week a new hire was inserted by right-clicking row 2 and choosing Insert, and the sheet was then re-sorted by department.
1. Diagnose the #N/A in E41 and the wrong band for the 90.0 employee. Are they the same fault? What do you change?
Read the worked answer
They are separate faults that both surface through VLOOKUP.
#N/A at E41. Inserting a row above row 2 pushed the original row 2 down, but if any part of the Rates reference was left relative (for example A2:B6 rather than $A$2:$B$6), the filled-down formulas now point at the wrong block or run off its end. Click E41 and press Ctrl+[ to jump to what it actually references; that shows immediately whether the range drifted. The other standard cause is a score stored as text, which never matches a numeric lookup: test with =ISNUMBER(D41). Fix by anchoring both sheet and range, or better, select Rates!$A$2:$B$6 and name it RateBands, then use =VLOOKUP(D2,RateBands,2,TRUE). Named ranges do not drift when rows are inserted.
The 90.0 employee. With TRUE, VLOOKUP returns the largest band value less than or equal to the lookup value, so 90.0 must return 7%. Getting 4% means the first-column values are not in ascending order - approximate match stops at the first value it finds that is larger and returns the row above. 90 appearing out of sequence, or a stray blank or duplicate in first position, produces exactly this. Sort Rates!A2:B6 ascending (or replace the lookup with =XLOOKUP(D2,RateBands[Score],RateBands[Rate],,-1), which does not require sorted data) and re-verify the boundary rows 0, 80, 90, 95, 100 individually.
2. Finance's 41,120 differs from your 41,300. Explain the 180 difference and show how to make the workbook agree with Finance.
Read the worked answer
Two different arithmetic operations are being compared. Column F stores full-precision products: 3150 × 7% = 220.5. Finance's figure is the sum of payouts after each individual payout has been rounded to the nearest 10.
Summing rounded rows and rounding a summed column are not interchangeable. Each row can drift by up to 5, so 40 rows can drift by up to 200; the observed 180 sits inside that envelope and is exactly the kind of difference a rounding-population mismatch produces.
To match Finance, change F2 to =MROUND(C2E2,10) (or =ROUND(C2E2/10,0)*10) and fill down. The sum should then read 41,120.
If it still does not, check two things before touching anything else:
- Display is not value. If F were formatted to 0 decimals, 220.5 would display as 221 but still sum as 220.5. Number formatting never closes a 180 gap.
- Hidden rows and text. =SUM ignores hidden rows only if you used SUBTOTAL; =SUM includes them. Confirm no rows are filtered out and no score is stored as text.
Reconcile openly rather than silently: add a residual column =C2E2-MROUND(C2E2,10) and confirm it totals −180. Then record the policy in the sheet header - rounding at payout level is what people are actually paid; rounding the aggregate is more mathematically accurate. Pick one and state it.
3. The workbook will be reused every quarter by a colleague who has never seen it. What structural changes reduce the chance of a repeat?
Read the worked answer
Fix the fragility rather than the symptoms.
- Convert the employee range to a Table (Ctrl+T), named BonusData. Formulas that reference table columns expand automatically when rows are inserted, so a new hire no longer breaks anything downstream.
- Name the band table RateBands and switch to XLOOKUP with match_mode −1: =XLOOKUP([@Score],RateBands[Score],RateBands[Rate],"Not rated",-1). It does not require ascending sort, has an explicit not-found argument instead of #N/A, and reads better than four commas and a TRUE.
- Guard the input. Data Validation on D: decimal, between 0 and 100, with an input message. A text-entered score is the most common way the lookup fails.
- Lock the formulas. Unlock D and C, protect the sheet without a password stored only in someone's head - or with one recorded in the team vault - so the rate column cannot be hand-edited.
- Test the boundaries. Keep a small test block listing 0, 79.9, 80, 89.9, 90, 94.9, 95, 99.9, 100 and the rate each should return. One glance per quarter confirms the bands still work after any edit, which is faster than discovering the error in a payroll dispute.