MS Excel: How to Convert a Letter Grade to GPA points in newer versions of Excel
This Excel tutorial explains how to convert a letter grade to GPA points in newer versions of Excel with screenshots and instructions.
Description
School grades can be represented as letter grades as well as GPA points. In this tutorial, we will show you how to convert your letter grades (such as A+, B-, C) into GPA points (such as 4, 3, 2) using a formula in Excel (such as Excel 2019 and Excel for Office 365). Let's explore how to do this.
If you want to follow along with this tutorial, download the example spreadsheet.
Example
In this tutorial, we will build a formula to convert a letter grade to GPA points. Here is a table that displays the Letter Grades with their GPA point equivalents (for the purposes of this tutorial):
| Letter Grade | GPA Points |
|---|---|
| A, A-, A+ | 4 |
| B, B-, B+ | 3 |
| C, C-, C+ | 2 |
| D, D-, D+ | 1 |
| F | 0 |
In our example below, we have a list of letter grades in column A in the range A2:A14. In column B, we want to calculate the corresponding GPA points for each of these letter grades.
In cell B2, we've created the following formula that uses the SWITCH function with the MID function to calculate the correct GPA points value:
=SWITCH(MID(A2,1,1),"A",4,"B",3,"C",2,"D",1,"F",0)
This formula will start by extracting the first character from cell A2 using the MID function as follows:
MID(A2,1,1)
This will remove the "+" character from the "A+" letter grade and return a value of "A".
Next, the formula will compare the value "A" to the list of expressions in the SWITCH function and return the corresponding result.
A → 4
B → 3
C → 2
D → 1
F → 0
Since the letter grade value is "A", the SWITCH function will return a GPA points value of 4.
Advertisements