Spreadsheet Payroll Errors at a Panchayat Samiti
A junior accountant at a Panchayat Samiti maintains a spreadsheet for 45 employees. Columns: A Employee ID, B Name, C Basic Pay, D DA%, E HRA, F Deductions, G Net Pay, H Deduction %. Cell D2 contains 12% (formatted as 0.12). The formula in G2 is =C2+C2*D2+E2-F2, and it was copied down to G46. Column H uses =F2/C2. The DA rate is later revised to 14% by changing D2, but net pay values do not update correctly. Some cells in C show 'NA' or are blank, and G shows #VALUE! or #DIV/0! for a few rows. The clerk plans to manually overwrite the errors and lock the sheet. The block development officer asks for a reliable fix before salary disbursement.
1. Diagnose why changing D2 did not update all net pay values. Explain absolute versus relative references and give the corrected formula.
Read the worked answer
In the formula =C2+C2*D2+E2-F2, the reference D2 is relative. When copied to G3, it becomes D3; in G4 it becomes D4, and so on. Only G2 actually reads the DA rate in D2. Changing D2 therefore updates only the first row, while lower rows read blank or unrelated cells, producing wrong or error values.
The corrected formula is =C2+C2*$D$2+E2-F2. The dollar signs make the row and column absolute, so every copied row continues to read the single DA rate in D2. Alternatively, define a named range such as DA_Rate for D2 and use =C2+C2*DA_Rate+E2-F2. Copy the corrected formula down after testing one row.
2. Explain the likely causes of #VALUE! and #DIV/0! and propose data-validation and protection measures.
Read the worked answer
#VALUE! appears when a text value such as 'NA' is used in arithmetic. In G, C contains 'NA', so C2+C2*$D$2 fails. #DIV/0! appears in H when C2 is blank or zero, because =F2/C2 divides by zero.
Fixes: clean the source data so C contains only valid non-negative numbers; use =IF(C2="","",F2/C2) or =IFERROR(F2/C2,"") in H; and use =IFERROR(C2+C2*$D$2+E2-F2,"Check basic pay") in G so exceptions are visible. Apply data validation on C (decimal, greater than or equal to zero), reject text entries, and use conditional formatting to flag blanks. Protect the sheet with formula cells locked and input cells unlocked, so users can enter data but cannot overwrite formulas.
3. Should the clerk manually overwrite the errors and lock the sheet? Advise.
Read the worked answer
No. Manually overwriting error cells destroys the formula logic and the audit trail; the next DA revision or employee change will reproduce the problem. Locking the sheet after overwriting freezes both the correct and incorrect values, making review harder.
The clerk should first take a backup, correct the formula, clean and validate the input data, test the workbook on a copy, and then protect only the formula cells while leaving input cells editable. A change note should record the old DA rate, new rate, date and approver. If any manual adjustment is unavoidable, it should be made in a separate adjustment column with a reason, not by replacing the calculation.