Guided project · 5 steps Cellwise Plus
Parent conference report in Excel
Weight grades, assign letters, check attendance and build the list of families to call.
You will use: ROUND, XLOOKUP, IF, OR, TEXTJOIN, FILTER
Starting data
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Student | Classwork | Tests | Missing | Absences | Grade % | Letter | Attendance | Call home | Classwork weight | 0.4 | |
| 2 | Mia | 92 | 88 | 0 | 1 | Tests weight | 0.6 | |||||
| 3 | Noah | 74 | 61 | 4 | 3 | Days so far | 40 | |||||
| 4 | Zara | 85 | 90 | 1 | 0 | |||||||
| 5 | Theo | 68 | 72 | 2 | 6 | Min % | Letter | |||||
| 6 | Lucy | 95 | 97 | 0 | 2 | 0 | F | |||||
| 7 | Sam | 80 | 76 | 3 | 1 | 60 | D | |||||
| 8 | 70 | C | ||||||||||
| 9 | 80 | B | ||||||||||
| 10 | 90 | A | ||||||||||
| 11 | ||||||||||||
| 12 | Families to call |
Build it step by step
Step 1
Classwork counts 40% (L1) and tests 60% (L2). Write the weighted grade, rounded to 1 decimal, in F2 and fill to F7.
Step 2
Look up each letter from the scale in K6:L10. Write it in G2 and fill to G7.
Step 3
Attendance rate is 1 minus absences divided by the days so far in L3. Write it in H2 and fill to H7.
Step 4
Mark "Call" in I2 if the letter is D or F, there are 3 or more missing assignments, or attendance is below 90%. Otherwise leave it blank (""). Fill to I7.
Step 5
Put the names of every family to call in one cell, L12, separated by commas.