WPS Spreadsheets: Grade Exam Scores with IF or XLOOKUP
Question details
Assign letter grades to exam scores based on defined score thresholds using XLOOKUP or nested IF formulas.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The score is stored in cell C1, and the user needs to return grades A through E for scores from 0 to 14 or higher.
- Observed behavior
- A formula is needed to match each score to the appropriate grade threshold, such as A for 0–4, B for 5–7, C for 8–10, D for 11–13, and E for 14 or higher.
Create a two-column lookup table with minimum scores in ascending order and their corresponding grades. For example, enter 0, 5, 8, 11, and 14 in A1:A5, and A, B, C, D, and E in B1:B5.
Use XLOOKUP with an approximate threshold match
Use this method when you want a compact, maintainable formula that can be updated by editing the threshold table.
The minimum-score thresholds must be sorted from smallest to largest. Use match mode -1 so XLOOKUP returns the grade for the largest threshold that is less than or equal to the score.
Enter the exam score in cell C1.
In A1:A5, enter 0, 5, 8, 11, and 14. In B1:B5, enter A, B, C, D, and E.
Select the result cell and enter =XLOOKUP(C1,$A$1:$A$5,$B$1:$B$5,"NA",-1), then press Enter.
If scores are listed in a column, copy or drag the formula down and adjust the score reference for each row.
Assign grades with a nested IF formula
Use this option when you prefer a single self-contained formula and do not need a separate lookup table.
Build threshold-based grading formulas in WPS Spreadsheets
WPS Spreadsheets lets you create a sorted score-threshold table and use XLOOKUP or nested IF formulas to assign grades quickly. Its familiar spreadsheet interface supports editing and saving Excel-compatible workbooks.
- 1. Open WPS Spreadsheets: Launch WPS Office and choose Spreadsheets to open a blank workbook or an existing Excel-compatible file.
- 2. Set up the grading table: Enter the minimum scores and grades in two adjacent columns, sorted by minimum score from low to high.
- 3. Enter the score formula: Place a score in C1 and use =XLOOKUP(C1,$A$1:$A$5,$B$1:$B$5,"NA",-1) in the grade cell.
- 4. Apply the formula to other scores: Copy the formula down the grade column to evaluate additional exam scores.
Frequently Asked Questions
What XLOOKUP match mode should be used for grade thresholds?
Use -1 in the match_mode argument: =XLOOKUP(C1,$A$1:$A$5,$B$1:$B$5,"NA",-1). This returns the value associated with the largest threshold that does not exceed the score.
How should the grading thresholds be arranged?
Place minimum scores in ascending order, such as 0, 5, 8, 11, and 14. Put the matching grades in the adjacent column on the same rows.
What formula assigns E to scores of 14 or higher?
Use =IF(C1>=14,"E",IF(C1>=11,"D",IF(C1>=8,"C",IF(C1>=5,"B","A")))). The first condition catches every score of 14 or above.
Why does my lookup return the wrong grade?
Check that the threshold values are sorted in ascending order and that the formula uses match mode -1. Also verify that the score cell and lookup ranges reference the intended cells.
Can I change the grading scale later?
Yes. With XLOOKUP, replace the minimum scores or grades in the lookup table without changing the formula, provided the threshold column remains sorted in ascending order.
