I85PREP
⌘ K
UP Police Computer Operator study materials
UP Police Computer Operator study resources

The Duty Roster That Will Not Add Up

UP Police Computer Operator · Put your knowledge to work in context.

All case studies

The Duty Roster That Will Not Add Up

At a district police lines office, a computer operator maintains Duty.xlsx in Excel 2019 for 30 constables. Column A holds names, column B the shift typed as Day, Night, Night duty or Leave, column C the date entered as text such as 01-02, and column D hours worked, except in six rows where NA was typed. The summary cell =COUNTIF(B2:B31,"Night") returns 9, but the handwritten register shows 11 night duties. A second summary cell =D2*24 returns #VALUE!. A VLOOKUP on the reporting sheet returns #N/A for two constables whose names carry a trailing space in column A. The daily report is due to the Superintendent in 45 minutes. The operator has been told not to edit any raw entry, only to repair the reporting layer. There is one shared office PC, no internet, and a pen drive that the standing order forbids using on office systems.

1. The COUNTIF returns 9 while the register shows 11. What are the plausible causes, and what formula counts correctly without editing any entry in column B?

Read the worked answer

Work out what COUNTIF does not do before touching the sheet.

  • COUNTIF is case-insensitive, so "night" or "NIGHT" would still count. Lowercase typing is not the explanation.
  • COUNTIF counts hidden and filtered rows. A filter is not the explanation either.
  • What remains is a text mismatch or a wrong range: a trailing/leading space ("Night " left after a name was pasted), the string "Night duty" in two rows, a non-breaking space (CHAR 160) copied from a PDF or web page, or a range stopping at B31 when two rows were inserted at B32.

Test in seconds: enter =COUNTIF(B2:B31,"Night*"). If it returns 11, the cells hold "Night" plus extra characters, and the cause is text, not counting logic.

Formula that survives all three text defects without editing B:

=SUMPRODUCT(--(TRIM(SUBSTITUTE(B2:B31,CHAR(160)," "))="Night"))

TRIM removes ordinary spaces, SUBSTITUTE converts the non-breaking space first (TRIM alone does not), and SUMPRODUCT needs no Ctrl+Shift+Enter. If the result is still 9, stop guessing about text and check the range end and merged cells.

2. Explain why =D2*24 shows #VALUE! and the VLOOKUP shows #N/A, and give a safe replacement for both.

Read the worked answer

#VALUE! - D2 holds the text "NA". Arithmetic on text is an error, so Excel refuses. This is actually the safer behaviour: =SUM(D2:D31) on the same column would silently skip the six text cells and report an understated total with no warning. The report layer must make missing data visible, not swallow it. Replacement: =IF(ISNUMBER(D2),D2*24,"") in the hours-total cell, plus a separate audit cell =COUNTIF(D2:D31,"NA") reported as "6 rows without hours". The number 6 goes to the Superintendent; the formula does not hide it.

#N/A - a trailing space means the lookup value "Ramesh Kumar " is not equal to the table entry "Ramesh Kumar", so exact match fails. Two things to check: the stray space, and the fourth argument. Omitting it leaves the default TRUE (approximate match), which on unsorted data returns wrong answers or #N/A. Fix: =IFERROR(VLOOKUP(TRIM($A2),Master!$A$2:$B$31,2,FALSE),"Not found"). Keep FALSE, wrap with IFERROR so a genuine mismatch is labelled rather than displayed as an error code, and remember TRIM does not remove CHAR 160 - use SUBSTITUTE if that is the culprit.

3. In what order do you act with 45 minutes left, and what do you leave behind for audit?

Read the worked answer

Order matters because the raw file is the evidence and the report is only a view.

  1. Protect the original: Save As Duty_<date>_working.xlsx and work only on the copy. Nothing is typed into row data.
  2. Report layer first: apply the corrected COUNTIF/SUMPRODUCT, the ISNUMBER guard, and the fixed lookup on a separate Report sheet.
  3. Reconcile, do not decide: a small variant list - count of "Night", count of "Night*", count of TRIM version - goes with the report so the Superintendent sees why 9 became 11. The operator does not silently pick 11; the supervisor confirms the register.
  4. Output: print or export to PDF, and keep the workbook saved.
  5. Audit note: one line per changed formula, the time, and the reason. When a figure has been corrected under time pressure, the note is what protects the operator later.

What is not done: no sorting or deleting of rows, no editing of column A or B, no pen drive (standing order), no silent arithmetic such as typing 11 over a formula. If the 45 minutes run out, send the report with the reconciliation list attached and the cell marked "under verification" - an explained number is defensible, an unexplained one is not.