How to Return 0 for Invalid Multiple-Choice Answers in Excel IF Formula
Question details
The user needs an Excel formula to grade a multiple-choice quiz, returning 1 for the correct answer and 0 for any incorrect or combined invalid answers.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Grading multiple-choice questions where respondents might input invalid combinations of letters (such as "bd") instead of a single valid option.
- Observed behavior
- The current formula returns an error or NA when encountering multiple combined answers instead of cleanly returning a 0.
Ensure your quiz response data does not contain trailing spaces, as hidden spaces can cause exact match formulas to fail. You can use the TRIM function to clean the data before evaluation.
Use a Simple IF Formula with a Default False Value
If only one exact answer is correct, you can simplify your formula to check for that specific answer and return 0 for all other inputs.
The most efficient way to handle invalid inputs, including combined answers like "bd", is to provide a default "value_if_false" in your IF statement. Instead of nesting multiple conditions or returning "NA", simply instruct Excel to output 0 for anything that does not perfectly match the correct answer.
Click on the cell where you want the quiz score (1 or 0) to appear.
Type the formula =IF(F3="a", 1, 0) into the formula bar. Replace F3 with the cell reference containing the student's answer, and "a" with the correct answer.
Press Enter, then click and drag the fill handle at the bottom right of the cell to copy the formula down to the rest of the rows.

Use a Nested IF Formula to Explicitly Catch Invalid Lengths
Use this method if you need to explicitly define the exact invalid options and catch combined answers using the LEN function.
Use ISNUMBER and SEARCH to Catch Specific Substrings
This advanced method is useful if you want to detect if an incorrect character is embedded anywhere within the cell text.
Grade Quizzes and Handle Formulas Easily with WPS Spreadsheet
You can seamlessly apply nested IF formulas, array functions, and grading logic using WPS Spreadsheet. It offers a familiar interface and robust calculation capabilities to help you evaluate complex datasets efficiently.
- 1. Open your quiz data in WPS: Launch WPS Spreadsheet and open the file containing your multiple-choice quiz responses.
- 2. Select the evaluation cell: Click on the empty cell next to the first student's answer where the score should be calculated.
- 3. Enter the IF formula: Type your formula, such as =IF(F3="a", 1, 0), into the formula bar and press Enter.
- 4. Drag to fill: Hover over the bottom right corner of the cell until the crosshair appears, then drag down to grade all responses instantly.

Frequently Asked Questions
Why does my IF formula return #N/A instead of 0?
Your IF formula may return #N/A if you used nested IF statements without providing a final "value_if_false" argument at the very end. Ensure your formula ends with a default value, like =IF(F3="a", 1, 0), to catch any unexpected inputs.
How do I handle case sensitivity in my quiz grading formula?
The standard IF formula is not case-sensitive, meaning "A" and "a" are treated equally. If you require case sensitivity, use the EXACT function: =IF(EXACT(F3, "a"), 1, 0).
Can I evaluate multiple correct answers using an IF formula?
Yes. If both "a" and "c" are acceptable correct answers, you can use the OR function inside the IF statement: =IF(OR(F3="a", F3="c"), 1, 0).
What if the quiz cell is empty?
If a cell is blank, the simple =IF(F3="a", 1, 0) formula will correctly evaluate it as false and return 0. If you prefer to return a blank instead of a 0 for missing answers, use =IF(ISBLANK(F3), "", IF(F3="a", 1, 0)).




