Monday, 27 January 2025

Excel Error functions IFERROR, ISERROR, ISERR, IFNA, and ISNA. 20 examples of each

 Excel provides several error-handling functions that help manage and handle errors in formulas. Below are 20 examples for each of the most commonly used error functions: IFERROR, ISERROR, ISERR, IFNA, and ISNA.


1. IFERROR Function

The IFERROR function returns a custom value if a formula results in an error; otherwise, it returns the result of the formula.

Examples:

  1. =IFERROR(A1/B1, "Division by zero error")
    Returns "Division by zero error" if B1 is 0.

  2. =IFERROR(VLOOKUP("Apple", A1:B10, 2, FALSE), "Not found")
    Returns "Not found" if "Apple" is not in the range.

  3. =IFERROR(SQRT(-1), "Invalid input")
    Returns "Invalid input" because the square root of a negative number is an error.

  4. =IFERROR(1/0, "Infinity")
    Returns "Infinity" because division by zero is an error.

  5. =IFERROR(MATCH("Banana", A1:A10, 0), "Not in list")
    Returns "Not in list" if "Banana" is not found.

  6. =IFERROR(DATE(2023, 13, 1), "Invalid date")
    Returns "Invalid date" because month 13 is invalid.

  7. =IFERROR(INDEX(A1:A10, 11), "Out of range")
    Returns "Out of range" because the index is beyond the range.

  8. =IFERROR(LEFT("Text", -1), "Invalid length")
    Returns "Invalid length" because the length is negative.

  9. =IFERROR(FIND("X", "Hello"), "Not found")
    Returns "Not found" because "X" is not in "Hello".

  10. =IFERROR(TEXT("ABC", "0.00"), "Invalid format")
    Returns "Invalid format" because "ABC" cannot be converted to a number.

  11. =IFERROR(NETWORKDAYS("2023-02-30", "2023-03-01"), "Invalid date")
    Returns "Invalid date" because "2023-02-30" is invalid.

  12. =IFERROR(INDIRECT("Sheet2!A1"), "Sheet not found")
    Returns "Sheet not found" if "Sheet2" does not exist.

  13. =IFERROR(ROUND("Text", 2), "Invalid number")
    Returns "Invalid number" because "Text" is not a number.

  14. =IFERROR(POWER(2, "A"), "Invalid exponent")
    Returns "Invalid exponent" because "A" is not a number.

  15. =IFERROR(TIME(25, 0, 0), "Invalid time")
    Returns "Invalid time" because 25 is not a valid hour.

  16. =IFERROR(SUM("Text"), "Invalid input")
    Returns "Invalid input" because "Text" cannot be summed.

  17. =IFERROR(LOG(0), "Undefined")
    Returns "Undefined" because the logarithm of 0 is undefined.

  18. =IFERROR(CHOOSE(5, "A", "B", "C"), "Out of range")
    Returns "Out of range" because 5 is beyond the number of choices.

  19. =IFERROR(REPLACE("Text", 0, 1, "X"), "Invalid position")
    Returns "Invalid position" because 0 is not a valid start position.

  20. =IFERROR(SUBSTITUTE("Text", "X", "Y"), "No match")
    Returns "No match" if "X" is not found in "Text".



2. ISERROR Function

The ISERROR function checks if a formula results in any error and returns TRUE or FALSE.

Examples:

  1. =ISERROR(A1/B1)
    Returns TRUE if B1 is 0.

  2. =ISERROR(VLOOKUP("Apple", A1:B10, 2, FALSE))
    Returns TRUE if "Apple" is not found.

  3. =ISERROR(SQRT(-1))
    Returns TRUE because the square root of a negative number is an error.

  4. =ISERROR(1/0)
    Returns TRUE because division by zero is an error.

  5. =ISERROR(MATCH("Banana", A1:A10, 0))
    Returns TRUE if "Banana" is not found.

  6. =ISERROR(DATE(2023, 13, 1))
    Returns TRUE because month 13 is invalid.

  7. =ISERROR(INDEX(A1:A10, 11))
    Returns TRUE because the index is beyond the range.

  8. =ISERROR(LEFT("Text", -1))
    Returns TRUE because the length is negative.

  9. =ISERROR(FIND("X", "Hello"))
    Returns TRUE because "X" is not in "Hello".

  10. =ISERROR(TEXT("ABC", "0.00"))
    Returns TRUE because "ABC" cannot be converted to a number.

  11. =ISERROR(NETWORKDAYS("2023-02-30", "2023-03-01"))
    Returns TRUE because "2023-02-30" is invalid.

  12. =ISERROR(INDIRECT("Sheet2!A1"))
    Returns TRUE if "Sheet2" does not exist.

  13. =ISERROR(ROUND("Text", 2))
    Returns TRUE because "Text" is not a number.

  14. =ISERROR(POWER(2, "A"))
    Returns TRUE because "A" is not a number.

  15. =ISERROR(TIME(25, 0, 0))
    Returns TRUE because 25 is not a valid hour.

  16. =ISERROR(SUM("Text"))
    Returns TRUE because "Text" cannot be summed.

  17. =ISERROR(LOG(0))
    Returns TRUE because the logarithm of 0 is undefined.

  18. =ISERROR(CHOOSE(5, "A", "B", "C"))
    Returns TRUE because 5 is beyond the number of choices.

  19. =ISERROR(REPLACE("Text", 0, 1, "X"))
    Returns TRUE because 0 is not a valid start position.

  20. =ISERROR(SUBSTITUTE("Text", "X", "Y"))
    Returns TRUE if "X" is not found in "Text".



