The Incentive Sheet That Must Leave in One Hour
A KPO handling a telecom client's outbound process maintains a monthly incentive sheet for 42 agents. The columns are Agent ID, Agent Name, Calls Made, AHT (seconds), Quality Score and Incentive. The incentive rate is looked up from a slab table on a second sheet using VLOOKUP on the Quality Score. The team leader must email the sheet to the client in one hour.
Opening the file, you find: the Total row shows 0 calls and #DIV/0! in AHT; five agents show #N/A in the Incentive column; several Agent IDs display a small green triangle and do not match the slab table; the 'Calls Made' total is lower than the figure the dialler report shows; and two agents are both named 'A. Kumar' with different IDs. A colleague suggests 'just paste everything as values and delete the formula columns'.
1. Diagnose each symptom with a likely cause and a one-line fix, and say which one you would fix first.
Read the worked answer
Fix the text-to-number problem first - it explains three symptoms at once.
- Totals showing 0 and #DIV/0!: if individual cells hold numbers but SUM returns 0, those 'numbers' are stored as text (left-aligned, green triangle). Converting them restores the CALLS total; the #DIV/0! appears because average AHT is calculated as total time ÷ total calls and the divisor was 0.
- Green triangles: same root cause - IDs pasted from a dialler or web export with a leading apostrophe or stray space.
- #N/A on five agents: the exact-match argument omitted in VLOOKUP, a trailing space in the ID, or a score below the lowest slab.
- Duplicate 'A. Kumar': looking up by name returns the first match, so one agent silently receives the other's incentive. This is the most dangerous error because it pays the wrong person.
Order: convert text to numbers, then rebuild the lookup on Agent ID, then investigate the five #N/A rows one by one and document each.
2. Explain how you would rebuild the lookup so that it is robust and does not break again next month.
Read the worked answer
Rebuild rather than patch:
- Convert: select the column, use Data > Text to Columns > Finish (or Paste Special > Multiply by 1), then TRIM and CLEAN to strip stray spaces and non-printing characters.
- Look up on Agent ID, never on name: =IFERROR(INDEX(Slab!$C$2:$C$8,MATCH($D2,Slab!$A$2:$A$8,TRUE)),"CHECK") with the slab table sorted ascending on the score boundary, and $ anchors so the range does not slide when copied down.
- Return a visible flag instead of a blank, so a failed lookup shows up during review rather than hiding as zero.
- Add data validation on Agent ID using the master roster, and conditional formatting to highlight any cell containing 'CHECK' or '#N/A'.
- Keep a control block: COUNT of IDs, SUM of calls, and a COUNTIF of error flags, each compared with an expected value before the file is sent.
3. Do you agree with the colleague's suggestion to paste everything as values and delete the formula columns? What exactly do you send within the hour?
Read the worked answer
No - but half of it is right. Agree with: sending the client a values-only copy. Clients should not receive broken formulas or a hidden slab sheet, and a values file cannot recalculate into different numbers after it leaves your machine. Disagree with: deleting the formula columns from the master. You lose the audit trail, and next month's sheet has to be rebuilt from scratch; if the client queries a figure you cannot show how it was derived. What to send: a copy with formulas converted to values, the two duplicate names resolved by ID, the five #N/A rows corrected or highlighted with a note, and totals reconciled to the dialler report - a variance of even 30 calls will be questioned. Add a short covering note listing the corrections made and the one item still open, with the date it will be closed. Sending a controlled file with a disclosed issue is professional; sending a silent one is not.