Marks Register With a Broken Formula and a Failed Lookup
At a PU college, a teacher maintains the II PUC IT practical marks register in a spreadsheet with 180 students. Columns are A Roll No, B Name, C Internal (20), D Theory (70), E Practical (30), F Total, G Result, H Grade. The teacher entered =SUM(C2:E2) in F2, but the Internal column contains the text “AB” for 14 absent students, so their totals show wrong values. The Grade formula is =IF(F2>=90,"A",IF(F2>=80,"B",IF(F2>=70,"C",IF(F2>=60,"D",IF(F2>=50,"E","Fail"))))) and the Result formula is =IF(F2>=35,"Pass","Fail"). A second sheet contains student names with extra spaces; a VLOOKUP from the marks sheet returns #N/A for 9 students. The principal wants a pie chart of all 180 grades for the notice board. The teacher asks whether to sort the data first and whether to use absolute references.
1. Identify the data and formula problems that can make Total or Grade wrong, and give corrected formulas that handle absent students.
Read the worked answer
SUM(C2:E2) ignores text, so “AB” is treated as 0; the student may then get a low total and “Fail” even though “AB” means absent, not zero. If any of C2:E2 is “AB”, the total should show “AB” or be excluded, not silently become a number.
Corrected Total: =IF(COUNT(C2:E2)<3,"AB",SUM(C2:E2)). This treats missing/non-numeric entries as absent; if only one component is legitimately blank, adjust the count.
Corrected Result: =IF(NOT(ISNUMBER(F2)),"AB",IF(F2>=35,"Pass","Fail")).
Corrected Grade should first test ISNUMBER(F2); otherwise comparisons with text can misbehave. Also make sure the grade boundaries match the college rule and that F2 is not itself a text total. Sorting is not required to fix the formulas; it only changes row order.
2. Explain why VLOOKUP returns #N/A for 9 students and give two fixes, including one that prevents future mismatches.
Read the worked answer
#N/A usually means the lookup value is not found exactly. The second sheet has names with extra leading/trailing spaces, so “Ravi Kumar” in the marks sheet does not equal “Ravi Kumar ” in the lookup sheet. Other causes are text-formatted roll numbers vs numeric roll numbers, or a missing fourth argument (exact match).
Fix 1: clean the key at lookup time: =VLOOKUP(TRIM(A2),Sheet2!$A$2:$B$181,2,FALSE). If the key is numeric text, use VALUE(TRIM(A2)) or standardise both columns. Fix 2: on the lookup sheet, use a helper column =TRIM(A2) and paste values, or use Data → Text to Columns/Find & Replace to remove spaces; then validate with COUNTIF. To prevent recurrence, use Data Validation and a consistent roll-number key instead of names, and keep the lookup table as an absolute range.
3. Recommend a chart and layout for the principal’s request. Why is a pie chart of all 180 students a poor choice?
Read the worked answer
A pie chart of 180 individual students would have 180 slices; labels would overlap and no one could compare categories. The principal needs the grade distribution, not each student. First build a small summary table using COUNTIF for A, B, C, D, E and Fail, then chart those six categories. A column/bar chart is clearest for comparing counts; a pie is acceptable only because there are six categories, with data labels showing counts and percentages.
Put the chart on a separate print-ready sheet, use a clear title (“Grade distribution - II PUC IT Practical”), and keep the full register sorted by Roll No for record-keeping. Sorting the marks sheet is not needed for the chart and can break row-based formulas, so sort only after converting formulas to values or use a pivot/summary table.