Function Problems

Excel Formula to Return High, Medium, or Low Scores

WPS Content Manager Sep 28, 2026 869 views

Question details

The user needs an Excel formula to automatically categorize numeric values into 'High', 'Medium', or 'Low' based on specific ranges, while returning a blank for values outside those predefined thresholds.

How to Create an Excel Formula to Return High, Medium, or Low Scores

Product: Excel
Device & OS: not provided
Scenario: Categorizing assessment scores, survey results, or grading numerical data into readable text labels.
Observed behavior: The goal is to display specific text labels corresponding to defined numeric ranges and leave cells blank if the value is too high or too low.

Before you start

Ensure your numeric data contains no text formatting or hidden spaces. Decide the exact thresholds for High, Medium, and Low categories before building the formula.

Solution 1Recommended

Use a Nested IF Formula to Categorize Scores

The IF function evaluates conditions sequentially, making it ideal for assigning text labels based on specific numeric thresholds.

A nested IF formula checks multiple conditions one by one. Once a condition is met, Excel stops checking and returns the corresponding value.

Select the target cell

Click on the cell where you want the categorized text (High, Medium, or Low) to appear.

Enter the nested IF formula

Type the formula =IF(A2>20,"",IF(A2>=16,"High",IF(A2>=6,"Medium",IF(A2>=3,"Low","")))) into the formula bar, assuming your source data is in cell A2.

Apply to other cells

Press Enter to apply the formula. Click the cell again, grab the fill handle at the bottom-right corner, and drag it down to apply the categorization to the rest of your data.

Use a Nested IF Formula to Categorize Scores

Condition Logic: Nested IF formulas must evaluate thresholds in a logical order (e.g., highest to lowest or lowest to highest) to work correctly.

Solution 2

Use the LOOKUP Function as a Cleaner Alternative

The LOOKUP function provides a cleaner and more compact way to categorize scores without needing multiple nested IF statements.

Categorize Data Faster

Use WPS Office to Handle Complex Formulas

WPS Spreadsheet fully supports IF and LOOKUP functions to categorize your data seamlessly. It offers an intuitive interface, rich formula hints, and fast data processing.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the file containing the scores you want to categorize.
2. Select the result cell: Click on the cell next to your first score where you want the 'High', 'Medium', or 'Low' text to appear.
3. Insert the formula: Go to the Formula tab or directly type your IF or LOOKUP function in the formula bar.
4. Use smart hints and apply: Utilize the smart formula assistant to ensure your commas and quotes are correct, then press Enter and drag down to fill.

Fully compatible with Microsoft Excel formulas like IF and LOOKUPLightweight application with incredibly fast startupRich built-in function library with auto-complete tipsFree to download and use for your daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my IF formula return an error?

Ensure you have matched the correct number of closing parentheses at the end of your nested IF formula and that all text values like 'High' are enclosed in double quotes.

Can I use the IFS function instead of nested IFs?

Yes, if you have a newer version of Excel or WPS Spreadsheet, you can use the IFS function, which evaluates multiple conditions without complex nesting. For example: =IFS(A2>20,"", A2>=16,"High", A2>=6,"Medium", A2>=3,"Low", TRUE,"").

Why is the LOOKUP function giving me incorrect results?

The LOOKUP function requires the lookup vector (the first array of numbers inside the curly brackets) to be sorted in strictly ascending order. If your thresholds aren't sorted from smallest to largest, the formula will return incorrect matches.

Excel & Data Problems