EMAIL LESSON 9: Setting Up a Basic Grade Book
Subject Line: Day 9: Your Professional Grade Book - Better Than Paper! 📚
Yesterday you learned powerful analysis formulas. Today we create a complete grade book that will handle all your subjects and assessments in one organized place.
Today's 15-Minute Lesson: Complete Grade Book Setup
Think of this as your digital teacher's record book - everything organized, calculated automatically, and always backed up.
What You'll Learn Today:
Design a multi-subject grade book
Use multiple sheets for organization
Link data between sheets
Create professional grade summaries
Today's Practice: Build Your Grade Book (12 minutes)
Step 1: Plan Your Grade Book Structure (2 minutes) We'll create a grade book with:
Sheet 1: Mathematics
Sheet 2: Physical Sciences
Sheet 3: Summary Report
Step 2: Set Up Mathematics Sheet (4 minutes)
Right-click on "Sheet1" tab at bottom
Choose "Rename" and type "Mathematics"
Create these headers in row 1:
A1: Student Name
B1: Student Number
C1: Test 1 (25%)
D1: Test 2 (25%)
E1: Assignment (20%)
F1: Project (30%)
G1: Final Mark
H1: Symbol
Step 3: Enter Sample Data (3 minutes) Add these 6 students:
Sipho Mthembu, 2024001, 78, 82, 85, 76
Lerato Dlamini, 2024002, 65, 70, 68, 72
Michael van Zyl, 2024003, 92, 89, 94, 88
Aisha Patel, 2024004, 56, 62, 58, 64
John Mthembu, 2024005, 73, 77, 75, 79
Nomsa Khoza, 2024006, 45, 48, 52, 55
Step 4: Create Weighted Average Formula (3 minutes)
Click G2 (Final Mark for Sipho)
Type this formula: =C20.25+D20.25+E20.2+F20.3
Press Enter
Copy this formula to G3:G7
Each student now has a properly weighted final mark!
Understanding Weighted Averages:
Test 1: 25% of final mark
Test 2: 25% of final mark
Assignment: 20% of final mark
Project: 30% of final mark
Formula calculates: (Test1×0.25) + (Test2×0.25) + (Assignment×0.2) + (Project×0.3)
Grade Symbol System: Add symbols based on DBE requirements:
Click H2 (Symbol for Sipho)
Type: =IF(G2>=80,"A",IF(G2>=70,"B",IF(G2>=60,"C",IF(G2>=50,"D",IF(G2>=40,"E",IF(G2>=30,"F","G"))))))
Copy to H3:H7
Students automatically get correct symbols!
Symbol Reference:
A: 80-100% (Outstanding)
B: 70-79% (Meritorious)
C: 60-69% (Substantial)
D: 50-59% (Adequate)
E: 40-49% (Moderate)
F: 30-39% (Elementary)
G: 0-29% (Not Achieved)
Professional Grade Book Features:
Quick Analysis Section: Add in rows 10-15:
Class Average: =AVERAGE(G2:G7)
Highest Mark: =MAX(G2:G7)
Lowest Mark: =MIN(G2:G7)
Pass Rate: =COUNTIF(G2:G7,">=40")/COUNT(G2:G7)*100
Why This Grade Book is Better Than Paper:
Automatic calculations (no arithmetic errors)
Instant updates when you change marks
Professional symbols assigned automatically
Easy to backup and share
Clear audit trail of all marks
Weighted averages calculated correctly
Common Grade Book Questions:
Q: Can I change the weightings? A: Yes! Just change the decimal values in the formula (0.25, 0.2, 0.3, etc.)
Q: What if a student was absent for a test? A: Leave the cell blank for now. We'll learn to handle missing data later.
Q: Can I add more assessments? A: Absolutely! Add columns and adjust the weighting formula.
Today's Success Check:
[ ] I created a professional grade book layout
[ ] I can calculate weighted averages correctly
[ ] Symbols are assigned automatically
[ ] I understand how to modify weightings
[ ] My grade book looks professional
Tomorrow We'll Learn: Creating visual charts from your grade data to show student progress and class performance.
[📚 Download Grade Book Template] ← Link to: https://yoursite.com/day9-gradebook
Your grade book is now more professional than most!
Best regards, Your Excel Course Team