3. ISERR Function

The ISERR function checks if a formula results in any error except #N/A and returns TRUE or FALSE.

Examples:

  1. =ISERR(A1/B1)
    Returns TRUE if B1 is 0.

  2. =ISERR(VLOOKUP("Apple", A1:B10, 2, FALSE))
    Returns TRUE if "Apple" is not found.

  3. =ISERR(SQRT(-1))
    Returns TRUE because the square root of a negative number is an error.

  4. =ISERR(1/0)
    Returns TRUE because division by zero is an error.

  5. =ISERR(MATCH("Banana", A1:A10, 0))
    Returns TRUE if "Banana" is not found.

  6. =ISERR(DATE(2023, 13, 1))
    Returns TRUE because month 13 is invalid.

  7. =ISERR(INDEX(A1:A10, 11))
    Returns TRUE because the index is beyond the range.

  8. =ISERR(LEFT("Text", -1))
    Returns TRUE because the length is negative.

  9. =ISERR(FIND("X", "Hello"))
    Returns TRUE because "X" is not in "Hello".

  10. =ISERR(TEXT("ABC", "0.00"))
    Returns TRUE because "ABC" cannot be converted to a number.

  11. =ISERR(NETWORKDAYS("2023-02-30", "2023-03-01"))
    Returns TRUE because "2023-02-30" is invalid.

  12. =ISERR(INDIRECT("Sheet2!A1"))
    Returns TRUE if "Sheet2" does not exist.

  13. =ISERR(ROUND("Text", 2))
    Returns TRUE because "Text" is not a number.

  14. =ISERR(POWER(2, "A"))
    Returns TRUE because "A" is not a number.

  15. =ISERR(TIME(25, 0, 0))
    Returns TRUE because 25 is not a valid hour.

  16. =ISERR(SUM("Text"))
    Returns TRUE because "Text" cannot be summed.

  17. =ISERR(LOG(0))
    Returns TRUE because the logarithm of 0 is undefined.

  18. =ISERR(CHOOSE(5, "A", "B", "C"))
    Returns TRUE because 5 is beyond the number of choices.

  19. =ISERR(REPLACE("Text", 0, 1, "X"))
    Returns TRUE because 0 is not a valid start position.

  20. =ISERR(SUBSTITUTE("Text", "X", "Y"))
    Returns TRUE if "X" is not found in "Text".



4. IFNA Function

The IFNA function returns a custom value if a formula results in #N/A; otherwise, it returns the result of the formula.

