Basic Excel Assignment – Single Course Grade Tracker
Assignment: Single Course Grade Tracker – "My
Grades, My Sheet"
Purpose/Objective:
This assignment is designed to solidify your understanding of fundamental Excel
operations: efficient data entry, applying simple formulas, using relative and
absolute cell references correctly, and basic formatting within a single
worksheet. This forms the essential groundwork for managing and analyzing data.
Scenario:
You are a student trying to keep track of your performance in a single course,
"Introduction to Programming." You have received scores for various
assessments and need to organize them in Excel to calculate your total score,
percentage, and determine your pass/fail status. You also want to see some
basic class statistics.
Instructions:
1.
Setup the Worksheet:
o
Open a new Excel workbook.
o
Rename Sheet1 to Programming Grades.
2.
Enter Data:
o
Starting from cell A1 in the Programming Grades sheet, enter the following
header row:
Student Name | Assignment 1 | Quiz
1 | Midterm | Final
Exam | Total Score | Percentage | Pass/Fail
Status
o
Enter data for at least 7 students (you can use your friends’
names, or hypothetical names). Input scores for each assessment. Make sure some
students have scores that would result in a "Pass" and some a
"Fail" later.
Student
Name |
Assignment
1 |
Quiz 1 |
Midterm |
Final Exam |
Emily Chen |
18 |
12 |
35 |
55 |
David Lee |
15 |
10 |
28 |
45 |
Sarah Kim |
19 |
14 |
38 |
58 |
John Doe |
10 |
5 |
20 |
30 |
Maria
Garcia |
17 |
11 |
32 |
50 |
Alex Wang |
12 |
8 |
25 |
38 |
Linda
Brown |
16 |
13 |
34 |
52 |
3.
Define Total Possible Marks and Pass Threshold:
o
In cell K1 (or any empty cell outside your
main data table), type Total
Possible Marks:.
o
In cell L1, calculate the sum of the
maximum marks for all assessments (20 + 15 + 40 + 60 = 135). So, enter 135 in L1.
o
In cell K2, type Pass Threshold (%):.
o
In cell L2, enter 0.40 (for 40%). Format this cell as Percentage with 0 decimal places.
4.
Calculate Total
Score for Each
Student:
o
In cell F2, write a formula to sum the
scores for Assignment 1, Quiz 1, Midterm, and Final Exam for the first student.
o
Hint: Use the SUM function.
o
Task: Ensure you
use relative references so that when you copy this formula down, it
automatically calculates the total for each student in their respective row.
o
Copy the formula down for all other students.
5.
Calculate Percentage for Each Student:
o
In cell G2, write a formula to calculate
the percentage for the first student. This will be their Total Score divided by the Total Possible Marks (from cell L1).
o
Task: This is a
crucial step for understanding references. Your formula must use an absolute
reference for the Total
Possible Marks (cell L1) so that when you copy the formula down, every
student’s total score is divided by the same overall maximum.
o
Copy the formula down for all other students.
o
Task: Format
column G as Percentage with 2 decimal places.
6.
Determine Pass/Fail
Status:
o
In cell H2, write
an IF statement: If the Percentage (from column G) is greater than
or equal to the Pass Threshold
(%) (from cell L2), display "Pass";
otherwise, display "Fail".
o
Task: Use an absolute
reference for the Pass
Threshold (%) (cell L2).
o
Copy the formula down for all other students.
7.
Basic Class Statistics:
o
Below your student data (e.g., starting in cell A10 or so), calculate the following:
§ Average Class Score
§ Highest Score
§ Lowest Score
§ Average Class Percentage
§ Number of Students Passed
§ Number of Students Failed
o
Task: Use AVERAGE, MAX, MIN functions for scores/percentages.
o
Task: Use COUNTIF for "Number of Students Passed" and
"Number of Students Failed".
o
Format the
average, highest, and lowest percentage cells as Percentage with 2 decimal places.
8.
Formatting and Presentation:
o
Apply Bold and Center Alignment to your main header row (A1:H1).
o
Apply All Borders to your entire student data
table (from headers down to the last student’s data).
o
Adjust column widths to ensure all text and numbers are clearly visible.
o
Conditional Formatting:
§ Select the Pass/Fail Status column (H2 downwards to the last student).
§ Go to Home tab > Conditional Formatting > Highlight Cells Rules > Text that Contains….
§ Set a rule to highlight cells
containing "Pass" with Green Fill with Dark Green Text.
§ Set another rule to highlight
cells containing "Fail" with Light Red Fill with Dark Red Text.
Deliverables:
Submit the completed Excel file (.xlsx).