The Grade Column That Sorted Itself Out
In a SSLC IT internal assessment, a teacher enters marks for 42 students in a spreadsheet. Column A is Roll No, B is Name, C is Theory out of 40, D is Practical out of 40, E is Total, F is Grade. The Grade rule is A for 70 and above, B for 60–69, C for 50–59, and D below 50, out of 80. Five students show Total as 0 even though their Theory and Practical marks are visible. The teacher's formula in E2 is =SUM(C2:D2), copied down. The Grade formula in F2 is =IF(E2>=75,"A",IF(E2>=60,"B",IF(E2>=50,"C","D"))) and copied down. The teacher then selects only column F, clicks Sort Z to A to group grades, and finds that names no longer match roll numbers. A parent meeting is in two hours, and the teacher asks for a corrected sheet and a simple chart showing how many students got each grade.
1. Explain two different reasons why a Total can show 0 while the marks are visible, and describe one quick test that distinguishes them.
Read the worked answer
One reason is that the marks are stored as text, not numbers. They may have been typed with an apostrophe, imported with leading spaces, or copied from a messaging app. SUM ignores text, so =SUM(C2:D2) returns 0 even though the digits are visible. A quick test is to click a mark cell and look at its alignment and the small green triangle, or type =ISNUMBER(C2) in an empty cell. FALSE confirms text. Another test is =SUM(C2:D2) versus =C2+D2; the second usually gives #VALUE! for text, while SUM hides the problem as 0.
The second reason is that calculation is set to Manual, so the totals have not recalculated after the marks were entered or corrected. The displayed 0 may be an old result. A quick test is to press F9 or use Formulas > Calculation Options > Automatic and see whether the totals change. A third possibility, worth checking if neither applies, is that the E cells in those rows were overwritten with 0 or point to the wrong row. The teacher should fix the cause, not just retype the totals, and then copy the corrected formula down to all 42 rows. Checking a known student's total by hand is the final confirmation.
2. The Grade formula returns B for a student with 72 marks. Identify the error and write a corrected formula for F2 using nested IF. Explain why the order of conditions matters.
Read the worked answer
The error is the first threshold. The formula tests E2>=75 for A, but the stated rule is 70 and above. A student with 72 therefore fails the first test, passes E2>=60, and gets B. The corrected formula is =IF(E2>=70,"A",IF(E2>=60,"B",IF(E2>=50,"C","D"))). This gives A at 72, B at 69, C at 55 and D at 49.
Order matters because nested IF stops at the first true condition. If the formula began with E2>=50, every student with 50 or more would get C and the A and B bands would never be reached. The safe pattern is to test from the highest cutoff downward. The teacher should also handle blank rows: =IF(E2="","",IF(E2>=70,"A",IF(E2>=60,"B",IF(E2>=50,"C","D")))) prevents an absent student from being graded D. After entering the formula, the teacher should copy it down, check the boundary values 49, 50, 59, 60, 69 and 70, and confirm that the maximum possible total is 80 so no A grade is impossible.
3. The teacher sorted only column F. Explain what went wrong, give the correct sorting procedure, and state one check to confirm the data is still aligned before sharing.
Read the worked answer
Sorting only column F rearranged the grades as a separate list. The roll numbers, names and marks stayed in their original rows, so each grade no longer belongs to the student beside it. The sheet now looks organised but the data is corrupted. The teacher should press Ctrl+Z immediately if the file is still open.
The correct procedure is to select the whole table A1:F43, or click inside it and use Data > Sort with 'My data has headers' ticked. Choose Grade as the first sort key and Roll No as the second, so students within a grade remain in roll order. Before sorting, the teacher should make a copy of the file, because sorting cannot always be undone after saving. To verify alignment, pick three students and check by hand that Theory + Practical equals Total and that the grade matches the rule. A second check is to compare the number of A, B, C and D grades with a COUNTIF count before and after sorting; the counts must be identical. Only then should the sheet and chart be shared.