Examples:

  1. =IFNA(VLOOKUP("Apple", A1:B10, 2, FALSE), "Not found")
    Returns "Not found" if "Apple" is not in the range.

  2. =IFNA(MATCH("Banana", A1:A10, 0), "Not in list")
    Returns "Not in list" if "Banana" is not found.

  3. =IFNA(INDEX(A1:A10, 11), "Out of range")
    Returns "Out of range" because the index is beyond the range.

  4. =IFNA(FIND("X", "Hello"), "Not found")
    Returns "Not found" because "X" is not in "Hello".

  5. =IFNA(INDIRECT("Sheet2!A1"), "Sheet not found")
    Returns "Sheet not found" if "Sheet2" does not exist.

  6. =IFNA(CHOOSE(5, "A", "B", "C"), "Out of range")
    Returns "Out of range" because 5 is beyond the number of choices.

  7. =IFNA(SUBSTITUTE("Text", "X", "Y"), "No match")
    Returns "No match" if "X" is not found in "Text".

  8. =IFNA(HLOOKUP("Apple", A1:B10, 2, FALSE), "Not found")
    Returns "Not found" if "Apple" is not in the range.

  9. =IFNA(XLOOKUP("Apple", A1:A10, B1:B10), "Not found")
    Returns "Not found" if "Apple" is not in the range.

  10. =IFNA(FILTER(A1:A10, B1:B10="Apple"), "No match")
    Returns "No match" if no rows match the condition.

  11. =IFNA(UNIQUE(FILTER(A1:A10, B1:B10="Apple")), "No unique values")
    Returns "No unique values" if no rows match the condition.

  12. =IFNA(SORT(FILTER(A1:A10, B1:B10="Apple")), "No data")
    Returns "No data" if no rows match the condition.

  13. =IFNA(SEQUENCE(0), "Invalid sequence")
    Returns "Invalid sequence" because the sequence length is 0.

  14. =IFNA(RANDARRAY(0, 0), "Invalid array")
    Returns "Invalid array" because the array dimensions are invalid.

  15. =IFNA(SPILL("A1:A10"), "No spill range")
    Returns "No spill range" if the spill range is invalid.

  16. =IFNA(LET(x, 1/0, x), "Error in calculation")
    Returns "Error in calculation" because of division by zero.

  17. =IFNA(LAMBDA(x, x^2)("Text"), "Invalid input")
    Returns "Invalid input" because "Text" cannot be squared.

  18. =IFNA(BYROW(A1:A10, LAMBDA(x, x^2)), "Error in calculation")
    Returns "Error in calculation" if any cell in the range contains an error.

  19. =IFNA(BYCOL(A1:B10, LAMBDA(x, SUM(x))), "Error in calculation")
    Returns "Error in calculation" if any column contains an error.

  20. =IFNA(MAP(A1:A10, LAMBDA(x, x^2)), "Error in calculation")
    Returns "Error in calculation" if any cell in the range contains an error.



5. ISNA Function

The ISNA function checks if a formula results in #N/A and returns TRUE or FALSE.

Examples:

  1. =ISNA(VLOOKUP("Apple", A1:B10, 2, FALSE))
    Returns TRUE if "Apple" is not found.

  2. =ISNA(MATCH("Banana", A1:A10, 0))
    Returns TRUE if "Banana" is not found.

  3. =ISNA(INDEX(A1:A10, 11))
    Returns TRUE because the index is beyond the range.

  4. =ISNA(FIND("X", "Hello"))
    Returns TRUE because "X" is not in "Hello".

  5. =ISNA(INDIRECT("Sheet2!A1"))
    Returns TRUE if "Sheet2" does not exist.

  6. =ISNA(CHOOSE(5, "A", "B", "C"))
    Returns TRUE because 5 is beyond the number of choices.

  7. =ISNA(SUBSTITUTE("Text", "X", "Y"))
    Returns TRUE if "X" is not found in "Text".

  8. =ISNA(HLOOKUP("Apple", A1:B10, 2, FALSE))
    Returns TRUE if "Apple" is not in the range.

  9. =ISNA(XLOOKUP("Apple", A1:A10, B1:B10))
    Returns TRUE if "Apple" is not in the range.

  10. =ISNA(FILTER(A1:A10, B1:B10="Apple"))
    Returns TRUE if no rows match the condition.

  11. =ISNA(UNIQUE(FILTER(A1:A10, B1:B10="Apple")))
    Returns TRUE if no rows match the condition.

  12. =ISNA(SORT(FILTER(A1:A10, B1:B10="Apple")))
    Returns TRUE if no rows match the condition.

  13. =ISNA(SEQUENCE(0))
    Returns TRUE because the sequence length is 0.

  14. =ISNA(RANDARRAY(0, 0))
    Returns TRUE because the array dimensions are invalid.

  15. =ISNA(SPILL("A1:A10"))
    Returns TRUE if the spill range is invalid.

  16. =ISNA(LET(x, 1/0, x))
    Returns TRUE because of division by zero.

  17. =ISNA(LAMBDA(x, x^2)("Text"))
    Returns TRUE because "Text" cannot be squared.

  18. =ISNA(BYROW(A1:A10, LAMBDA(x, x^2)))
    Returns TRUE if any cell in the range contains an error.

  19. =ISNA(BYCOL(A1:B10, LAMBDA(x, SUM(x))))
    Returns TRUE if any column contains an error.

  20. =ISNA(MAP(A1:A10, LAMBDA(x, x^2)))
    Returns TRUE if any cell in the range contains an error.


These examples demonstrate how to use Excel's error-handling functions effectively

No comments:

Post a Comment