Calculate Semester Final Grade with VLookup, IF Function, and Conditional Formatting

September 27, 2017 by Ray Sanchez

A VLookup in excel is a function that “vertically looks up” a provided value and provides a corresponding lookup value to the right of it. VLookups are great for looking up additional information about a product based on a SKU number, UPC, or a unique ID number. They can also be used to lookup a grade letter based on the total points earned in a class.

In my spreadsheet below, I have created a sample grade sheet that assigns weights to each individual assignment. Download The Spreadsheet!

The midterm, final, and the group project are each worth 25% of the total grade. The remaining 25% of the total grade is divided among 10 quizzes throughout the semester. The points earned from each assignment is calculated by the following formula.

=(B2/100)*C5

The score earned from the assignment is divided by 100 to reach a decimal format and then it is multiplied by the weight of the grade. This will give you the points earned for each assignment which is located in column D. The sum of all the points earned is located in cell D15.

=SUM(D2:D14)

Conditional Formatting

Based on the value of cell D15 (total points earned), the background color will turn green if the total points earned is greater or equal (>=) to 70, or it will turn red if the total points earned is less (<) 70. This is possible with conditional formatting in the “styles” section of the “Home” tab.

NOTE: I am using Microsoft Excel 2016 to create this spreadsheet. The steps below may be different if you are using a different version.

First, you highlight the cell(s) that you want to change based on a certain condition and then click “Conditional Formatting”. Next, select “Highlight Cell Rules” and then “Less Than”. A popup box will show up. Under “Format cells that are less than” enter 70 and click the “with” drop down and choose “Custom Format”. Choose the fill tab and select the red color as the background. This will turn the cell red if the total points earned is below 70 which is a failing grade.

The same steps apply to turn the cell green when you have a passing grade, except this time, you will want to select greater than or equal to 70. Also, choose green for the background fill color.

Feel free to change some of the scores in the gray background so you can see the effect on the total points earned. If it is less than 70, the conditional formatting will turn the cell red.

VLookup Grade Based On Score

=VLOOKUP(D15,F2:G10,2,TRUE)

Values in Vlookup:

1. The first is the value we want to look up (lookup value)

2. Second is the range where the lookup value is.
Note: Lookup value should always be in the first column of the range

3. Third is the column of the range where the return value is located. In this case, we want the “Grade” which is column 2.

4. Lastly, TRUE for an approximate match and FALSE is for an exact match.
Note: TRUE is the default value

What Is Happening???

The VLookup Function is typed into cell F13 and automatically changes the grade based on the value of cell D15 (total points earned). Next, it uses the range F2 through G10 as the “grade key”. When it finds the matches the lookup value to the score, it returns the corresponding grade that’s in the 2nd column of the range F2:G10. Hopefully, it’s an A!

Try changing some of the scores in the gray background so you can see the VLookup do it’s job!

If Function Grade Based On Score

=IF(I13>=95,”A”,IF(I13>=90,”A-“,IF(I13>=87,”B+”,

IF(I13>=83,”B”,IF(I13>=80,”B-“,IF(I13>=77,”C+”,

IF(I13>=70,”C”,IF(I13>=60,”D”,”F”))))))))

This method of calculating your semester grade looks like more work, but it is actually easier as long as you are careful with your parenthesis. Just like math, if you open one you need to close it too. The “IF” function checks if the value is greater than or equal to 95. If that condition is true, it will display an “A”. If that condition is false, it will move to the next condition and so on. If none of the conditions are true, it will move to the default value. Sadly, the default value in this case is an “F”.

Again, try changing scores in the gray background so you can see the “IF” function in action!

Woah! Can I Have An Easier Example?!?

Yes! Your eyes may cross at first sight of the “IF” function above. I setup another “IF” function to print a motivational message base on the final grade.

=IF(D15>=70,”Awesome! You PASSED the class!”,”You FAILED! Better luck next year!”)

If the total points earned for the semester is greater than or equal to 70, then Excel will print “Awesome! You PASSED the class!”. If this condition is not met, total points earned are less than 70, and Excel will print “You FAILED! Better luck next year!”.

Awesome! You have learned to calculate your final semester grade with Vlookups, if functions, and conditional formatting.