The Internal Assessment Sheet That Doubled Itself
A +2 Vocational IT teacher in a Bhubaneswar higher secondary school keeps the internal assessment marksheet in a spreadsheet. Row 1 holds headings: Roll No (A), Name (B), Test 1 (C), Test 2 (D), Test 3 (E), Best Two (F), Percentage (G), Grade (H). Data for 42 students occupies rows 2 to 43. Each test is out of 25 marks, so the best two total is out of 50. Absentees are entered as the text AB, not as 0. A grade table sits in K3:L7 - minimum percentage in column K (35, 45, 60, 75, 90) and grade in column L (D, C, B, A, A1). The teacher wants an automatic grade and a count of how many students got each grade. A colleague's earlier file showed #VALUE! in column F on some rows, and after two new students were inserted in the middle of the sheet, several Best Two totals quietly became double the expected value.
1. Write the formulas for F2, G2 and H2 so that they can be filled down all 42 rows. Explain which references must be relative and which absolute, and how the text AB is handled.
Read the worked answer
F2 (best two of three): =IF(COUNT(C2:E2)<2,SUM(C2:E2),SUM(C2:E2)-MIN(C2:E2))
SUM and MIN both ignore text, so a row containing AB adds only the two numeric tests and subtracts the smaller of them, leaving the larger - i.e. the best two with the absence counted as zero. The COUNT test is needed because if two cells are AB, SUM - MIN returns 0 instead of the single mark actually scored.
G2 (percentage): =F2/50*100 - the divisor is the maximum best-two total, not 100.
H2 (grade): =IFERROR(VLOOKUP(G2,$K$3:$L$7,2,TRUE),"AB")
Why the references are what they are. C2:E2, F2 and G2 are relative so that filling down shifts them one row at a time to C3:E3, F3, G3 and so on. $K$3:$L$7 is absolute so the grade table never shifts. TRUE selects approximate match, which requires column K to be sorted ascending - otherwise the lookup silently returns wrong grades. The final fallback label should be chosen deliberately: a score below 35 also produces #N/A, so IFERROR(...,"F") may be better than labelling it AB.
2. Explain the two symptoms - #VALUE! in column F and totals that doubled after rows were inserted - and state how you would repair the sheet so the problem cannot return.
Read the worked answer
#VALUE! This is the signature of arithmetic using the + and - operators directly on text. A formula such as =C2+D2+E2-MIN(C2:E2) forces Excel to convert AB to a number, and the conversion fails. In contrast, SUM(C2:E2) and MIN(C2:E2) simply skip text cells. So the fix is to keep the aggregation inside functions that ignore text, never to build the total with +.
Doubling after insertion. Two rows inserted in the middle caused several totals to double. That is the classic result of overlapping or self-including ranges - for example a total that sums F2:F43 while itself sitting inside, or formulas hard-coded as $C$2:$C$43 that were then copied into the new rows, so the same marks were counted twice. Reproduce it by clicking one affected cell and reading its formula bar and precedent arrows.
Permanent repair. (1) Convert the data range to a table with Ctrl+T so ranges expand automatically and structured references replace hard-coded addresses. (2) Keep one row per student, no subtotal rows inside the data. (3) Rebuild F, G and H from a single clean row-2 formula and fill down. (4) Add a control cell, =SUMPRODUCT(MAX(...)) style total, or simply check that the sum of all F values equals the sum of formulas recomputed manually for two or three students.
3. The teacher now wants a count of students per grade. Describe the formulas and the layout you would use, and one cross-check that would catch a counting mistake.
Read the worked answer
Layout. Build a small summary block away from the data, say K10:L15, with the grade labels typed once in K10:K15 (A1, A, B, C, D, AB) and the counts in L10:L15.
Formulas. In L10 put:
=COUNTIF($H$2:$H$43,K10)
and fill down to L15. The range $H$2:$H$43 is absolute so it never drifts; K10 is relative so each row compares against its own grade label. Typing the grade inside the formula instead of pointing at K10 would mean editing five or six formulas whenever a label changes, and that is where typing errors creep in.
Use COUNTIFS when the question is narrower, for example girls who scored A1: =COUNTIFS($H$2:$H$43,"A1",$C$2:$C$43,">=20") - though mixing criteria across columns only makes sense if a second column really records the category.
Cross-check. Add a total cell =SUM(L10:L15) and compare it with =COUNTA($A$2:$A$43). If the two numbers differ, some student has a grade label that is not in the summary list - a trailing space, 'A 1' or lowercase 'a1' - or a blank grade caused by a broken row-2 formula that was filled down. This one check catches almost every counting error before the sheet is printed.