The 48 Report Cards: Leading Zeros, Zero Totals and a Mail Merge
Renu is the office clerk at a government senior secondary school in Haryana. She keeps the Class 10 internal assessment marks in a spreadsheet on the office computer, which runs a free office suite and has no internet connection. The sheet has 48 rows: roll number in column A, student name in column B, and marks out of 25 for English, Maths and Science in columns C, D and E. A total column F and a grade column G follow.
Two problems have appeared. Roll numbers that should read 007, 018 and 042 are now displayed as 7, 18 and 42, and the printed list no longer sorts in the same order as the attendance register. In rows 12 and 31 the total shows 0 even though marks are visible in C, D and E. Renu has also been told to print a separate report card for each of the 48 students showing name, roll number, subject marks, total and grade, before the parents' meeting tomorrow morning.
1. Explain why the roll numbers changed and why the totals in rows 12 and 31 show 0. Describe the exact steps Renu should take to fix both problems without retyping the whole sheet.
Read the worked answer
Why the roll numbers changed: the cells were left in the General format, so the moment 007 was typed the spreadsheet stored it as the number 7. Formatting the range as Text (or typing an apostrophe first: '007) keeps the leading zeros, and the column then sorts in register order. Fix: select A2:A49 → Format Cells → Text, then retype only the three affected entries. A quick test is =LEN(A2), which should return 3; left-aligned digits are a hint that the value is already text.
Why rows 12 and 31 total 0: SUM ignores text, so those marks are almost certainly stored as text - a leading or trailing space, a non-breaking space pasted from a website, a stray apostrophe, or a cell pre-formatted as Text. Diagnosis: =ISNUMBER(C12) returns FALSE and the mark sits left-aligned.
Fix without retyping: in a helper column use =VALUE(TRIM(C12)), then copy and Paste Special → Values over the original; or select C12:E31 and use Data → Text to Columns → Finish. Recheck =SUM(C12:E12) and the total formula =SUM(C2:E2) afterwards.
2. Renu wants column G to show a grade from the total in F (60 and above = A, 45–59 = B, 30–44 = C, below 30 = Fail) and column H to show percentage, with the maximum total stored once in cell J1 as 75. Give the formulas for G2 and H2, explain the order in which the IF conditions must be tested, and why H2 must use $J$1.
Read the worked answer
G2: =IF(F2>=60,"A",IF(F2>=45,"B",IF(F2>=30,"C","Fail")))
H2: =F2/$J$1 formatted as a percentage (or =F2/$J$1*100 for a plain number).
Why the IF order matters: nested IF stops at the first condition that is TRUE. If F2>=45 were tested first, a student scoring 62 would also satisfy it and be graded B. The conditions must therefore run from the highest band down to the lowest, with the final catch-all being the remaining case.
Why $J$1: the dollar signs lock column and row, so when the formula is filled down to H49 it still reads J1. With a relative reference, H3 would look at J2, which is empty, and give #DIV/0! all the way down. Wrapping the formula as =IFERROR(F2/$J$1,"") also hides errors caused by a blank total.
Finally, widen the columns and set Print → Fit to 1 page wide so the grade and percentage do not spill onto a second sheet when printed.
3. Describe how Renu can produce 48 individual report cards from the same sheet without copy-paste, listing the steps and the settings she must check so that all 48 actually print.
Read the worked answer
Mail merge suits this because the layout is written once and only the data changes - 48 hand-made copies invite errors.
Steps
- Clean the data: one header row, no blank rows, no merged cells, and the roll number column already in Text format so 007 survives the merge.
- Save the marks sheet; it becomes the data source.
- In the word processor, type the report card template and leave gaps for the fields.
- Tools → Mail Merge (or Mailings → Start Mail Merge → Letters) → select the spreadsheet → choose the sheet → tick "First row contains column headings".
- Insert merge fields RollNo, Name, English, Maths, Science, Total, Grade into the gaps.
- Preview the first and last record - the last one catches a truncated data range.
- Finish & Merge → Print Documents, or Edit Individual Documents to check before printing.
Settings to check: merge "All" records, not "Current record"; the data range must cover rows 2–49; the printer must not be set to a wrong paper tray or duplex, or 48 cards come out back-to-back on 24 sheets.