Pro workbook: Grades, Report, Assessments, Setup, Formulas. Sample data, real formulas.
| Grade book | ||||||||||||
| Students down, assessments across. Max points and weights in the two rows above; % and letter grades follow. | ||||||||||||
| Max points | 20 | 50 | 100 | 100 | 20 | 100 | ||||||
| Weight % | 10% | 10% | 20% | 25% | 10% | 25% | 100% | ← should be 100 % | ||||
| Student | Quiz 1 | Homework 1 | Test 1 | Project | Quiz 2 | Final | Weighted % | Letter | Done | Letter | Students | |
| Ana Costa | 18 | 46 | 88 | 92 | 17 | 90 | 89.8% | A | 6 | A | 4 | |
| Ben Okafor | 14 | 38 | 71 | 80 | 13 | 74 | 73.8% | B | 6 | B | 2 | |
| Dana Ruiz | 12 | 30 | 58 | 65 | 61 | 55.1% | D | 5 | C | 2 | ||
| Elif Kaya | 20 | 50 | 95 | 97 | 19 | 96 | 96.8% | A | 6 | D | 1 | |
| Finn Walsh | 9 | 25 | 44 | 52 | 11 | 49 | 49.0% | F | 6 | F | 1 | |
| Gia Moretti | 16 | 41 | 79 | 84 | 15 | 82 | 81.0% | B | 6 | |||
| Hugo Lind | 15 | 66 | 70 | 14 | 68 | 62.2% | C | 5 | ||||
| Ida Berg | 17 | 44 | 83 | 88 | 18 | 87 | 86.7% | A | 6 | |||
| Jon Park | 11 | 33 | 62 | 58 | 12 | 60 | 60.0% | C | 6 | |||
| Kate Novak | 19 | 48 | 91 | 89 | 20 | 93 | 92.8% | A | 6 | |||
| Report | |||||||
| One line per student: overall %, letter, rank in class, assessments done and missing, best and weakest. | |||||||
| Student | Weighted % | Letter | Rank | Done | Missing | Best % | Weakest % |
| Ana Costa | 89.8% | A | 3 | 6 | 92% | 85% | |
| Ben Okafor | 73.8% | B | 6 | 6 | 80% | 65% | |
| Dana Ruiz | 55.1% | D | 9 | 5 | 1 | 65% | |
| Elif Kaya | 96.8% | A | 1 | 6 | 100% | 95% | |
| Finn Walsh | 49.0% | F | 10 | 6 | 55% | 44% | |
| Gia Moretti | 81.0% | B | 5 | 6 | 84% | 75% | |
| Hugo Lind | 62.2% | C | 7 | 5 | 1 | 75% | |
| Ida Berg | 86.7% | A | 4 | 6 | 90% | 83% | |
| Jon Park | 60.0% | C | 8 | 6 | 66% | 55% | |
| Kate Novak | 92.8% | A | 2 | 6 | 100% | 89% | |
| Assessments | ||||||||
| How each assessment went: class average as a % of max, median, high, low, how many did it. | ||||||||
| Assessment | Max | Weight | Average % | Median % | High | Low | Done | Missing |
| Quiz 1 | 20 | 10% | 75.5% | 77.5% | 20 | 9 | 10 | |
| Homework 1 | 50 | 10% | 78.9% | 82.0% | 50 | 25 | 9 | 1 |
| Test 1 | 100 | 20% | 73.7% | 75.0% | 95 | 44 | 10 | |
| Project | 100 | 25% | 77.5% | 82.0% | 97 | 52 | 10 | |
| Quiz 2 | 20 | 10% | 77.2% | 75.0% | 20 | 11 | 9 | 1 |
| Final | 100 | 25% | 76.0% | 78.0% | 96 | 49 | 10 | |
| Hardest | Test 1 | |||||||
| Easiest | Homework 1 | |||||||
| Class weighted average | 74.7% |
| Setup | ||||
| Assessment names, and the grade bands. | ||||
| Assessment | From % | Letter | ||
| Quiz 1 | 0 | F | ||
| Homework 1 | 50 | D | ||
| Test 1 | 60 | C | ||
| Project | 70 | B | ||
| Quiz 2 | 85 | A | ||
| Final | ||||
| Bands must be ascending: the letter is the last threshold the % reaches (MATCH with type 1). Six assessments — the columns on Grades; max points and weights are on that sheet. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Grades · Weighted % | SUMPRODUCT(B8:G8/$B$5:$G$5,$B$6:$G$6)/SUM($B$6:$G$6) | Each score as a share of its max, times its weight, added up, over the total weight. A blank score is a zero — not submitted. |
| Grades · Letter | INDEX(band_letters,MATCH(H8*100,band_min,1)) | MATCH type 1 on ascending thresholds returns the last band the % reaches — the standard way to do grade bands without nested IFs. |
| Grades · Missing (per assessment) | COUNTA($A$8:$A$47)-COUNT(B8:B47) | Students minus scores entered. |
| Report · Rank | COUNTIF(Grades!$H$8:$H$47,">"&B5)+1 | How many have a higher % plus one. Ties share a rank. |
| Report · Best % | SUMPRODUCT(MAX(Grades!B8:G8/Grades!$B$5:$G$5)) | Highest score-over-max across the row. Wrapped in SUMPRODUCT so the division runs over the whole row without array entry — a bare MAX(range/range) silently takes only the first cell in Excel 2016 and LibreOffice. MIN the same way for the weakest. |
| Assessments · Average % | AVERAGE(Grades!B8:B47)/B5 | Class average over the max — comparable across assessments with different totals. |
| Assessments · Hardest | INDEX(A5:A10,MATCH(MIN(D5:D10),D5:D10,0)) | The assessment with the lowest average %. |