Step-by-Step Procedure: Class Performance Data Analysis
Step-by-Step Procedure: Class Performance Data Analysis
Overview:
This activity teaches students how to use core spreadsheet functions (=SUM, =COUNT,
=MIN, =MAX, =COUNTIF, =AVERAGE) to analyze class scores and produce meaningful
summaries.
Objectives:
1. Use formulas to compute averages, totals, counts, and identify min/max values.
2. Apply COUNTIF to answer conditional questions (e.g., how many scored >= 85).
3. Use conditional formatting to highlight highest and lowest grades.
Materials:
- A computer with Microsoft Excel, Google Sheets, or LibreOffice Calc
- The sample student data (below)
Procedure (step-by-step):
1. Open a new spreadsheet and create the header row in row 1:
A1: STUDENT NAME B1: SCORE 1 C1: SCORE 2 D1: SCORE 3 E1: FINAL GRADE
- Make it BOLD, ALL CAPITAL and use CAMBRIA (BODY) for the font. For the Font size,
make it 12.
- Adjust the Column width A3: 23.00 B1-D3: 14.00 E1: 18:00
2. Enter the student names and scores starting in row 2 (use the sample table above).
STUDENT NAME SCORE 1 SCORE 2 SCORE 3
ANNA 65 87 64
BEN 68 85 76
CARLA 82 72 76
DAVID 71 100 69
ELLA 93 80 94
FRANCIS 90 71 76
GRACE 73 99 69
HENRY 90 87 83
ISLA 78 67 95
JACK 96 84 77
KYLA 89 63 80
LIAM 81 79 65
MIA 91 63 73
NOAH 92 100 82
OLIVIA 73 100 67
PAUL 75 90 72
QUEENIE 69 61 65
RYAN 93 61 86
SOPHIA 78 93 62
TOM 79 91 97
3. Apply Borders. Select A1 to E21 → Home Tab → Font Category → Borders ribbon → click
the down arrow → select ALL BORDERS.
- Adjust the Alignment. Select Column Header B, C, D and E → Home Tab → Alignment
Category → Select CENTER
4. Compute each student’s Final Grade (average of Score 1–3):
5. Input A24: Total Score 1, A25: Total Score 2, A26: Total Score 3 then calculate Total Score
for each (below the table):
- In B24 compute the Total Score 1
- In B25 compute the Total Score 2
- In B26 compute the Total Score 3
6. Count the Number of Students:
- In A27 write: Number of Students
- In B27 enter the answer
(Note: COUNT counts numeric cells)
7. Find Highest and Lowest Final Grades:
- In A28 write: Highest Final Grade
- In B28 enter the answer
- In A29 write: Lowest Final Grade
- In B29 enter the answer
8. Count students with Final Grade ≥ 85:
- In A30 write: Count ≥ 85
- In B30 enter the COUNTIF of Final Grade ≥ 85
- Use the formula =COUNTIF(E2:E21, “>=85”)
9. Compute Overall Class Average Final Grade:
- In A31 write: Class Average
- In B31 enter the total average of the Final Grade
10. (Optional) Format the results area with bold headers and borders so values are easy to
read.
11. Apply Conditional Formatting to highlight highest (green) and lowest (red) final grades:
- Excel (quick way): Select E2:E21 → Home → Conditional Formatting → Top/Bottom
Rules → Top 10 Items → Make it Top 1 → choose a green fill, custom format select green fill;
then Bottom/Top Rules → Bottom 10 Items → Make it Top 1 → choose a red fill.
- Google Sheets (custom formula): Select E2:E6 → Format → Conditional formatting →
Under 'Format rules' choose 'Custom formula is' → For highest enter: =E2 =
MAX($E$2:$E$6) and pick a green fill. Add a second rule for lowest: =E2 = MIN($E$2:$E$6)
and pick a red fill.
11. Save your spreadsheet (File → Save As). Suggested filename: Delacruz_Juan_spreadsheet
