Repairing a Marks Register with Formulas
Ms Kaur maintains the Class 10 marks register for 42 students in a school in Moga. Columns: Roll No, Name, Punjabi, English, Maths, Science, Social Science, Total, Percentage, Result, Grade. Maximum marks are 100 each except Maths, which is out of 80 (theory 60 + practical 20). A student helper typed totals after adding marks on a calculator and made four errors. He then wrote a percentage formula by dividing the total by 500 and copied it down. When Ms Kaur changed the Maths maximum to 80, every percentage became wrong. She also wants the merit list sorted highest to lowest and a count of students who failed (below 33% in any subject). The sheet has a merged title in row 1 and blank rows between some records.
1. Write the Total and Percentage formulas for row 2 using absolute references where needed, and explain what goes wrong when the percentage formula is copied down without them.
Read the worked answer
With marks in B2:F2: Total in G2 is =SUM(B2:F2). Percentage in H2 should be =G2/$L$1*100, where L1 holds the maximum total. L1 should itself be calculated, for example =SUM(B1:F1) if subject maxima are entered above, so that changing Maths from 100 to 80 automatically changes the maximum from 500 to 480.
A relative reference such as G2/L1 changes when copied. In row 3 it becomes G3/L2, in row 4 G4/L3, and so on. Those cells are blank or contain text, so Excel returns wrong values or #DIV/0!. The dollar signs in $L$1 lock both column and row, so every copied formula still divides by the same maximum cell.
A hard-coded =G2/500*100 avoids #DIV/0! but is worse in another way: when the Maths maximum changed, 500 was no longer correct and every percentage silently became too low. Referencing a single maximum cell fixes all rows in one edit.
2. Write formulas for Result and Grade using the rule that a student fails if below 33 in any subject, and explain the order of the IF conditions.
Read the worked answer
Result in I2: =IF(COUNTIF(B2:F2,"<33")>0,"Fail","Pass"). COUNTIF counts how many subject marks are below 33; if the count is greater than zero, at least one subject is failed. Using "<33" means a mark of exactly 33 passes, which matches the rule 'below 33'.
Grade in J2, using the school's table (A ≥ 80, B 60–79, C 40–59, D 33–39, E below 33):
=IF(I2="Fail","E",IF(H2>=80,"A",IF(H2>=60,"B",IF(H2>=40,"C","D"))))
Order matters. Excel's IF stops at the first TRUE condition. A student with 85% satisfies H2>=80, H2>=60 and H2>=40, so the highest band must be tested first. If the formula checked >=60 before >=80, every student above 60 would receive B. The Fail test comes first so a student who failed one subject cannot receive a high grade from a good percentage. Boundaries should use >= consistently to avoid gaps between bands. If the school's actual grade table differs, only the numbers inside the IF change, not the structure.
3. Explain why sorting the merit list could scramble the data and how Ms Kaur should prepare the sheet, including a formula to count failures.
Read the worked answer
Sorting scrambles data when only one column is selected. If Ms Kaur clicks a single cell in the Percentage column and chooses Sort, Excel may ask whether to expand the selection; if she answers wrongly, only the percentages move and every name becomes attached to another student's marks. Blank rows split the data into separate blocks, and the merged title in row 1 prevents Excel from recognising a clean table range.
Preparation:
- Unmerge the title, move it to a separate row or into the header, and delete the blank rows so the data is one continuous block.
- Select the whole range A1:K43, or click one cell inside it and use Sort with My data has headers ticked.
- Sort by Percentage, largest to smallest, and make a backup copy first.
- Check that Total and Percentage formulas still reference their own row's marks after sorting; absolute references such as $L$1 should remain fixed.
Failure count: =COUNTIF(I2:I43,"Fail"). This counts the Result column, so one formula gives the total number of students who failed at least one subject.