Weighted Grade Calculator

About this template

When to use it

A weighted mark book for the Korean achievement-standards system: two performance tasks and one written assessment per student, combined into a single weighted total and an A–E achievement level. Three sheets — Dashboard, Input data, Usage guide. Replace the template subtitle in B3 with your subject and term.

What the dashboard holds

Key metrics in B7:E7 — rows entered, Sum of weighted totals, Average weighted total, Highest weighted total — read column G of Input data. Summary by class in B12:F14 groups the same column by the class in column C. The Year-group average by assessment round table (B18:C21) is typed in by hand.

Filling order

1. Rows 2–21 of Input data take No., Student, Class, Written 40%, Performance A 30%, Performance B 30%, Weighted total, Achievement level (A–H). Eight sample students ship across classes 3-1, 3-2 and 3-3.
2. Type marks only into D, E and F. Columns G and H are formula cells and fill themselves in (note 5); typing a number over them destroys the calculation for that student.
3. The class labels on the Dashboard are 3-1, 3-2, 3-3 (B12:B14) and must match column C character for character.
4. Replace the four round averages (Written 1, Performance 1, Written 2, Performance 2: 78 · 84 · 81 · 87) with your own.
5. To add students, copy the last student row and paste it (note 8) so the G and H formulas travel with it.

How the formulas calculate

• Each weighted total is =ROUND(D2*0.4+E2*0.3+F2*0.3,1). To reweight the assessment, edit those three coefficients — they must still add to 1 (note 6).
• The level is =IF(G2=””,””,IF(G2>=90,”A”,IF(G2>=80,”B”,IF(G2>=70,”C”,IF(G2>=60,”D”,”E”))))). The cut-offs are the 90, 80, 70 and 60 inside it (note 7); change them if your school sets different boundaries.
• The class block is =COUNTIF(‘Input data’!$C$2:$C$21,$B12), =SUMIF(‘Input data’!$C$2:$C$21,$B12,’Input data’!$G$2:$G$21) and the matching AVERAGEIF, with a share of =IFERROR(C12/COUNT(‘Input data’!$G$2:$G$21),0).
• Under the round table, =SUM(C18:C21) and =AVERAGE(C18:C21) — 330 and 82.5 as shipped.
• Nothing counts the levels. A distribution is one formula away: =COUNTIF(‘Input data’!$H$2:$H$21,”A”) repeated for B to E.

Know this first

The Dashboard’s second metric is labeled Sum of weighted totals, and it is exactly that — 638.4 across eight students. It is a checksum, not a class result; the number to read is the average beside it. Watch the boundary too: the eighth student’s marks of 56, 65 and 60 round to 59.9, one tenth below the D cut-off, so the level formula returns E. If your school rounds to a whole number before grading, change the ROUND to =ROUND(D2*0.4+E2*0.3+F2*0.3,0) and be consistent about it from the first student onward.

Examples

• Row 2 — 1 · Kim ○○ · 3-1 · 92 · 88 · 95 → weighted total 91.7, level A.
• Across the eight shipped students the average weighted total reads 79.8 and the highest 95.0.
• Class 3-1 holds three of the eight rows, so its share cell shows 37.5% and its average 80.2; 3-2 averages 84.9 and 3-3 averages 71.6.

Common mistakes

• Typing a mark straight into column G, which overwrites the formula and hides it from the next person.
• Changing one coefficient in row 2 only, so students end up graded on different weightings.
• Inserting a row instead of copying the last one, which leaves G and H empty and the student uncounted.
• Circulating the sheet with names attached. Share class-level averages, or replace names with numbers first.