Diagnosing a corrupted policyholder worksheet
An Upper Division Clerk maintains a single worksheet of 2,400 policyholders: column A employee code, column B name, column C premium in rupees, column D date of joining, column E mobile number. Work has gone wrong in three places. First, the total at the foot, =SUM(C2:C2401), shows ₹4,12,300, although the clerk's rough calculator total is about ₹6,80,000; several premium cells sit left-aligned and carry a small green triangle. Second, a VLOOKUP on a second sheet, =VLOOKUP(A5,Sheet1!A:E,3,0), returns #N/A for about 40 codes that the clerk can plainly see in column A. Third, the file was saved as CSV and reopened; codes such as 00417 have become 417, and mobile numbers beginning with 9 now display as 9.02E+09. The clerk has a backup from last week but is reluctant to redo two days of typing.
1. Explain why the SUM total is too low, and give a procedure for correcting it without retyping the data.
Read the worked answer
Cause. SUM ignores text. The left alignment and green triangle are Excel's warning that those entries in column C are stored as text, not numbers - typically after a copy-paste from a website, a PDF or a CSV, sometimes with a stray space or a comma used as a thousands separator. Every text entry is silently excluded from C2:C2401, so the total is short by roughly ₹2,67,700.
Fix, in order:
- Make a copy of the sheet before editing.
- Select column C and run Data → Text to Columns → Finish. This re-evaluates each entry as a number and usually converts the whole column at once.
- For stubborn cells, use a helper column with =VALUE(TRIM(C2)) and paste the results back as values.
- Verify before trusting the total: compare =COUNT(C2:C2401) with =COUNTA(C2:C2401). If the counts differ, some entries are still text - find them with =ISNUMBER(C2)=FALSE.
- Set the column to Number format, then read the total.
The principle to state: a total is only as reliable as the data type of the cells behind it.
2. Why does the VLOOKUP return #N/A for codes that visibly exist, and how would you confirm and fix it?
Read the worked answer
The formula already uses 0 for exact match, so the classic wrong-argument explanation is out. The likely cause is a data-type mismatch between the lookup value and the stored key: A5 is numeric while the codes in Sheet1 are text (or the reverse), or the stored codes carry trailing spaces. To Excel, 417 and '417' are different, and '417 ' is different again.
Confirm it with three quick tests on a failing row: =ISNUMBER(A5), =ISNUMBER(Sheet1!A200), and =A5=Sheet1!A200. If the last returns FALSE while the values look identical, the type or spacing differs. =LEN(Sheet1!A200) exposes hidden spaces.
Fix by normalising both sides rather than patching each row: clean the key column with TRIM and convert it consistently to text (or consistently to number), then re-point the lookup. A quick check formula is =VLOOKUP(A5&'',Sheet1!A:E,3,0) if the stored keys are text. Longer term, format the code column as Text before any data entry, so the mismatch never arises.
3. What caused the CSV damage, and is restoring last week's backup the right response?
Read the worked answer
Cause. CSV stores plain values with no formatting, so Excel on reopening guesses types. Employee codes are read as numbers, and leading zeros vanish (00417 → 417); long mobile numbers are also read as numbers and, because the column is narrow, display in scientific notation (9.02E+09), which also destroys the last digits in some cases. Neither problem is data corruption in the file so much as a type-guessing error on import.
Fix: stop double-clicking the .csv. Use Data → Get Data → From Text/CSV, and in the import wizard set the employee-code and mobile-number columns to Text before loading. Alternatively keep the master file as .xlsx and export a CSV copy for transmission only, and format both columns as Text so the values are stored as strings.
On the backup: restoring it would discard two days of genuine entries and still not fix the underlying format problem. The better sequence is: save a copy of the current file, repair the types in place, reconcile the record count against the source register, and re-save as .xlsx. Keep the weekly backup untouched until the repair is verified.