Student Result System with Grades, Rank & Pass/Fail

A student result system enters marks, computes totals and grades, and ranks students. You build it in Excel and use Copilot and ChatGPT for the grading logic and report layout.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It takes subject marks, finds total, average, grade, and rank, and shows pass or fail.
• Where it is used: Schools and colleges producing result sheets.
• What you learn: SUM, AVERAGE, nested IF/IFS grading, RANK, and AI prompts.

Features

• Marks entry per subject
• Total and average
• Letter grade with IFS
• Rank among students
• AI-written report card text

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A list of students and subjects

Folder Structure

student-result-system.xlsx
│
├── Sheet: Marks    (StudentID, Name, Math, Sci, Eng, Total, Avg, Grade, Rank)
├── Sheet: Report   (per-student card)
└── Module: StuMacros (optional VBA)

• Marks: the main sheet.
• Report: printable cards.
• StuMacros: optional helpers.

Step-by-Step Development

Step 1 – Total and average

What are we building?

Total and average columns from subject marks.

Why do we need it?

Grade and rank depend on the average.

How do we build it?

=SUM(C2:E2)
=AVERAGE(C2:E2)

• What it does: adds the three subject marks; averages them.
• Why needed: base numbers for grading.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write Excel formulas for total and average of three subject columns C, D, E."

Step 2 – Grade with IFS

What are we building?

A letter grade from the average.

Why do we need it?

Grades are easier to read than numbers.

How do we build it?

=IFS(F2>=90,"A", F2>=75,"B", F2>=60,"C", F2>=50,"D", TRUE,"F")

• What it does: checks the average from high to low and returns the first matching grade.
• Important line: TRUE,"F" is the fallback for below 50.
• Why needed: one formula grades every student.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write an IFS formula that returns A for >=90, B >=75, C >=60, D >=50, else F."

Step 3 – Rank

What are we building?

A rank of each student by total.

Why do we need it?

Shows who scored highest.

How do we build it?

=RANK(F2, F$2:F$100)

• What it does: ranks the total in F2 against the whole total column.
• Important line: F$2:F$100 is an absolute range so it stays fixed when copied.
• Why needed: compares all students.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write a RANK formula that ranks a total against an absolute range F2:F100."

Step 4 – Pass/Fail and AI card

What are we building?

A pass/fail label and an AI report sentence.

Why do we need it?

Quick status and a readable card.

How we build it?

=IF(F2>=50, "Pass", "Fail")

• What it does: passes if average is 50 or more.
• Why needed: clear status per student.

Use AI (Copilot & ChatGPT)

Paste a student's row into ChatGPT:

"Write one sentence for a report card: name, average, grade, and pass/fail."

Complete Source Code

Marks formulas

=SUM(C2:E2)
=AVERAGE(C2:E2)
=IFS(F2>=90,"A", F2>=75,"B", F2>=60,"C", F2>=50,"D", TRUE,"F")
=RANK(F2, F$2:F$100)
=IF(F2>=50, "Pass", "Fail")

AI prompts

ChatGPT: "Total and average of columns C,D,E."
Copilot: "IFS grade A>=90 down to F."
ChatGPT: "RANK total against absolute range F2:F100."
ChatGPT: "One report-card sentence from a student row."

Running the Project

1. Open student-result-system.xlsx.
2. Enter marks for each student.
3. Total, grade, rank, and pass/fail fill in.
4. Expected output: a full result sheet.

Testing

Test Case 1

Input: Math 90, Sci 80, Eng 70. Expected Output: Total 240, Avg 80, Grade B, Pass. Explanation: Average 80 maps to B.

Test Case 2

Input: All 40. Expected Output: Avg 40, Grade F, Fail. Explanation: Below 50 fails and grades F.

Test Case 3

Input: Top total 240 among 30 students. Expected Output: Rank 1. Explanation: Highest total ranks first.

Common Errors

Grade wrong

Problem: Grade too high. Reason: IFS order wrong (checked low first). Solution: Keep conditions high to low; first true wins.

Rank repeats

Problem: Same rank for different totals. Reason: Range included a blank or text. Solution: Use a clean numeric absolute range.

Average off

Problem: Divides by wrong count. Reason: Included the Total column in AVERAGE. Solution: Average only the subject columns.

Improvements

• Add more subjects and weight them
• Use XLOOKUP to pull student names
• Build a class topper chart
• Ask ChatGPT for improvement tips per student
• Export report cards to PDF

Challenge Exercises

1. Add a weighted average where Math counts double.
2. Write a formula for the class average per subject.
3. Use ChatGPT to write VBA that prints one report card per student.
4. Build a chart of grade distribution (A, B, C...).
5. Let ChatGPT flag students at risk of failing.