How to Fix Excel IF Not Evaluating Chained Comparisons in WPS Spreadsheets
Question details
User needs to understand why Excel does not evaluate chained comparisons like =IF((1<2<3),"ok","x") and how to rewrite the formula with the AND function.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- User is writing an IF formula that tests a value against two bounds using one chained comparison such as 1<2<3 and expects the IF condition to return TRUE only when both comparisons are TRUE.
- Observed behavior
- Excel evaluates the first comparison first and then compares the Boolean result with the next number, so the formula does not work as a mathematical chained inequality.
Open the WPS Spreadsheets workbook containing the formula and identify the cell reference or fixed values that should be used in the logical test.
Use the AND Function to Test Both Conditions Separately
Use this when you need IF to return a value only if all conditions in a chained comparison are true.
WPS Spreadsheets and Excel do not evaluate 1<2<3 as a single inequality. The formula =IF((1<2<3),"ok","x") first evaluates 1<2 as TRUE, then compares TRUE with 3. The AND function avoids this by evaluating each comparison independently.
Click the cell where the IF result should appear, such as B1.
Type =IF(AND(1<2,2<3),"ok","x") for fixed numbers, or use a cell reference like =IF(AND(A1>1,A1<3),"ok","x") to test a value in A1.
Press Enter. The formula now returns "ok" only when both conditions are TRUE.
Use Nested IF as an Explicit Alternative
Use this method when you want to see each comparison as a separate branch, though it can become harder to read with many conditions.
Fix Chained Comparisons in Excel Files with WPS Spreadsheets
Use the AND function inside IF in WPS Spreadsheets to evaluate each condition separately. WPS Spreadsheets opens, edits, and saves Excel .xlsx workbooks, so you can correct the formula without changing your workflow.
- 1. Open the workbook: Launch WPS Office, open the WPS Spreadsheets app, and load the Excel workbook that contains the chained comparison formula.
- 2. Select the formula cell: Click the cell containing the IF formula, or select an empty result cell such as B1.
- 3. Replace the formula with AND: Type =IF(AND(A1>1,A1<3),"ok","x") to test a cell reference, or =IF(AND(1<2,2<3),"ok","x") for fixed values.
- 4. Apply and save: Press Enter to calculate the corrected result, then save the file as .xlsx to keep full Excel compatibility.
Frequently Asked Questions
Why does =IF((1<2<3),"ok","x") not return "ok" in Excel?
Excel and WPS Spreadsheets do not treat 1<2<3 as a three-part mathematical inequality. The formula first evaluates 1<2 as TRUE, then compares TRUE with 3. Use =IF(AND(1<2,2<3),"ok","x") instead.
How do I write an IF formula for a value between two numbers?
Use AND to test both bounds separately: =IF(AND(A1>1,A1<3),"ok","x"). Replace A1 with the cell that contains the value you want to test.
Can I use AND with more than two conditions in an IF formula?
Yes. Add each condition as a separate AND argument, for example =IF(AND(A1>1,A1<3,A1<>2),"ok","x").
Does WPS Spreadsheets support the same IF and AND functions as Excel?
Yes. WPS Spreadsheets supports Excel-compatible formulas and can open and save .xlsx workbooks, so you can apply the same AND/IF correction.
