Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
A sample letter grade standard is shown in the dataset below.
Method 1 – Use the IF and the AVERAGE Functions to Calculate Average Letter Grades
Use the AVERAGE function and the IF function to get the letter grade.
The IF function checks whether a condition is met and, returns a value if True and another if False.
The AVERAGE function returns the average (arithmetic mean) of its arguments.
Steps:
- Add two new rows to the sheet.
- Calculate the average score in C12.
- Enter the following formula.
=AVERAGE(F6:F10)
- Press Enter to see the result.
- Enter another formula in C13.
=IF(C12>=90,"A",IF(C12>=80,"B",IF(C12>=70,"C",IF(C12>=60,"D","F"))))
- Press Enter.
This is the output.
Read More: How to Make Result Sheet in Excel (with Easy Steps)
Method 2 – Apply the IFS Function
Use the Excel IFS function to get the letter grade using the average value.
The IFS function checks whether one or more conditions are met and, returns a value corresponding to the first TRUE condition.
Steps:
- Go to C13 and enter the formula.
=IFS(C12>=90,"A",C12>=80,"B",C12>=70,"C",C12>=60,"D",C12<60,"F")
- Press Enter to see the result.
This is the output.
Read More: How to Make a Grade Calculator in Excel (2 Suitable Ways)
Similar Readings
- How to Calculate Final Grade in Excel (with Easy Steps)
- Calculate College GPA in Excel (3 Handy Approaches)
- How to Calculate Subject Wise Pass or Fail with Formula in Excel
- Make Automatic Marksheet in Excel (with Easy Steps)
- How to Calculate Grades with Weighted Percentages in Excel
Method 3 – Use the VLOOKUP Function to Get the Average Letter Grades
The VLOOKUP function looks for a value in the leftmost column of a table and returns a value in the same row from a specified column. The table must be in ascending order.
Steps:
- Add a new column to the dataset. This column contains the lowest score of each level.
- Enter the VLOOKUP formula in C13.
=VLOOKUP(C12,$C$6:$D$10,2,TRUE)
- Press Enter.
Method 4 – Combine the MATCH & INDEX Functions
The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.
The INDEX function returns a value or reference of the cell at the intersection of the particular row and column in a given range.
Steps:
- Enter the formula in C13.
=INDEX(D6:D10,MATCH(C12,C6:C10,1))
- Press Enter to see the result.
Formula Breakdown
- MATCH(C12,C6:C10,1)
finds a match for C12 in C6:C10. If it finds a value less than C12 in that range, it shows the row number.
Result: 3
- INDEX(D6:D10,MATCH(C12,C6:C10,1))
shows the value of the corresponding row in D6:D10.
Result: C
Read More: How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)
Related Articles
- How to Calculate Percentage of Marks in Excel (5 Simple Ways)
- Excel Formula for Pass or Fail with Color (5 Suitable Examples)
- How to Apply Percentage Formula in Excel for Marksheet (7 Applications)
- How to Calculate Grade Percentage in Excel (3 Easy Ways)
