Consolidating Class XI Marks Without Breaking Formulas
Class XI has 42 students. The mark register in a spreadsheet has columns A roll number, B name, C Tamil, D English, E Mathematics, F Science, G total, H average and I grade. Each subject is out of 100. The class teacher typed =C2+D2+E2+F2 in G2 and =G2/4 in H2, then filled both down to row 43. The grade rule is: 90 and above A, 75 to 89 B, 60 to 74 C, 50 to 59 D, below 50 E. For the student in row 10, the Tamil mark is entered as AB (absent). G10 shows 0, H10 shows 0 and I10 shows E. The teacher also wants to sort the class by total, but is warned that sorting may scramble the grades. A separate threshold table is placed in K2:L6.
1. Write spreadsheet formulas for G2, H2 and I2 that treat a student with any absent subject as ABSENT, and that take grade cut-offs from the table K2:L6 using absolute references. Explain why COUNT, not COUNTA, is used.
Read the worked answer
Assume the threshold table is arranged in ascending order: K2=0, L2=E; K3=50, L3=D; K4=60, L4=C; K5=75, L5=B; K6=90, L6=A.
G2: =IF(COUNT(C2:F2)<4,"ABSENT",SUM(C2:F2))H2: =IF(G2="ABSENT","ABSENT",G2/4)I2: =IF(H2="ABSENT","ABSENT",VLOOKUP(H2,$K$2:$L$6,2,TRUE))
COUNT counts only numeric entries. If Tamil contains the text AB, COUNT(C2:F2) becomes 3, so the formula returns ABSENT instead of 0. COUNTA would count AB as an entry and wrongly treat the row as complete. The absolute range $K$2:$L$6 keeps the lookup table fixed when the formula is filled down. VLOOKUP with TRUE requires the first column of the table to be sorted ascending, which it is.
2. Explain why filling I2 down without absolute references would produce wrong grades, and why sorting only column G is risky. State the safe sort range.
Read the worked answer
Without dollar signs, the lookup range K2:L6 is relative. In row 3 it becomes K3:L7, in row 4 K4:L8, and so on. Eventually the range extends below the table into blank cells, so VLOOKUP either returns the wrong grade or #N/A. The $ signs lock both the column and the row.
Sorting only column G is risky because it moves the totals but leaves names and subject marks in their original rows. The total in row 5 would then belong to a different student, and any formula that refers to that row would show a mismatch. The safe action is to select the whole data block A2:I43 and sort by column G, so each student's row moves as one unit. Even then, the teacher should check that no formula uses fixed row references that would now point to another student.
3. Write formulas to count A grades and to compute the class average excluding absentees. Explain whether AVERAGE(H2:H43) is safe when some cells contain the text ABSENT.
Read the worked answer
Count of A grades: =COUNTIF(I2:I43,"A"). This counts only cells whose value is exactly A, so grades such as B or ABSENT are ignored.
Class average: =AVERAGE(H2:H43). This is safe only because the absent cells contain the text ABSENT, not 0. AVERAGE ignores text cells, so they are excluded automatically. If the formula had returned 0 for absentees, the same AVERAGE would include those zeros and pull the class average down unfairly.
A more explicit alternative is =AVERAGEIF(H2:H43,"<>ABSENT"), but it is not necessary here. The teacher should verify that H10 displays ABSENT, not 0, before trusting the average.