'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create an Excel Formula for Approval Status Based on Multiple Responses
Question details
The user needs to create an Excel formula that returns a specific approval status (Approved, Not approved, Review needed) by evaluating a combination of two responses (Teacher and Parent).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Evaluating survey or questionnaire responses where different combinations of 'Yes', 'No', 'Maybe', or blank cells dictate the final outcome string.
- Observed behavior
- Requires a logical formula that checks multiple variables in separate columns and returns the correct status string according to a predefined set of rules.
Ensure your data is organized consistently, with Teacher responses in one specific column (e.g., Column A) and Parent responses in another (e.g., Column B), and check for accidental trailing spaces in the cells.
Use a Nested IF Function with OR Logic
Combine the IF and OR functions to check multiple conditions sequentially and output the correct status.
A nested IF statement allows you to evaluate multiple conditions in a specific order. By incorporating the OR function, you can handle multiple scenarios (like 'No' or a blank cell) in a single logical test. The formula evaluates the Teacher's response first, and if it passes, evaluates the Parent's response.
Click on the cell where you want the final Approval Status to appear (for example, cell C2).
Type the following formula exactly as shown: =IF(OR(A2="No",A2=""),"Not approved",IF(B2="Yes","Approved",IF(B2="No","Not approved","Review needed"))). This assumes the teacher response is in A2 and the parent response is in B2.
Press Enter to execute the formula. Then, click the small square at the bottom-right corner of cell C2 and drag it down to fill the formula for the rest of your data list.
Easily Build Complex Logical Formulas with WPS Spreadsheet
WPS Office provides a highly compatible and user-friendly spreadsheet application that perfectly handles complex nested IF and OR formulas, making data evaluation and tracking effortless.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your survey or approval data.
- 2. Insert the logic formula: Select the status column, go to the Formula bar, and type your nested IF logic to evaluate the responses.
- 3. Use AutoFill to complete the list: Press Enter and double-click the fill handle to automatically apply the formula down your entire dataset.
Frequently Asked Questions
Why is my IF formula returning an incorrect result even though the text looks correct?
This is usually caused by hidden spaces in your data (e.g., 'Yes ' instead of 'Yes'). Excel treats these as completely different text strings. You can use the TRIM function or Find and Replace to clean up extra spaces in your response columns.
Can I use the IFS function instead of a nested IF statement?
Yes. If your spreadsheet software supports the IFS function, you can simplify the formula to: =IFS(OR(A2="No",A2=""), "Not approved", B2="Yes", "Approved", B2="No", "Not approved", TRUE, "Review needed").
How do I make the formula case-insensitive so 'yes' and 'YES' both work?
The standard IF function in Excel and WPS Spreadsheet is naturally case-insensitive. It will treat 'yes', 'YES', and 'Yes' identically without requiring any modifications to the formula.
