Attendance Bonus Sheet at Five in the Evening
Sushila leads a 20-agent data-entry shift at a BPO in Jaipur. An attendance bonus of ₹1,200 is paid only when an agent is marked present on at least 24 of the 26 working days. She keeps one workbook: column A is the agent name, columns C to AB hold the daily marks (P, A or L) copied from the client's portal, column AC should show days present, AD the attendance percentage, and AE the bonus decision. Before payroll she finds three problems. For agent Rakesh, a manual count gives 23 P marks but AC shows 22. Column AE shows 'Yes' for all 20 agents, including two who were absent for eight days. When she types a total in AD29, Excel shows a circular-reference warning and the status bar reads 0. The daily marks in columns C to AB are left-aligned, and the sheet has no data validation or protection. Payroll closes in one hour.
1. For Rakesh the AC column shows 22 while a manual count gives 23. Explain the most likely cause and give a formula that would count correctly even if the sheet is pasted again next month.
Read the worked answer
The 23rd mark is almost certainly not exactly the text P. COUNTIF tests the whole cell contents, so P (trailing space), P. or a non-breaking space pasted from a web portal all fail the test COUNTIF(C2:AB2,"P"). Click the suspect cell and read the formula bar; =LEN(cell) returning 2 instead of 1 confirms an invisible character.
Two points candidates often get wrong: COUNTIF is not case-sensitive, so a lowercase p would be counted; and TRIM removes ordinary spaces but not a non-breaking space, CHAR(160).
Robust formula:
=SUMPRODUCT(--(TRIM(SUBSTITUTE(C2:AB2,CHAR(160)," "))="P"))
If the sheet must stay simple, clean once per paste: select C2:AB21, use Find & Replace to remove stray spaces, then re-run COUNTIF. Better still, import the portal export into a staging sheet, clean it there in one pass, and feed the register from it so the same corruption never reaches the payroll columns.
2. Every agent shows 'Yes' in the bonus column. Explain how this happened, state the corrected formula, and give one way to prove it is right.
Read the worked answer
A filled-down formula that points at one fixed cell. If AE2 holds =IF($AC$2>=24,"Yes","No"), the dollar signs lock both column and row, so every row re-reads AC2 - the first agent's total - and returns the same answer. The two agents with eight absences got 'Yes' because the first agent qualified, not because they did.
Corrected formula: =IF(AC2>=24,"Yes","No") in AE2, filled down so AC2 becomes AC3, AC4 and so on. Use $AC2 only if the column must be locked while rows are allowed to move.
Proof: click AE7 and read the formula bar - it must say AC7, not AC2; use Formulas → Trace Precedents; or step through one failing row with Evaluate Formula. Cross-check =COUNTIF(AE2:AE21,"Yes") against a manual list of agents with 24 or more P marks.
Also guard the threshold: 24 of 26 is 92.31%, so never test a percentage that has been rounded to 92 - test the day count directly.
3. The total row triggers a circular-reference warning and the status bar shows 0. Explain the cause, give the fix, and describe two controls that would stop the next person from breaking the sheet.
Read the worked answer
Cause: the total formula sits inside the range it adds. If =SUM(AD2:AD29) is typed in AD29, then AD29 refers to itself; Excel cannot resolve it, shows the warning and returns 0, which also corrupts any figure built on that total. Fix: put the total outside the summed range (AD30 with =SUM(AD2:AD29)), or sum only AD2:AD28, or drop the total from the per-agent block and compute payroll totals on a separate summary sheet.
Controls:
- Data validation on C2:AB21 with a list of P, A, L only, using the 'Stop' style, so no stray space or word like 'Present' can be typed.
- Sheet protection with the formula columns AC, AD and AE locked and only the mark area unlocked, so a user cannot overwrite a formula or drag it over neighbouring cells.
Supporting controls: convert the range to an Excel Table so formulas extend automatically when rows are added, and keep a clean master plus a dated copy before each payroll run. Before releasing the file, test two known agents by hand and reconcile the headcount, the number of 'Yes' entries and the total paid.