totn Excel Functions

MS Excel: How to Convert a Letter Grade to GPA points in older versions of Excel

This Excel tutorial explains how to convert a letter grade to GPA points in older versions of Excel with screenshots and instructions.

NOTE: This formula will work in all versions of Excel. However, if you are running Excel 2019 or Excel for Office 365, you can try this more elegant solution that uses the new SWITCH function.

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 2016 and older. Let's explore how to do this.

Microsoft Excel

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

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.

Microsoft Excel

In cell B2, we've created the following formula that uses a number of nested IF functions with the MID function to calculate the correct GPA points value:

=IF(MID(A2,1,1)="A",4,IF(MID(A2,1,1)="B",3,IF(MID(A2,1,1)="C",2,IF(MID(A2,1,1)="D",1,0))))

Each of the IF functions will extract the first character from cell A2 using the MID function and then return the corresponding result.

A → 4
B → 3
C → 2
D → 1

And if no other condition is met, the formula will return 0 (thus returning a GPA points value of 0 for a letter grade of F).

Since the letter grade value is "A", the formula will return a GPA points value of 4.

Share on: