Function Problems

How to Use Excel IFS Function with Multiple Numeric Ranges

Anum Mushtaq Oct 20, 2026 868 views

Question details

The user needs a formula to assign specific numeric scores based on varying value brackets using the IFS function.

How to Use Excel IFS Function with Multiple Numeric Ranges

Product: Excel
Device & OS: not provided
Scenario: Categorizing numeric data into specific scoring brackets (e.g., 0, 1, 2, 3) based on defined value ranges like <=15, 16-30, 31-60, and >60.
Observed behavior: The goal is to map target values to numerical scores using a logical formula, ensuring the outputs are recognized as numbers rather than text strings.

Before you start

Ensure your spreadsheet software supports the IFS function (available in Excel 2019, Microsoft 365, and modern versions of WPS Spreadsheet), and identify the target cell containing the value you wish to evaluate.

Solution 1Recommended

Apply the IFS Function for Numeric Ranges

Construct an IFS formula that evaluates conditions sequentially from left to right, outputting the correct score for the first matching range.

The IFS function checks multiple conditions in the exact order they are written. It returns a value corresponding to the first condition that evaluates to TRUE.

By deliberately omitting quotation marks around the return values, the output remains a true number, which ensures compatibility with any downstream mathematical calculations.

Select Target Cell

Click on the cell where you want the resulting numeric score to be displayed.

Input the IFS Formula

Type the formula =IFS(B13<=15,0, B13<=30,1, B13<=60,2, TRUE,3), replacing 'B13' with your actual target cell reference.

Execute Calculation

Press Enter to apply the formula and view the numeric score.

Apply to Multiple Cells

Click and drag the fill handle at the bottom-right corner of the cell to apply this formula to other rows in your dataset.

Apply the IFS Function for Numeric Ranges

Formatting Outputs: Avoid using quotation marks around the result values (e.g., "1") unless you specifically want the formula to output text strings instead of numbers. Text and numerical results behave differently in spreadsheet software.

Advanced Formula Support

Easily Execute IFS Functions with WPS Spreadsheet

WPS Office fully supports advanced Excel functions, including IFS, allowing you to seamlessly evaluate multiple numeric ranges. Enjoy a highly compatible, free spreadsheet tool designed for efficiency.

1. Open your Worksheet: Launch WPS Spreadsheet and open the document containing your data.
2. Start the Formula: Select the designated output cell and begin typing =IFS( to prompt the formula assistant.
3. Define Logical Tests: Input your conditional tests and corresponding return values separated by commas (e.g., =IFS(A1<=15,0, A1<=30,1, TRUE,2)).
4. Press Enter: Hit Enter to execute the formula and instantly categorize your numeric ranges.

100% compatible with Microsoft Excel formulas (.xlsx)Built-in intelligent formula suggestions and auto-completeFree to use with a lightweight, fast installationCross-platform support for Windows, Mac, and mobile devices

microsoft office alternative - wps office

Frequently Asked Questions

Why is my IFS formula returning an #N/A error?

An #N/A error occurs if none of the specified logical tests evaluate to TRUE. To prevent this, include TRUE as the final logical test in your formula, followed by a default fallback value (e.g., TRUE, 3).

What is the difference between IFS and nested IF functions?

The IFS function evaluates multiple conditions in a single, straightforward string without needing to nest multiple IF statements inside one another. This makes the formula much easier to read, write, and troubleshoot.

Can I use text strings as the result in an IFS function?

Yes. If you need the result to output specific words (like 'Low', 'Medium', 'High'), enclose the return values in double quotation marks (e.g., =IFS(A1<=15,"Low")). However, if you need numeric results for further calculations, omit the quotation marks.

Excel & Data Problems