The Lab Stock Register That Cannot Be Trusted
Anita, a Class XII student, maintains the IT lab consumables register in a spreadsheet. Sheet Stock has headers in row 1: A=Item, B=Opening, C=Issued, D=Received, E=Closing, F=Status. Rows 2 to 21 hold items such as RJ45 connectors, thermal paste and A4 reams. E2 contains =B2-C2+D2, copied down to E21. F2 contains =IF(E2<10,'ORDER','OK'), copied down, where 10 is the re-order level. Two things have gone wrong. First, E9 shows #VALUE! while E8 and E10 are correct; the lab assistant remembers typing 15 into D9 'but it sat on the left side of the cell'. Second, three new items were added in rows 22 to 24 last week without any formulas, and the D column was later pasted in from a WhatsApp message forwarded by the store clerk. When the in-charge filters column F for ORDER she finds 4 items, but a physical count finds 7 items below the re-order level. She also wants the level changed from 10 to 8 in one place only, and one cell showing how many items need re-ordering. Anita has her board practical in two days.
1. Diagnose the `#VALUE!` in E9 and explain the exact correction. Then explain why the filter on column F shows 4 items when 7 are genuinely below the re-order level. Which of the two faults should Anita fix first, and why?
Read the worked answer
E9 = #VALUE!
A formula like =B9-C9+D9 performs arithmetic. Arithmetic needs numbers. D9 holds the text string '15', not the number 15. The left alignment the assistant noticed is the visible symptom: by default a spreadsheet right-aligns numbers and left-aligns text. A leading apostrophe, a leading or trailing space, a cell pre-formatted as Text before typing, or a paste from a chat app all produce this. Once one operand is text, the whole expression fails and the error propagates into anything that references E9.
Corrections, cheapest first:
- Select D9, press F2, delete the apostrophe or space, press Enter.
- Or use
=VALUE(D9)/=B9-C9+NUMBERVALUE(D9)where the text is unavoidable. - Or select the pasted range, Data > Text to Columns > Finish, which re-coerces text-numbers to numbers.
- Or Paste Special > Multiply by 1 into an empty cell and copy back.
Why 4 and not 7
The filter is not lying; it is honest about a half-built sheet. Four of the seven low items sit in rows 2-21 and carry a working IF formula. The other three sit in rows 22-24, where Anita added items but did not fill the E and F formulas down, so F22:F24 are empty. An empty cell never equals 'ORDER', so a filter or COUNTIF on column F silently excludes them. This is the more dangerous fault of the two: #VALUE! announces itself, a blank row does not.
Fix order
Fix the silent fault first. Rebuild E22:F24, then re-run the check, then solve the #VALUE!. A register that under-reports stock causes a real stock-out during practical exams; an error cell merely looks untidy. Reporting 4 when the truth is 7 is exactly the failure a viva examiner will probe.
2. The in-charge wants to change the re-order level from 10 to 8 by editing one cell, and wants a single cell that counts how many items need re-ordering. Give the exact formulas and explain the difference between relative and absolute references. State one weakness of your design when rows are added later, and how to remove it.
Read the worked answer
Single control cell
Put 8 in, say, H1 and label H2 as Re-order level. Then F2 becomes:
=IF(E2<$H$1,'ORDER','OK')
$H$1 is an absolute reference: the column and the row are locked, so when F2 is copied down to F24 every row still compares against H1. Had Anita written H1 (relative), copy-down would have produced H2, H3, H4 and each row would compare against a different, mostly empty cell, with random ORDER flags as the result. Rule to remember: relative references move with the copy because they describe a position relative to the formula; absolute references stay put because they describe a fixed location.
Counting cell
Two safe options:
=COUNTIF(F2:F24,'ORDER')
=COUNTIF(E2:E24,'<'&$H$1)
The second is better because it counts from the data itself and does not depend on column F being filled correctly. Note the syntax: you cannot write '<'&H1 as '<H1'; the operator and the cell must be joined with & so the criterion is rebuilt as the text string <8.
The weakness
Both formulas use a fixed range that stops at row 24. The next item Anita adds in row 25 is outside the count and outside the check, quietly recreating the original bug. Remedies: convert the range to a proper Table so ranges expand automatically; or use =COUNTIF(F:F,'ORDER') on the whole column with a header that cannot match; or add a control row that compares COUNTA(A:A)-1 with the number of non-blank E cells and flags a mismatch. Whichever is chosen, the count cell is worthless unless the range coverage is also verified.
3. One pasted entry reads '15 ' with a trailing space, one reads '15 units', and one is blank where 0 was meant. Anita cannot inspect 20 rows by eye every week. Recommend a validation and verification routine using specific spreadsheet features, and state what she should tell the in-charge about the 30-unit gap between the register and the physical stock.
Read the worked answer
Prevent (data validation, applied before entry)
- Data > Data Validation on D2:D24: Allow = Whole number, Data = between, Minimum 0, Maximum 500. Set an input message ('Enter units received as a plain number, 0 if none') and a Stop-style error alert. Stop is important: a Warning alert can be clicked through, which is how the current mess happened.
- Format the D column as Number, not Text or General, before entry.
- Protect the sheet and unlock only the D column, so formulas in E and F cannot be overwritten by a paste.
Verify (catch what got past)
- Add a check column:
=IF(AND(ISNUMBER(D2),D2>=0),'','CHECK')and a summary=COUNTIF(G2:G24,'CHECK'). Any non-zero result means the closing balances are not trustworthy and must not be used for ordering. =IF(TRIM(D2)<>D2,'SPACE','')or=SUMPRODUCT(--(TRIM(D2:D24)<>D2:D24))detects trailing spaces, which are invisible on screen.- Conditional formatting on column G to turn red, so the check is seen without being looked for.
- Clean on import rather than after: paste into a scratch column, run TRIM and CLEAN, use Text to Columns or VALUE, confirm ISNUMBER, then paste values into D.
What to say about the 30-unit gap
Do not simply overwrite the closing balance to match the shelf and declare the problem fixed. That destroys the audit trail and hides where the loss came from. She should: freeze the sheet, recount with a second person, record an explicit adjustment row dated and initialled with a reason (unrecorded issue, text entry ignored by a formula, damage, or theft), and state plainly that the register was unreliable because text values and unformula'd rows were silently excluded, so the stock figure for the affected period should be treated as approximate. Owning the mechanism of the error is what makes the correction credible.