Fix Nested IF, AND, and OR Formulas for Multiple Conditions in WPS Spreadsheets
Question details
The user needs to create a formula that evaluates every combination of A2 and A3 thresholds in WPS Spreadsheets so boundary values do not return #N/A or unexpected results.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating a value based on multiple conditions, including the A2 100/101 boundary and A3 thresholds around 137 and 150.
- Observed behavior
- Nested IF or IFS formulas may return #N/A or wrong values when combinations such as A3 between 137 and 150 are not explicitly defined.
Check that A2 and A3 contain numeric values and decide the exact result for each threshold: A2 below 101 versus above 100, and A3 below 150, equal to 150, or above 150. Then select an empty cell where the calculated result should appear.
Use IFS with Explicit AND Branches and a TRUE Fallback
Best when you want a readable formula that lists each possible A2/A3 combination and prevents unmatched combinations from becoming #N/A.
IFS evaluates each AND test in order and returns the value for the first TRUE test. If no test matches and no final TRUE condition is supplied, WPS Spreadsheets and Excel return #N/A.
Click the empty cell in WPS Spreadsheets where you want the calculated result.
Type =IFS(AND(A2<101,A3<150),7500,AND(A2<101,A3=150),9500,AND(A2<101,A3>150),14000,AND(A2>100,A3<150),7500/100*A2,AND(A2>100,A3=150),9500/100*A2,AND(A2>100,A3>150),14000/100*A2,TRUE,"Review conditions") and press Enter.
Test key values such as A2=100, A2=101, A3=137, A3=149, and A3=150 to confirm each branch returns the expected result.
Use a Compact Nested IF Formula for the Same Result
Use this when you prefer a shorter formula and want A3 values between 137 and 149 to automatically fall into the base result.
Build Multi-Condition Nested IF and IFS Formulas in WPS Spreadsheets Without #N/A Errors
WPS Spreadsheets lets you write nested IF, IFS, AND, and OR formulas that explicitly handle multiple A2 and A3 combinations and prevent #N/A results.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook containing A2 and A3.
- 2. Click the result cell: Select the empty cell where you want the calculated result.
- 3. Enter the formula: Type the IFS or nested IF formula, for example =IFS(AND(A2<101,A3<150),7500,AND(A2<101,A3=150),9500,AND(A2<101,A3>150),14000,AND(A2>100,A3<150),7500/100*A2,AND(A2>100,A3=150),9500/100*A2,AND(A2>100,A3>150),14000/100*A2,TRUE,"Review conditions").
- 4. Press Enter: Press Enter to apply the formula and verify the result for known A2/A3 combinations.
- 5. Adjust thresholds if needed: If your business rule uses 137 rather than 150 as the base boundary, change A3<150 to A3<137 or add an explicit A3>=137,A3<150 branch.
Frequently Asked Questions
Why does my IFS formula return #N/A when A3 is 140?
Because no earlier branch matches 140 if you only use A3<137, A3=150, and A3>150. Add an explicit branch such as AND(A2<101,A3>=137,A3<150) for the base value, or end the IFS formula with TRUE,"Review conditions" so unmatched values have a defined result.
How do I make the formula switch at 100/101 without overlapping branches?
Use A2<101 for the fixed-result branch and A2>100 for the percentage branch. This avoids overlap at the boundary and ensures values 100 and 101 follow the correct intended rule.
Can I use OR inside the nested IF formula?
Yes. If multiple conditions should return the same result, wrap them in OR, for example OR(A3<137,A3>=137,A3<150) can represent the base A3 range. Keep branches mutually exclusive so WPS Spreadsheets does not match two results at once.
Does WPS Spreadsheets support IFS and nested AND/OR functions?
Yes, WPS Spreadsheets supports IFS, IF, AND, OR, and nested combinations. Workbooks saved as .xlsx retain formula compatibility with Microsoft Excel.
What should I do if the formula returns the "Review conditions" fallback?
Check the values in A2 and A3 against your thresholds. If the fallback appears for a valid combination, add that missing branch before the TRUE fallback or correct the operator, such as changing <137 to <150 if needed.
