Scholarship Ranking With Unequal Subject Maximums

• Different maximum marks: adding raw marks gives Additional Mathematics twice the weight of every other subject. A mark of 150/200 counts as 150, while 75/100 in another subject counts as 75. The student with 150 in Additional Mathematics and 30 in English can total 380 (with four marks of 50), while a consistent student with 60 in every subject totals 360. Raw ranking puts the first student ahead; percentage ranking gives 50.8% against 60%.
• Absent recorded as zero: a zero from absence is treated as a genuine score. This can be defensible only if the rule says so; otherwise it punishes illness and may reward a student who skips a hard subject.
• Input-order tie-break: using the row number means the award list changes when the spreadsheet is re-sorted. The sort is not stable with respect to a defined key, so the 40th place is arbitrary.
• No cutoff tie rule: equal totals at 40th place are split by row order, not by a published rule.
• Hidden eligibility: the program does not check required passes, so a student may be awarded a scholarship without meeting English or Mathematics requirements.

Define the rule before running it and make every step deterministic.

1. Convert each mark to a percentage of that subject's maximum: mark / max * 100. For equal weighting, average the six percentages. If the school wants core subjects to count more, publish fixed weights that sum to 100.
2. Handle absence explicitly. One defensible policy is: absent = 0 for ranking, but a student absent from a required subject is not eligible. Another is to rank only on subjects taken and require a minimum number of subjects. Either way, record the policy and apply it to everyone.
3. Add eligibility checks before ranking: pass in English and Mathematics, and at least five subjects taken.
4. Sort by the computed score descending, then by a published tie-breaker: core-subject average, then English, then a witnessed random draw. Never use the spreadsheet row number.
5. At the 40th place, either award all students with the same score if the budget allows, or apply the tie-breakers. Record the decisions.
6. Use exact fractions or a fixed number of decimal places for the score, and round only for display, so equal marks compare equal.

Test with small hand-checked cases and with the reported examples.

• Same totals entered in different row orders must produce the same award list.
• Re-sorting the source spreadsheet must not change the list.
• 150/200 in Additional Mathematics, 30 in English and 50 in four subjects versus 60 in all six: under equal percentage weighting the second student must rank higher.
• A student absent in one subject versus a student with a genuine zero: the eligibility rule must be applied consistently.
• Tie at 40th place: all tied students or the published tie-breaker must decide, not row order.
• Boundary cases: exactly 40 awards, 41st place one mark below, all zero marks, missing mark, duplicate student ID, and a mark above the subject maximum.
• Volume case: 620 students and 150 new intake; the report should run once, produce the same result on repeat, and finish in acceptable time. Expected result: a published rule, a deterministic list, and an audit trail showing each score and tie decision.