Detailed Solution: Single Course Grade Tracker

This solution addresses the requirements of the "Single Course Grade Tracker" assignment, focusing on fundamental Excel concepts within one worksheet.

Step 1: Setup the Worksheet

1.     Open Excel: Start a new blank workbook.

2.     Rename Sheet: Double-click on the Sheet1 tab at the bottom and rename it to Programming Grades.

Step 2: Enter Data

Navigate to the Programming Grades sheet.

1.     Enter Headers:

o    In cell A1, type Student Name.

o    In B1, type Assignment 1.

o    In C1, type Quiz 1.

o    In D1, type Midterm.

o    In E1, type Final Exam.

o    In F1, type Total Score.

o    In G1, type Percentage.

o    In H1, type Pass/Fail Status.

2.     Enter Student Data and Scores:
Starting from cell A2, enter the student names and their respective scores.

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

Step 3: Define Total Possible Marks and Pass Threshold

This step sets up the values that will be used as absolute references in your formulas.

1.     Total Possible Marks:

o    In cell K1, type Total Possible Marks:.

o    In cell L1, enter the value 135 (calculated as $20 + 15 + 40 + 60$). This represents the maximum possible score for the course based on the provided breakdown.

2.     Pass Threshold:

o    In cell K2, type Pass Threshold (%):.

o    In cell L2, enter 0.4 (or 40%).

o    Format L2 as Percentage: Select cell L2. On the Home tab, in the Number group, click the % style button. You can then click the Increase Decimal or Decrease Decimal button to show 0 decimal places if desired.

Step 4: Calculate Total Score for Each Student

1.     Enter Formula for First Student:

o    Select cell F2.

o    Type the formula: =SUM(B2:E2)

o    Press Enter. This calculates Emily Chen’s total score.

2.     Copy Formula Down:

o    Select cell F2 again.

o    Double-click the small green square (fill handle) at the bottom-right corner of cell F2. Excel will automatically copy the formula down to F8, adjusting the row references (e.g., F3 will be =SUM(B3:E3)). This demonstrates relative referencing.

Step 5: Calculate Percentage for Each Student

1.     Enter Formula for First Student:

o    Select cell G2.

o    Type the formula: =F2/$L$1

o    Press Enter.

§  F2 is a relative reference because you want it to change for each student.

§  $L$1 is an absolute reference. The dollar signs lock both the column (L) and the row (1), ensuring that no matter where you copy the formula, it always refers to the Total Possible Marks in cell L1. You can type $L$1 or, after typing L1, press F4 to toggle it to absolute.

2.     Copy Formula Down:

o    Select cell G2 again.

o    Double-click the fill handle to copy the formula down to G8.

3.     Format as Percentage:

o    Select cells G2:G8.

o    On the Home tab, in the Number group, click the % style button. You may choose to show 2 decimal places using the Increase Decimal button.

Step 6: Determine Pass/Fail Status

1.     Enter Formula for First Student:

o    Select cell H2.

o    Type the formula: =IF(G2>=$L$2,"Pass","Fail")

o    Press Enter.

§  G2 is a relative reference (the student’s percentage).

§  $L$2 is an absolute reference (the fixed pass threshold).

2.     Copy Formula Down:

o    Select cell H2 again.

o    Double-click the fill handle to copy the formula down to H8.

Step 7: Basic Class Statistics

Below your student data (e.g., starting in cell A10):

1.     Enter Headers for Statistics:

o    In A10, type Average Class Score.

o    In A11, type Highest Score.

o    In A12, type Lowest Score.

o    In A13, type Average Class Percentage.

o    In A14, type Number of Students Passed.

o    In A15, type Number of Students Failed.

2.     Calculate Statistics:

o    Average Class Score (B10): =AVERAGE(F2:F8)

o    Highest Score (B11): =MAX(F2:F8)

o    Lowest Score (B12): =MIN(F2:F8)

o    Average Class Percentage (B13): =AVERAGE(G2:G8)

§  Format B13 as Percentage with 2 decimal places.

o    Number of Students Passed (B14): =COUNTIF(H2:H8,"Pass")

o    Number of Students Failed (B15): =COUNTIF(H2:H8,"Fail")

Step 8: Formatting and Presentation

1.     Bold and Center Headers:

o    Select cells A1:H1.

o    On the Home tab, in the Font group, click Bold (B) and in the Alignment group, click Center.

2.     Apply Borders to Data Table:

o    Select the entire range of your student data, including headers and all calculated columns (e.g., A1:H8).

o    On the Home tab, in the Font group, click the Borders dropdown and choose All Borders.

3.     Adjust Column Widths:

o    Select all columns containing data (e.g., A:H).

o    Double-click the line between any two column headers (e.g., between A and B). This will AutoFit the columns to the content.

4.     Conditional Formatting for Pass/Fail Status:

o    Select the range H2:H8 (the Pass/Fail Status column).

o    On the Home tab, go to Conditional Formatting > Highlight Cells Rules > Text that Contains....

o    For "Pass": In the dialog box, type Pass. From the dropdown, choose Green Fill with Dark Green Text. Click OK.

o    For "Fail": Repeat the steps. With H2:H8 still selected, go to Conditional Formatting > Highlight Cells Rules > Text that Contains.... Type Fail. From the dropdown, choose Light Red Fill with Dark Red Text. Click OK.