TheGREATrandino
New Member
- Joined
- Sep 9, 2020
- Messages
- 15
- Office Version
- 365
- Platform
- Windows
Trying to track grades. Each grade is assigned one of several categories. Each category is different weight. I want to see the average for each category and the overall average..
I can create a pivot table that shows the average of category, but I can't figure out how to compute the weighted average of the averages.
Pivot Table
I can create a pivot table that shows the average of category, but I can't figure out how to compute the weighted average of the averages.
| Grades.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | |||
| 3 | Date | Item | Type | Score | Grade | Weight | Weighted Score | ||
| 4 | 1/10/2022 | BB quiz 1 | Daily | 93 | A | 0.08 | 7.4 | ||
| 5 | 1/18/2022 | BB Quiz 2 | Daily | 100 | A | 0.08 | 8 | ||
| 6 | 1/24/2022 | BB Quiz 3 | Daily | 70 | C | 0.08 | 5.6 | ||
| 7 | 1/31/2022 | BB Quiz 4 | Daily | 90 | A | 0.08 | 7.2 | ||
| 8 | 2/7/2022 | BB Quiz 5 | Daily | 100 | A | 0.08 | 8 | ||
| 9 | 2/14/2022 | BB Quiz 6 | Daily | 84 | B | 0.08 | 6.72 | ||
| 10 | 2/18/2022 | BB Quiz 7 | Daily | 78 | C | 0.08 | 6.2 | ||
| 11 | 1/12/2022 | Introductions: Collaborations | Daily | 100 | A | 0.08 | 8 | ||
| 12 | 1/13/2022 | Syllabus | Daily | 95 | A | 0.08 | 7.6 | ||
| 13 | 1/24/2022 | Lab 1 | Lab | 80 | B | 0.07 | 5.6 | ||
| 14 | 2/15/2022 | Lab 2 | Lab | 98 | A | 0.07 | 6.825 | ||
| 15 | 2/22/2022 | Lab 3 | Lab | 93 | A | 0.07 | 6.475 | ||
| 16 | 1/3/2022 | Test 1 | Test | 62 | D | 0.6 | 37.14 | ||
| 17 | 2/3/2022 | Web Assign 1 | Web Assign | 97 | A | 0.05 | 4.831 | ||
| 18 | 2/22/2022 | Web Assign 2 | Web Assign | 96 | A | 0.05 | 4.807857143 | ||
| 19 | Total | 1334 | 1.63 | 8.693257143 | |||||
Grades | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G4:G18 | G4 | =XLOOKUP([@Type],rngGradeItem,tbGradeTypes[Weight]) |
| H4:H18 | H4 | =G4*E4 |
| G19 | G19 | =SUBTOTAL(109,[Weight]) |
| H19 | H19 | =SUBTOTAL(101,[Weighted Score]) |
| E14 | E14 | =195/2 |
| E15 | E15 | =(100+85)/2 |
| F4:F18 | F4 | =VLOOKUP(E4,tbLetterGrades,2) |
| E18 | E18 | =(100+89.2+100+100+92.4+100+91.5)/7 |
| E19 | E19 | =SUBTOTAL(109,[Score]) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| rngGradeItem | =Tables!$B$5:$B$9 | G4:G18 |
| rngGradeWt | =Tables!$C$5:$C$9 | G4:G18 |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| D4:D18 | List | =rngGradeItem |
Pivot Table
| Row Labels | Average of Score | Sum of Weighted Score | Sum of Field1 |
| Daily | 89.88888889 | 64.72000 | 582.480000 |
| BB quiz 1 | 92.5 | 7.4 | 7.4 |
| BB Quiz 2 | 100 | 8 | 8 |
| BB Quiz 3 | 70 | 5.6 | 5.6 |
| BB Quiz 4 | 90 | 7.2 | 7.2 |
| BB Quiz 5 | 100 | 8 | 8 |
| BB Quiz 6 | 84 | 6.72 | 6.72 |
| BB Quiz 7 | 77.5 | 6.2 | 6.2 |
| Introductions: Collaborations | 100 | 8 | 8 |
| Syllabus | 95 | 7.6 | 7.6 |
| Lab | 90 | 18.9 | 56.7 |
| Lab 1 | 80 | 5.6 | 5.6 |
| Lab 2 | 97.5 | 6.825 | 6.825 |
| Lab 3 | 92.5 | 6.475 | 6.475 |
| Test | 61.9 | 37.14 | 37.14 |
| Test 1 | 61.9 | 37.14 | 37.14 |
| Web Assign | 96.38857143 | 9.638857143 | 19.27771429 |
| Web Assign 1 | 96.62 | 4.831 | 4.831 |
| Web Assign 2 | 96.15714286 | 4.807857143 | 4.807857143 |
| Grand Total | 88.91180952 | 130.3988571 | 2173.893743 |
