How to Use an Excel Formula for Tiered Values Based on Number Ranges in WPS Spreadsheets
Question details
User needs an Excel-compatible formula that returns 2 for values below 12, 1.5 for values from 12 up to 18, and 1.25 for values 18 and above.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is calculating tiered results based on numeric ranges in a worksheet.
- Observed behavior
- The formula must return different results depending on whether the source value falls below 12, between 12 and 18, or is at least 18.
Open your workbook in WPS Spreadsheets and identify the source cell that contains the numeric value, for example A1. Make sure the cell contains a number or a value that the spreadsheet can evaluate numerically.
Use the IFS Formula for Tiered Values
Use this modern, readable formula when your version of WPS Spreadsheets supports IFS and you want all tier conditions in one place.
IFS checks each condition in order and returns the first matching result. The final TRUE condition acts as the default result for values that do not meet the earlier conditions.
Click the cell where you want the tiered result to appear, such as B1.
Type =IFS(A1<12,2,A1<18,1.5,TRUE,1.25) into the formula bar and press Enter.
Change the value in A1 to test values below 12, between 12 and 18, and 18 or above to confirm the result is 2, 1.5, or 1.25.
Use a Nested IF Formula for Broader Compatibility
Use this classic formula when you need wider compatibility with older spreadsheet versions or prefer familiar IF logic.
Use LOOKUP with Array Constants for Tiered Ranges
Use this compact formula when your tier thresholds are fixed, sorted ascending, and you want a shorter lookup formula.
Calculate Tiered Values by Number Ranges in WPS Spreadsheets
You can complete this task directly in WPS Spreadsheets using Excel-compatible formulas. Enter =IFS(A1<12,2,A1<18,1.5,TRUE,1.25) in the result cell, or use the nested IF or LOOKUP alternatives as needed.
- 1. Open WPS Spreadsheets: Launch WPS Office and open a blank workbook or the file containing your numeric data.
- 2. Enter the source value: Type the value to be evaluated in cell A1, or use the cell that already contains your number.
- 3. Insert the tiered formula: Click the result cell and enter =IFS(A1<12,2,A1<18,1.5,TRUE,1.25) in the formula bar.
- 4. Press Enter and verify: Press Enter to calculate the result, then change A1 to verify that the formula returns the correct tier for each number range.
Frequently Asked Questions
Which formula returns 2 below 12, 1.5 from 12 to under 18, and 1.25 at 18 or above?
Use =IFS(A1<12,2,A1<18,1.5,TRUE,1.25). Replace A1 with your source cell and enter the formula in the result cell.
Does WPS Spreadsheets support LOOKUP with array constants?
Yes. You can enter =LOOKUP(A1,{0,12,18},{2,1.5,1.25}) in the formula bar. The lookup vector must be sorted in ascending order.
Why does the nested IF formula return 1.25 when the value is exactly 18?
In =IF(A1<12,2,IF(A1<18,1.5,1.25)), the condition A1<18 is false for 18, so the formula proceeds to the final default result of 1.25.
Can I use XLOOKUP for the same tiered value calculation?
Yes, in versions that support XLOOKUP, you can use =XLOOKUP(A1,{0,12,18},{2,1.5,1.25},,-1) to match the largest value less than or equal to A1.
