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).