How to Use Excel Functions to Check for Errors and Display Specific Results

Excel allows you to create customized logic that can display specific results based on different conditions. In this case, you want to evaluate the values in cells A1, B1, and C1 and return a specific value based on combinations of errors and text. For example, if cell A1 is blank, and B1 and C1 contain errors (#N/A), you want to display ‘確認’. If C1 contains any text and B1 still contains an error, you want to show ‘OK’. For all other combinations, you need to return ‘NG’. This guide will walk you through using Excel functions like IF, ISNA, and IFNA to create this logic.

Understanding the Excel Functions
Creating the Formula
What to Do If the Formula Doesn’t Work
Conclusion

Understanding the Excel Functions

Before we dive into the solution, it’s important to understand the functions you’ll use:

IF: A conditional function that returns one value if a condition is true and another value if it is false.
ISNA: A function that checks if a cell contains the #N/A error.
IFNA: A combination of IF and ISNA, used specifically for handling #N/A errors.

Creating the Formula

Now let’s create a formula based on the conditions you’ve described. Here’s the logic we need to implement:

If A1 is blank, and B1 and C1 contain the #N/A error, the result should be ‘確認’.
If A1 is blank, and B1 contains #N/A but C1 contains any text, the result should be ‘OK’.
If A1 contains text, and B1 and C1 both contain any text, the result should be ‘NG’.

Here’s the formula that will do this:

=IF(AND(ISBLANK(A1), ISNA(B1), ISNA(C1)), "確認", IF(AND(ISBLANK(A1), ISNA(B1), NOT(ISNA(C1))), "OK", IF(AND(NOT(ISBLANK(A1)), NOT(ISNA(B1)), NOT(ISNA(C1))), "NG", "")))

Let’s break this down:

AND(ISBLANK(A1), ISNA(B1), ISNA(C1)): This checks if A1 is blank and both B1 and C1 contain #N/A errors. If true, it returns ‘確認’.
AND(ISBLANK(A1), ISNA(B1), NOT(ISNA(C1))): This checks if A1 is blank, B1 contains #N/A, and C1 contains any text. If true, it returns ‘OK’.
AND(NOT(ISBLANK(A1)), NOT(ISNA(B1)), NOT(ISNA(C1))): This checks if A1 contains text, and both B1 and C1 contain any text, and if true, returns ‘NG’.

What to Do If the Formula Doesn’t Work

If the formula doesn’t work as expected, there are a few things to check:

Ensure that the #N/A error is correctly present in cells B1 and C1. If the error is not showing, the formula will not work as intended.
Check that there are no extra spaces or characters in the values being evaluated, especially in cell A1. Excel treats spaces as characters.
Ensure that your formula is referencing the correct cells, as Excel can sometimes misinterpret cell references in more complex formulas.

Conclusion

By using IF, ISNA, and IFNA functions, you can easily create conditional logic in Excel that checks for errors and returns specific results based on various conditions. The formula provided here should solve the problem you described, but don’t hesitate to modify it as per your requirements. If you’re still facing issues, double-check your cell references and ensure that all error conditions are being met.