The #VALUE! Result: Diagnosing a Class XII Marks Sheet
Mrs. Patra maintains the Class XII IT practical record in LibreOffice Calc. Columns are A Roll No, B Name, C Theory (out of 70), D Practical (out of 30), E Attendance %, and F Result. Some students were absent, and she typed 'AB' in C or D. Her formula in F2 is =IF((C2+D2)>=45,"Pass","Fail"), dragged down to F49. It displays #VALUE! for absent students. Her summary cell B52 uses =COUNTIF(F2:F49,"Pass")/COUNTA(F2:F49) and shows 62%, but she expected 78%. There are 48 students in rows 2–49. The principal wants subject-wise pass percentage, counting absent students as fail but not including them in the average marks. Mrs. Patra also wants to award a star to the top three total marks, with ties sharing the same rank. She plans to sort the sheet by total marks before printing.
1. Explain why F2 shows #VALUE! for absent students and why the summary shows 62% when Mrs. Patra expected 78%. Under the principal's rule, which pass percentage is correct?
Read the worked answer
#VALUE!: the formula performs arithmetic, C2+D2. When C2 or D2 contains the text 'AB', Calc cannot add text to a number, so the whole expression returns an error. The IF never gets a numeric total to compare with 45.
62% versus 78%: these are answers to two different questions. 62% is the enrolled pass rate: 30 passes out of 48 students gives 30/48 = 62.5%. The 78% figure is probably the present-only pass rate: if 37 students were present and 29 passed, 29/37 = 78.4%. The summary formula counts only cells literally equal to 'Pass' in the numerator but divides by all 48 non-empty roll rows, so absent students land in the denominator and not the numerator.
Under the principal's rule, absent students count as fail, so the correct headline pass percentage is the enrolled rate, about 62.5%, not 78%. The 78% should be reported separately as 'pass percentage of students present' and must be labelled, otherwise it flatters the result. The school should state both figures so the principal can see the absence problem.
2. Write corrected formulas for F2 and for the summary cell, and a formula for average theory marks that excludes 'AB' without treating it as zero.
Read the worked answer
For F2, test both marks before doing arithmetic:
=IF(AND(ISNUMBER(C2),ISNUMBER(D2)),IF(C2+D2>=45,"Pass","Fail"),"Fail")
This returns 'Fail' for any absent mark, so the result column has no errors. An alternative is to keep a separate status such as 'Absent', but then COUNTIF must be adjusted.
For the summary, divide passes by the number of enrolled students, using a column that is always filled:
=COUNTIF(F2:F49,"Pass")/COUNTA(A2:A49)
Format the cell as a percentage. Using COUNTA on column A avoids counting stray cells in F.
For average theory marks excluding absentees:
=SUM(C2:C49)/COUNT(C2:C49)
SUM and COUNT ignore text, so 'AB' is skipped in both numerator and denominator. Do not use AVERAGE(C2:C49) directly in a range containing error values, and do not replace 'AB' with 0 - that would pull the average down and wrongly count absence as a zero score.
3. Write a formula to rank students by total marks with ties sharing the same rank, and explain what happens if five students tie for second place when she awards stars to ranks 1–3.
Read the worked answer
First insert a Total column, for example G2: =IF(AND(ISNUMBER(C2),ISNUMBER(D2)),C2+D2,""). Then in H2 use =RANK(G2,$G$2:$G$49,0) and fill down. The third argument 0 means descending order. Equal totals receive the same rank, and the next rank is skipped: 1, 2, 2, 4.
For stars, =IF(H2<=3,"★","") awards a star to every student whose rank is 3 or better. If five students tie for second, all five receive rank 2 and all five get stars, so the school may print six stars rather than three. That is mathematically consistent with 'ties share the same rank', but it may not be what the principal intended by 'top three'. Mrs. Patra must choose a rule before printing: either accept that ties expand the list, or add a tie-breaker such as higher practical marks, then higher theory marks, then lower roll number, and rank on a helper column. She should not sort the sheet and then rely on row numbers, because row numbers change; RANK uses the mark values, so it survives sorting if the range is absolute.