Weighted Marks Sheet with a Copyable Formula
An HSLC elective teacher has entered marks for 40 students in a spreadsheet. Column A is roll number, B is name, C is theory marks out of 70, and D is practical marks out of 30. Cells H2, H3, H4 and H5 contain 70, 30, 60 and 40 respectively. The school wants a final weighted score out of 100: theory contributes 60% and practical contributes 40%, but each must first be scaled to its own maximum. The teacher writes in F2: =(C2/70)*60+(D2/30)*40 and copies it down to F41. A colleague says the formula will break when copied because 70, 60, 30 and 40 are fixed values, and suggests typing them again in every row. The pass mark is a weighted score of 33 or more. The teacher also needs a single cell showing how many students passed, and a chart for the principal showing the number of students in each grade band: Distinction 75+, First 60 to 74.99, Pass 33 to 59.99, and Fail below 33.
1. Will the formula copied from F2 to F41 produce correct results? Justify using absolute and relative references, and rewrite it if necessary.
Read the worked answer
Yes, the literal formula will calculate correctly when copied down. C2 and D2 are relative references, so F3 uses C3 and D3, F4 uses C4 and D4, and so on. The numbers 70, 60, 30 and 40 are constants, not cell references, so copying does not change them. The colleague is wrong about breakage, but there is a maintainability problem: if a maximum or weight changes, every row must be edited. A better formula is =(C2/$H$2)$H$4+(D2/$H$3)$H$5. The dollar signs lock the parameter cells while C2 and D2 still adjust row by row. This also makes the weighting visible and auditable instead of hiding values inside formulas.
2. For a student with C=52 and D=18, compute the weighted score and state pass or fail. Then give a formula for cell F42 that counts passes.
Read the worked answer
Theory contribution = (52/70) x 60 = 0.742857 x 60 = 44.5714. Practical contribution = (18/30) x 40 = 0.6 x 40 = 24. Total = 68.5714, which rounds to 68.57. This is at least 33, so the student passes. The count formula is =COUNTIF(F2:F41,">=33"). The range must cover exactly the 40 student rows. The comparison is written as a text string in quotes because COUNTIF expects a criteria expression. If F42 itself were included, it would create a circular reference. The teacher should also decide whether displayed values are rounded before comparison; here the underlying value 68.5714 is safely above 33, so no boundary issue arises.
3. Recommend a chart type for the grade-band counts and explain why a pie chart of individual F scores would be a poor choice.
Read the worked answer
A column or bar chart is appropriate because the data are counts in ordered categories: Distinction, First, Pass and Fail. A column chart makes comparison between category heights easy and keeps the natural order from highest to lowest. A pie chart of 40 individual scores would try to show 40 slices. Many slices would be tiny, labels would overlap, and the reader could not compare similar scores. A pie chart is meant for a small number of parts of one whole, not for a long list of individual measurements. If a pie chart is required at all, it should be used on the four grade-band totals, where the slices represent meaningful parts of the whole class.