How to Create an Excel IF Formula for Resolved, Pending, or Blank Status
Question details
The user needs a formula to automatically output "Resolved", "Pending", or a blank string depending on whether two target cells are both filled, partially filled, or both empty.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating task or issue tracking statuses based on two data points, such as an open date and a close date.
- Observed behavior
- The user wants to evaluate two cells simultaneously to determine the current status without having to manually type "Resolved" or "Pending".
Identify the two specific cells you want to evaluate (for example, H2 and I2). Ensure your dataset does not contain hidden space characters in seemingly empty cells, as this can cause the formula to evaluate them as containing text.
Use Nested IF with AND/OR Functions
This is the most straightforward method to evaluate the conditions logically using standard Excel operators.
This solution uses a nested IF statement. The first logical test uses the AND function to check if both cells contain data. If they don't, the second logical test uses the OR function to check if at least one cell has data.
Click on the cell where you want the "Resolved" or "Pending" status to be displayed.
Type the following formula: =IF(AND(H2<>"",I2<>""),"Resolved",IF(OR(H2<>"",I2<>""),"Pending","")). Be sure to change H2 and I2 to match your actual cell references.
Press Enter to apply the formula. Click the cell again, hover over the bottom-right corner until you see a crosshair, and drag the fill handle down to apply the formula to the remaining rows.
Use IF and LEN Functions for Formula-Generated Blanks
Use this method if your reference cells contain background formulas that output empty text strings, which standard operators might misread.
Manage Task Statuses Easily with WPS Spreadsheet
WPS Spreadsheet is a powerful data analysis tool that fully supports all standard Excel functions, including nested IF, AND, OR, and LEN. It allows you to automate status tracking seamlessly without any learning curve.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your task tracker.
- 2. Insert the status formula: Click on the target status cell and type the nested IF formula just as you would in Excel.
- 3. Drag to apply: Press Enter and drag the fill handle to apply the logical formula to your entire column instantly.
Frequently Asked Questions
Why does my formula return 'Pending' when both cells look empty?
This usually happens if the seemingly empty cells contain hidden spaces or invisible characters. You can use the TRIM function to clean the data, or simply select the empty cells and press the Delete key to clear their contents entirely.
Can I add a third cell to check for a different status?
Yes, you can expand the logic by nesting another IF function. However, if you are evaluating three or more complex conditions, utilizing the IFS function (if available in your software version) is often cleaner and easier to read than deeply nested IF statements.
How can I highlight 'Resolved' and 'Pending' in different colors?
Once your formula is working and outputting text, select the status column, go to the Home tab, and click Conditional Formatting. Choose 'Highlight Cells Rules' > 'Equal To', and assign a green background to 'Resolved' and a yellow background to 'Pending'.
