I85PREP
⌘ K
UPMSP Vocational IT/ITeS study materials
UPMSP Vocational IT/ITeS study resources

Repairing a Fee Register with Formula Errors

UPMSP Vocational IT/ITeS · Put your knowledge to work in context.

All case studies

Repairing a Fee Register with Formula Errors

A coaching centre uses a spreadsheet to track fees for 320 students. Columns are Roll No., Name, Course, Fee, Discount, Paid, Balance, and Payment Date. The Balance formula is =Fee-Discount-Paid and is filled down. Six discounts were typed as text '10%', and 14 students have not paid yet, so Paid is blank. The sheet shows #VALUE! in several Balance cells. Last week a staff member sorted the range by Name; now some rows show the wrong fee against the wrong student, and the monthly total is unreliable. The centre also wants a month-wise collection summary by course and a list of students with balances above ₹5,000. There are merged cells in the header, and two rows have the same roll number. The centre's manager wants a fix that staff can maintain without advanced Excel skills.

1. Diagnose the main causes of the errors and the sorting problem, and describe a safe repair sequence.

Read the worked answer

Diagnosis:

  • Blank Paid cells are not the cause of #VALUE!; Excel treats a blank as zero in arithmetic. The errors come from the six discounts stored as text (10%) and any non-numeric entries in Paid.
  • The sort problem likely happened because only one column was selected, or because merged header cells and duplicate roll numbers confused the sort. If formulas point to cells that moved, relative references can also show the wrong row.

Safe repair sequence:

  1. Work on a copy; unmerge the header and resolve the two duplicate roll numbers after checking them.
  2. Standardise discounts to decimals: strip the percent sign with SUBSTITUTE, convert with VALUE, and divide by 100; flag anything that fails.
  3. Replace the balance formula with =IFERROR(D2-N(E2)-N(F2), "Check"), where D=Fee, E=Discount, F=Paid. N() converts text to zero, but the word Check makes problems visible.
  4. Convert the range to an Excel Table, then sort the whole table by Name, never a single column.
  5. Reconcile the monthly total against the previous month before publishing.

2. Recommend a structure and formulas for the month-wise collection summary and the high-balance list.

Read the worked answer

Structure: keep a clean Fees table with one row per student, no merged cells, a real Month column, and numeric Discount and Paid values.

  • Month-wise collection by course: =SUMIFS(Fees[Paid],Fees[Course],$I2,Fees[Month],J$1), where I2 is the course and J1 is the month label. Anchor the criteria references correctly so the formula can be copied across.
  • High-balance list: add =IF([@Balance]>5000,"Follow up","") in the table and filter, or use =FILTER(Fees[[Roll No.]:[Balance]],Fees[Balance]>5000) if available.
  • Use a PivotTable for the course × month matrix if courses are added later.

Reasoning: SUMIFS and PivotTables recalculate from the table and survive sorting; hard-coded totals and merged cells do not. Validate the total against SUM(Paid) for the month.