Broken totals and a grade table in the Class XII marksheet
An IT-ITeS instructor at a Kurukshetra school keeps the Class XII internal assessment marks of 120 students in a spreadsheet: roll number and name in columns A and B, then marks out of 50 in five subject columns C to G. The total in column H was entered as =B2+C2+D2+E2+F2 and filled down. After a new student was inserted at row 37, several totals below it no longer match a manual check, and one total cell displays #VALUE!. Percentage was calculated by dividing by 5, but Computer Applications is an optional subject taken by only 40 students and must be excluded from the percentage of everyone else. Grades are to come from a fixed table: 90 and above A1, 80-89 A2, 70-79 B1, 60-69 B2, below 60 C. The instructor also wants a merit list of the top 10 without disturbing roll-number order, a count of students who failed in at most one subject, and one chart for the principal.
1. Diagnose why the totals broke after the row was inserted and why one cell shows #VALUE!, then give a corrected, more robust total formula.
Read the worked answer
A common but wrong diagnosis is that inserting a row breaks filled formulas. Inserting a row inside a filled range actually expands the range and adjusts references, so insertion alone is not the cause.
The realistic causes are two:
- A row was pasted over an existing row rather than inserted, so the cells in that row lost their formulas and now hold static numbers or blanks, and the rows below still reference the old positions. The check 'does row 37 contain a formula or a typed number?' settles it.
- #VALUE! means a non-numeric value is inside the arithmetic range - for example the text 'AB' or a marks value entered with a trailing space or an apostrophe. The error appears only in that row's total, not in the rows below, which is exactly what the instructor described.
Fix: re-enter or copy the formula down from the row above, and clean the offending cell (TRIM, then re-enter as a number).
More robust formula: =SUM(C2:G2). It tolerates inserted rows and inserted columns, ignores blanks, and is far easier to audit than a chain of plus signs. Percentage should then be total divided by the number of subjects actually taken, not a hard-coded 5.
2. Compare a nested IF and a lookup-table approach for assigning grades, and write the formula you would actually use. Mention at least one edge case.
Read the worked answer
A five-level nested IF such as =IF(pct>=90,"A1",IF(pct>=80,"A2",IF(pct>=70,"B1",IF(pct>=60,"B2","C")))) does work if the brackets are right and the order runs from highest to lowest. Its weaknesses: it is easy to mis-nest, hard for a colleague to audit, and any change to the cut-offs means editing the formula in 120 cells.
A lookup table is better. Put the lower bounds in ascending order in K2:K6 (0, 60, 70, 80, 90) and the grades in L2:L6 (C, B2, B1, A2, A1), then use:
=VLOOKUP(pct,$K$2:$L$6,2,TRUE)
The TRUE (approximate match) argument requires the first column to be sorted ascending; it then returns the grade for the largest bound not exceeding the percentage. The absolute $ references let the formula be filled down safely.
Edge cases to state: a percentage of exactly 90 must fall in the A1 row and does, because 90 matches its own lower bound; a blank percentage returns C silently, so wrap with =IF(pct="","",VLOOKUP(...)). Also confirm no percentage is stored as text, or the lookup fails.
3. How would you produce a top-10 merit list, count students failing in at most one subject, and choose the chart - all without sorting the sheet and without the optional subject distorting the results?
Read the worked answer
Merit list without sorting. Sorting would reorder the roll numbers and break every downstream reference and printed register. Use =RANK.EQ(pct,$I$2:$I$121,0) in a helper column for descending rank, then pull the top 10 with =LARGE($I$2:$I$121,ROW(A1)) alongside =INDEX($B$2:$B$121,MATCH(LARGE(...),$I$2:$I$121,0)). A mistake to flag: if two students tie, MATCH returns the first name twice - acknowledge the tie rather than pretending it cannot happen.
Fail count. Add a helper column =COUNTIF(C2:G2,"<33"). Blanks are ignored by COUNTIF, which is exactly right for the 80 students who do not take Computer Applications - a naive COUNTA or a manual count would wrongly fail them. Then =COUNTIF($J$2:$J$121,1) gives students with exactly one fail.
Chart. Subject-wise average comparison is categorical, so a column chart with five labelled bars; a line chart implies continuity between subjects and misleads. For grade distribution a pie or bar works, but keep it to six slices or fewer. One chart for the principal means one message: subject averages showing which subject needs attention.