Applying Formulas and Grade Calculations0%

Applying Formulas and Grade Calculations

Beginner12 min read•Updated: Jul 11, 2026

Applying Formulas and Grade Calculations

With the raw score records in place, this second phase automates computation: calculating total points with SUM, computing percentage scores with AVERAGE, determining Pass/Fail status with logical IF, and generating class-wide statistics (MAX, MIN, AVERAGE).

Step 1: Computing Total Marks with SUM

In cell H4 (Total Marks for Aarav Sharma):

1
Type the formula:

1   =SUM(C4:G4)

1
Press Enter. Excel computes 439.
2
Double-click the green AutoFill handle at the bottom-right of cell H4 to calculate totals for all 10 students instantly!

Step 2: Calculating Percentage (%)

Since there are 5 subjects with a maximum of 100 marks each, the total possible score is 500:

1
In cell I4, type:

1   =H4 / 500

(Alternatively: =AVERAGE(C4:G4) / 100).

1
Press Enter. Cell I4 displays 0.878.
2
Select column range I4:I13.
3
Click the Percent Style (%) button on the Home tab, and click Increase Decimal once.
4
The column now displays formatted percentages: 87.8%!
5
AutoFill down to row 13.

Step 3: Determining Pass / Fail with the IF Function

A student passes if their overall percentage is 40% (0.40) or greater:

1
In cell J4, enter the logical test:

1   =IF(I4>=0.40, "PASS", "FAIL")

1
Double-click the AutoFill handle.
2
Noah Patel (32 in Math, total 230, 46%) passes overall, but notice how conditional formatting can flag subject-level failures!

Step 4: Class Summary Statistics (Rows 15 to 18)

Below the student table, build a summary dashboard:

In cell B15: Class Average Score
In cell B16: Highest Score in Class (Topper)
In cell B17: Lowest Score in Class
In cell B18: Total Students Evaluated

The Aggregate Formulas:

Class Average Percentage (cell I15):

1  =AVERAGE(I4:I13)

Highest Total Marks (cell H16):

1  =MAX(H4:H13)

Lowest Total Marks (cell H17):

1  =MIN(H4:H13)

Total Students Evaluated (cell B18):

1  =COUNTA(A4:A13)

Step 5: Highlight Failures with Conditional Formatting

1
Select the subject score cells C4:G13.
2
Go to Home > Conditional Formatting > Highlight Cells Rules > Less Than...
3
Enter 40 and select Light Red Fill with Dark Red Text.
4
Instantly, any score under 40 (such as Noah Patel's 32 in Math) is illuminated in bright red!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which formula calculates the Total Marks across 5 subjects located in cells C4 through G4?

Question 2

If a student's total score is in cell H4, which formula correctly computes their percentage based on 500 total points?

Question 3

Which logical formula returns "PASS" if a percentage in cell I4 is greater than or equal to 40% (0.40), and "FAIL" otherwise?

Question 4

Which function tallies how many student ID codes exist in the range A4:A13?

Question 5

How can you highlight all individual subject scores below 40 in light red automatically?

Next Lesson

Visualizing Performance with a Chart

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating a Student Report Card Layout	Visualizing Performance with a Chart

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Visualizing Performance with a Chart

Applying Formulas and Grade Calculations

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Applying Formulas and Grade Calculations

Step 1: Computing Total Marks with SUM

In cell H4 (Total Marks for Aarav Sharma):

1
Type the formula:

1   =SUM(C4:G4)

1
Press Enter. Excel computes 439.
2
Double-click the green AutoFill handle at the bottom-right of cell H4 to calculate totals for all 10 students instantly!

Step 2: Calculating Percentage (%)

Since there are 5 subjects with a maximum of 100 marks each, the total possible score is 500:

1
In cell I4, type:

1   =H4 / 500

(Alternatively: =AVERAGE(C4:G4) / 100).

1
Press Enter. Cell I4 displays 0.878.
2
Select column range I4:I13.
3
Click the Percent Style (%) button on the Home tab, and click Increase Decimal once.
4
The column now displays formatted percentages: 87.8%!
5
AutoFill down to row 13.

Step 3: Determining Pass / Fail with the IF Function

A student passes if their overall percentage is 40% (0.40) or greater:

1
In cell J4, enter the logical test:

1   =IF(I4>=0.40, "PASS", "FAIL")

1
Double-click the AutoFill handle.
2
Noah Patel (32 in Math, total 230, 46%) passes overall, but notice how conditional formatting can flag subject-level failures!

Step 4: Class Summary Statistics (Rows 15 to 18)

Below the student table, build a summary dashboard:

In cell B15: Class Average Score
In cell B16: Highest Score in Class (Topper)
In cell B17: Lowest Score in Class
In cell B18: Total Students Evaluated

The Aggregate Formulas:

Class Average Percentage (cell I15):

1  =AVERAGE(I4:I13)

Highest Total Marks (cell H16):

1  =MAX(H4:H13)

Lowest Total Marks (cell H17):

1  =MIN(H4:H13)

Total Students Evaluated (cell B18):

1  =COUNTA(A4:A13)

Step 5: Highlight Failures with Conditional Formatting

1
Select the subject score cells C4:G13.
2
Go to Home > Conditional Formatting > Highlight Cells Rules > Less Than...
3
Enter 40 and select Light Red Fill with Dark Red Text.
4
Instantly, any score under 40 (such as Noah Patel's 32 in Math) is illuminated in bright red!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which formula calculates the Total Marks across 5 subjects located in cells C4 through G4?

Question 2

If a student's total score is in cell H4, which formula correctly computes their percentage based on 500 total points?

Question 3

Which logical formula returns "PASS" if a percentage in cell I4 is greater than or equal to 40% (0.40), and "FAIL" otherwise?

Question 4

Which function tallies how many student ID codes exist in the range A4:A13?

Question 5

How can you highlight all individual subject scores below 40 in light red automatically?

Next Lesson

Visualizing Performance with a Chart

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating a Student Report Card Layout	Visualizing Performance with a Chart

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next