Function Problems

How to Convert Text Grades to Numbers and Calculate Averages in Excel

Khadija Khan Sep 30, 2026 872 views

Question details

The user needs to convert text-based grading categories into numerical scores, calculate their average, and translate the final mean score back into a text grade.

Convert Text Grades to Numbers and Calculate Averages in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a grading calculator that accurately processes and averages qualitative text grades.
Observed behavior: The user aims to automate the conversion of text categories to numerical values, calculate the mean, and execute a reverse conversion from numbers back to appropriate text categories.

Before you start

Ensure you have established a complete reference list mapping all possible text grades to their exact numerical equivalents before building your formulas.

Solution 1Recommended

Use VLOOKUP with a Grade Mapping Table

Create dedicated lookup tables to map text grades to numbers, allowing you to easily calculate the average and reverse-lookup the final text grade.

This method involves setting up a conversion table in your worksheet. It is highly flexible because you can easily adjust the grading scale later without having to rewrite complex formulas.

Create a grade-to-number table

In an empty area of your spreadsheet (e.g., cells G1:H5), set up a two-column table. Enter your text grades (like 'Exceptional First', 'First') in the left column and their corresponding numerical values in the right column.

Convert text to numbers

Next to your students' text grades, enter a VLOOKUP formula to translate the text. For example, use `=VLOOKUP(B2, $G$1:$H$5, 2, FALSE)` and drag the fill handle down to apply it to all students.

Calculate the average

Select a cell at the bottom of your newly created numerical column and use the AVERAGE function, such as `=AVERAGE(C2:C20)`, to calculate the mean score.

Convert the average back to text

Create a second lookup table (number to text) sorted in ascending numerical order. Use a VLOOKUP formula with an approximate match on your calculated average, like `=VLOOKUP(C21, $J$1:$K$5, 2, TRUE)`, to output the final text grade.

Sorting the Reverse Lookup Table: When using TRUE for an approximate match in your reverse lookup (number to text), ensure the numerical column in your reference table is sorted in ascending order.

Solution 2

Use the IFS Function for Direct Conversion

If you are using a modern spreadsheet version, the IFS function allows you to convert text to numbers directly within the formula without creating a separate lookup table.

Easily Calculate and Convert Grades with WPS Spreadsheet

WPS Spreadsheet provides powerful built-in functions like VLOOKUP, XLOOKUP, and IFS, making it perfectly suited for building complex grading calculators quickly and efficiently.

1. Input Data: Open WPS Spreadsheet and input your students' text grades into a column.
2. Create Reference Table: Set up a small reference table mapping each text category to its numerical value.
3. Apply Formulas: Use the built-in VLOOKUP function from the Formulas tab to automatically convert the text grades to numbers.
4. Calculate and Re-convert: Use the AVERAGE function to find the mean, then use VLOOKUP with an approximate match to turn the average back into a text grade.

Fully compatible with Microsoft Excel formulas, formatting, and lookup functions.Includes advanced data analysis tools to handle text-to-number conversions.Lightweight and completely free to download.Familiar, easy-to-use interface ensuring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use XLOOKUP instead of VLOOKUP for grade conversion?

Yes, XLOOKUP is an excellent alternative that allows you to search in any direction and specify what to do if a grade is not found. It defaults to an exact match, making your grading calculator more robust.

Why is my VLOOKUP formula returning an #N/A error for some grades?

This error usually occurs if the text grade in your data has trailing spaces, or if it doesn't exactly match the text in your lookup table. Wrapping your cell reference in the TRIM function (e.g., TRIM(B2)) can help clean the text data.

How do I convert an exact decimal average to a broad text grade category?

Set up your number-to-text lookup table in ascending numerical order, defining the minimum threshold for each grade. Then, use VLOOKUP with the match_type argument set to TRUE. This ensures Excel finds the closest approximate match below the average value.

Is there a way to calculate the average without using helper columns?

Yes, you can use an array formula combining AVERAGE and VLOOKUP or XLOOKUP to calculate the mean directly from the text grades in a single cell. However, using helper columns often makes the data easier to audit and troubleshoot.

Excel & Data Problems