How to Use an Excel Nested IF Formula for Complete or Not Complete Status in WPS Spreadsheets
Question details
The user needs a nested Excel IF formula that returns Complete or Not Complete based on the values in columns B and C.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating a status column that automatically evaluates completion conditions from two source columns.
- Observed behavior
- The formula must return Complete when B2 is Yes, or when B2 is not Not Completed and C2 is Yes; otherwise it must return Not Complete.
Check that the Status column is empty and that columns B and C use consistent text values such as Yes, No, and Not Completed without extra spaces or capitalization differences.
Use an OR/AND Nested IF Formula to Return Complete or Not Complete
Use this method for the direct status logic: Complete if B2 is Yes, or if B2 is not Not Completed and C2 is Yes.
This formula wraps OR and AND inside IF. It returns Complete when either B2 equals Yes or the combination of B2 not being Not Completed and C2 being Yes is true.
Click cell D2, or the first empty cell in your Status column, where the first formula result should appear.
Type =IF(OR(B2="Yes",AND(B2<>"Not Completed",C2="Yes")),"Complete","Not Complete") and press Enter.
Drag the fill handle at the lower-right corner of D2 down through the remaining status cells to apply the same logic to each row.
Confirm that columns B and C contain exactly Yes, No, or Not Completed, with no leading or trailing spaces or capitalization differences.
Use Reverse Logic with AND and OR to Return Not Complete First
Use this alternative when you prefer the formula to define Not Complete conditions first and treat everything else as Complete.
Create a Complete or Not Complete Status Formula in WPS Spreadsheets
Use WPS Spreadsheets to build the same nested IF, OR, and AND status formula with Microsoft Excel compatibility, so you can track task completion without changing your existing workbook or workflow.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook that contains columns B and C.
- 2. Click the first Status cell: Select the cell where the first Complete or Not Complete result should appear, such as D2.
- 3. Type the nested IF formula: Enter =IF(OR(B2="Yes",AND(B2<>"Not Completed",C2="Yes")),"Complete","Not Complete") and press Enter.
- 4. Fill the formula down: Drag the fill handle from D2 down to apply the formula to the remaining rows.
- 5. Save in Excel-compatible format: Press Ctrl+S and keep the .xlsx format so the workbook remains compatible with Microsoft Excel.
Frequently Asked Questions
Why does my formula return Not Complete even when C2 is Yes?
The formula also checks B2. When B2 equals Not Completed, the AND condition fails because B2 must not be Not Completed for C2=Yes to make the result Complete.
Can I use this nested IF formula in both WPS Spreadsheets and Microsoft Excel?
Yes. WPS Spreadsheets supports the same IF, OR, and AND functions and can open and save .xlsx files, so the formula works across both applications.
How do I handle lowercase yes or extra spaces in columns B and C?
Clean the data with Find & Replace or wrap the references with TRIM, for example =IF(OR(TRIM(B2)="Yes",AND(TRIM(B2)<>"Not Completed",TRIM(C2)="Yes")),"Complete","Not Complete").
What is the difference between OR and AND inside this IF formula?
OR returns TRUE when at least one condition is true. AND returns TRUE only when all of its conditions are true. The formula uses both to decide Complete or Not Complete.
Can I add an In Progress status to this formula?
Yes. You can nest another IF, for example =IF(B2="Yes","Complete",IF(AND(B2="Not Completed",C2="No"),"Not Complete","In Progress")).
