Formula Errors

Fix Excel IFS Formula Returns #N/A for Blank or Numeric Cells

John Wilson Oct 22, 2026 868 views

Question details

The user needs to correct an IFS formula that returns an #N/A error when evaluating logical TRUE values alongside blank or numeric cells.

How to Fix Excel IFS Formula Returns #N/A for Blank or Numeric Cells

Product: Excel
Device & OS: not provided
Scenario: Writing conditional logic to output specific values based on whether a target cell is blank or contains a number, given another cell is TRUE.
Observed behavior: The IFS formula fails and returns an #N/A error instead of the expected result, indicating missing fallback conditions or incorrect logical text testing.

Before you start

Double-check your data to ensure that logical values are stored as actual booleans (TRUE/FALSE) rather than text strings like "TRUE" to avoid syntax evaluation failures.

Solution 1Recommended

Use a Nested IF with the IFS Function

This method isolates the primary TRUE condition in a standard IF function and uses IFS to handle the subsequent blank or numeric checks.

The #N/A error in an IFS function occurs when none of the specified conditions are met. By adding a final fallback condition (TRUE) and separating the primary logic, you ensure the formula always returns a valid result.

Select the target cell

Click on the cell where you want the formula result to appear.

Enter the nested formula

Type the formula: =IF(AE2=TRUE, IFS(AD2="", 0, ISNUMBER(AD2), 1, TRUE, ""), "") into the formula bar.

Apply the calculation

Press Enter. This formula checks if AE2 is TRUE. If so, it returns 0 for a blank AD2, 1 for a numeric AD2, and blank for any other value.

Fallback Condition: Using TRUE as the final logical test in an IFS function acts as a 'catch-all' or default fallback value, effectively preventing the #N/A error.

Solution 2

Combine IFS with the AND Function

Consolidate the logic into a single IFS function by using AND to evaluate multiple criteria simultaneously.

Easily Handle Complex Formulas with WPS Spreadsheets

WPS Spreadsheets provides full support for advanced logical functions like IF, IFS, AND, and ISNUMBER. With its intuitive interface and built-in formula error-checking tools, you can easily troubleshoot and fix #N/A errors.

1. Open your workbook: Launch WPS Spreadsheets and open the file containing your data.
2. Select the error cell: Click on the cell returning the #N/A error.
3. Input the corrected formula: Use the formula bar to input =IF(AE2=TRUE,IFS(AD2="",0,ISNUMBER(AD2),1,TRUE,""),"") and press Enter.
4. Apply to multiple rows: Drag the fill handle at the bottom-right corner of the cell to apply the corrected formula down the column.

100% compatible with Microsoft Excel formulas and functionsBuilt-in formula auditing and error-checking toolsColor-coded syntax highlighting for easy troubleshootingLightweight application with fast calculation speeds

microsoft office alternative - wps office

Frequently Asked Questions

Why does my IFS formula return an #N/A error?

The #N/A error in an IFS function means that none of the provided logical tests evaluated to TRUE. To fix this, you must include a default fallback condition by adding TRUE as the final logical test and specifying a corresponding return value.

How do I correctly test for a blank cell in an Excel formula?

You can test for a blank cell by using double quotes "" in your logical test (e.g., AD2="") or by wrapping the cell reference in the ISBLANK() function.

Does ISNUMBER count blank cells as numbers?

No, the ISNUMBER function evaluates blank cells as FALSE. It only returns TRUE when the referenced cell contains an actual numeric value, a date, or a time.

What is the difference between TRUE and "TRUE" in formulas?

TRUE without quotes is a logical boolean value recognized natively by spreadsheet software. "TRUE" enclosed in quotes is treated strictly as a text string. Logical formulas may fail or return errors if they attempt to evaluate a boolean condition but encounter text.

Excel & Data Problems