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:
=IFERROR(A1/B1, "Division by zero error")
Returns "Division by zero error" ifB1
is 0.=IFERROR(VLOOKUP("Apple", A1:B10, 2, FALSE), "Not found")
Returns "Not found" if "Apple" is not in the range.=IFERROR(SQRT(-1), "Invalid input")
Returns "Invalid input" because the square root of a negative number is an error.=IFERROR(1/0, "Infinity")
Returns "Infinity" because division by zero is an error.=IFERROR(MATCH("Banana", A1:A10, 0), "Not in list")
Returns "Not in list" if "Banana" is not found.=IFERROR(DATE(2023, 13, 1), "Invalid date")
Returns "Invalid date" because month 13 is invalid.=IFERROR(INDEX(A1:A10, 11), "Out of range")
Returns "Out of range" because the index is beyond the range.=IFERROR(LEFT("Text", -1), "Invalid length")
Returns "Invalid length" because the length is negative.=IFERROR(FIND("X", "Hello"), "Not found")
Returns "Not found" because "X" is not in "Hello".=IFERROR(TEXT("ABC", "0.00"), "Invalid format")
Returns "Invalid format" because "ABC" cannot be converted to a number.=IFERROR(NETWORKDAYS("2023-02-30", "2023-03-01"), "Invalid date")
Returns "Invalid date" because "2023-02-30" is invalid.=IFERROR(INDIRECT("Sheet2!A1"), "Sheet not found")
Returns "Sheet not found" if "Sheet2" does not exist.=IFERROR(ROUND("Text", 2), "Invalid number")
Returns "Invalid number" because "Text" is not a number.=IFERROR(POWER(2, "A"), "Invalid exponent")
Returns "Invalid exponent" because "A" is not a number.=IFERROR(TIME(25, 0, 0), "Invalid time")
Returns "Invalid time" because 25 is not a valid hour.=IFERROR(SUM("Text"), "Invalid input")
Returns "Invalid input" because "Text" cannot be summed.=IFERROR(LOG(0), "Undefined")
Returns "Undefined" because the logarithm of 0 is undefined.=IFERROR(CHOOSE(5, "A", "B", "C"), "Out of range")
Returns "Out of range" because 5 is beyond the number of choices.=IFERROR(REPLACE("Text", 0, 1, "X"), "Invalid position")
Returns "Invalid position" because 0 is not a valid start position.=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:
=ISERROR(A1/B1)
ReturnsTRUE
ifB1
is 0.=ISERROR(VLOOKUP("Apple", A1:B10, 2, FALSE))
ReturnsTRUE
if "Apple" is not found.=ISERROR(SQRT(-1))
ReturnsTRUE
because the square root of a negative number is an error.=ISERROR(1/0)
ReturnsTRUE
because division by zero is an error.=ISERROR(MATCH("Banana", A1:A10, 0))
ReturnsTRUE
if "Banana" is not found.=ISERROR(DATE(2023, 13, 1))
ReturnsTRUE
because month 13 is invalid.=ISERROR(INDEX(A1:A10, 11))
ReturnsTRUE
because the index is beyond the range.=ISERROR(LEFT("Text", -1))
ReturnsTRUE
because the length is negative.=ISERROR(FIND("X", "Hello"))
ReturnsTRUE
because "X" is not in "Hello".=ISERROR(TEXT("ABC", "0.00"))
ReturnsTRUE
because "ABC" cannot be converted to a number.=ISERROR(NETWORKDAYS("2023-02-30", "2023-03-01"))
ReturnsTRUE
because "2023-02-30" is invalid.=ISERROR(INDIRECT("Sheet2!A1"))
ReturnsTRUE
if "Sheet2" does not exist.=ISERROR(ROUND("Text", 2))
ReturnsTRUE
because "Text" is not a number.=ISERROR(POWER(2, "A"))
ReturnsTRUE
because "A" is not a number.=ISERROR(TIME(25, 0, 0))
ReturnsTRUE
because 25 is not a valid hour.=ISERROR(SUM("Text"))
ReturnsTRUE
because "Text" cannot be summed.=ISERROR(LOG(0))
ReturnsTRUE
because the logarithm of 0 is undefined.=ISERROR(CHOOSE(5, "A", "B", "C"))
ReturnsTRUE
because 5 is beyond the number of choices.=ISERROR(REPLACE("Text", 0, 1, "X"))
ReturnsTRUE
because 0 is not a valid start position.=ISERROR(SUBSTITUTE("Text", "X", "Y"))
ReturnsTRUE
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:
=ISERR(A1/B1)
ReturnsTRUE
ifB1
is 0.=ISERR(VLOOKUP("Apple", A1:B10, 2, FALSE))
ReturnsTRUE
if "Apple" is not found.=ISERR(SQRT(-1))
ReturnsTRUE
because the square root of a negative number is an error.=ISERR(1/0)
ReturnsTRUE
because division by zero is an error.=ISERR(MATCH("Banana", A1:A10, 0))
ReturnsTRUE
if "Banana" is not found.=ISERR(DATE(2023, 13, 1))
ReturnsTRUE
because month 13 is invalid.=ISERR(INDEX(A1:A10, 11))
ReturnsTRUE
because the index is beyond the range.=ISERR(LEFT("Text", -1))
ReturnsTRUE
because the length is negative.=ISERR(FIND("X", "Hello"))
ReturnsTRUE
because "X" is not in "Hello".=ISERR(TEXT("ABC", "0.00"))
ReturnsTRUE
because "ABC" cannot be converted to a number.=ISERR(NETWORKDAYS("2023-02-30", "2023-03-01"))
ReturnsTRUE
because "2023-02-30" is invalid.=ISERR(INDIRECT("Sheet2!A1"))
ReturnsTRUE
if "Sheet2" does not exist.=ISERR(ROUND("Text", 2))
ReturnsTRUE
because "Text" is not a number.=ISERR(POWER(2, "A"))
ReturnsTRUE
because "A" is not a number.=ISERR(TIME(25, 0, 0))
ReturnsTRUE
because 25 is not a valid hour.=ISERR(SUM("Text"))
ReturnsTRUE
because "Text" cannot be summed.=ISERR(LOG(0))
ReturnsTRUE
because the logarithm of 0 is undefined.=ISERR(CHOOSE(5, "A", "B", "C"))
ReturnsTRUE
because 5 is beyond the number of choices.=ISERR(REPLACE("Text", 0, 1, "X"))
ReturnsTRUE
because 0 is not a valid start position.=ISERR(SUBSTITUTE("Text", "X", "Y"))
ReturnsTRUE
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:
=IFNA(VLOOKUP("Apple", A1:B10, 2, FALSE), "Not found")
Returns "Not found" if "Apple" is not in the range.=IFNA(MATCH("Banana", A1:A10, 0), "Not in list")
Returns "Not in list" if "Banana" is not found.=IFNA(INDEX(A1:A10, 11), "Out of range")
Returns "Out of range" because the index is beyond the range.=IFNA(FIND("X", "Hello"), "Not found")
Returns "Not found" because "X" is not in "Hello".=IFNA(INDIRECT("Sheet2!A1"), "Sheet not found")
Returns "Sheet not found" if "Sheet2" does not exist.=IFNA(CHOOSE(5, "A", "B", "C"), "Out of range")
Returns "Out of range" because 5 is beyond the number of choices.=IFNA(SUBSTITUTE("Text", "X", "Y"), "No match")
Returns "No match" if "X" is not found in "Text".=IFNA(HLOOKUP("Apple", A1:B10, 2, FALSE), "Not found")
Returns "Not found" if "Apple" is not in the range.=IFNA(XLOOKUP("Apple", A1:A10, B1:B10), "Not found")
Returns "Not found" if "Apple" is not in the range.=IFNA(FILTER(A1:A10, B1:B10="Apple"), "No match")
Returns "No match" if no rows match the condition.=IFNA(UNIQUE(FILTER(A1:A10, B1:B10="Apple")), "No unique values")
Returns "No unique values" if no rows match the condition.=IFNA(SORT(FILTER(A1:A10, B1:B10="Apple")), "No data")
Returns "No data" if no rows match the condition.=IFNA(SEQUENCE(0), "Invalid sequence")
Returns "Invalid sequence" because the sequence length is 0.=IFNA(RANDARRAY(0, 0), "Invalid array")
Returns "Invalid array" because the array dimensions are invalid.=IFNA(SPILL("A1:A10"), "No spill range")
Returns "No spill range" if the spill range is invalid.=IFNA(LET(x, 1/0, x), "Error in calculation")
Returns "Error in calculation" because of division by zero.=IFNA(LAMBDA(x, x^2)("Text"), "Invalid input")
Returns "Invalid input" because "Text" cannot be squared.=IFNA(BYROW(A1:A10, LAMBDA(x, x^2)), "Error in calculation")
Returns "Error in calculation" if any cell in the range contains an error.=IFNA(BYCOL(A1:B10, LAMBDA(x, SUM(x))), "Error in calculation")
Returns "Error in calculation" if any column contains an error.=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:
=ISNA(VLOOKUP("Apple", A1:B10, 2, FALSE))
ReturnsTRUE
if "Apple" is not found.=ISNA(MATCH("Banana", A1:A10, 0))
ReturnsTRUE
if "Banana" is not found.=ISNA(INDEX(A1:A10, 11))
ReturnsTRUE
because the index is beyond the range.=ISNA(FIND("X", "Hello"))
ReturnsTRUE
because "X" is not in "Hello".=ISNA(INDIRECT("Sheet2!A1"))
ReturnsTRUE
if "Sheet2" does not exist.=ISNA(CHOOSE(5, "A", "B", "C"))
ReturnsTRUE
because 5 is beyond the number of choices.=ISNA(SUBSTITUTE("Text", "X", "Y"))
ReturnsTRUE
if "X" is not found in "Text".=ISNA(HLOOKUP("Apple", A1:B10, 2, FALSE))
ReturnsTRUE
if "Apple" is not in the range.=ISNA(XLOOKUP("Apple", A1:A10, B1:B10))
ReturnsTRUE
if "Apple" is not in the range.=ISNA(FILTER(A1:A10, B1:B10="Apple"))
ReturnsTRUE
if no rows match the condition.=ISNA(UNIQUE(FILTER(A1:A10, B1:B10="Apple")))
ReturnsTRUE
if no rows match the condition.=ISNA(SORT(FILTER(A1:A10, B1:B10="Apple")))
ReturnsTRUE
if no rows match the condition.=ISNA(SEQUENCE(0))
ReturnsTRUE
because the sequence length is 0.=ISNA(RANDARRAY(0, 0))
ReturnsTRUE
because the array dimensions are invalid.=ISNA(SPILL("A1:A10"))
ReturnsTRUE
if the spill range is invalid.=ISNA(LET(x, 1/0, x))
ReturnsTRUE
because of division by zero.=ISNA(LAMBDA(x, x^2)("Text"))
ReturnsTRUE
because "Text" cannot be squared.=ISNA(BYROW(A1:A10, LAMBDA(x, x^2)))
ReturnsTRUE
if any cell in the range contains an error.=ISNA(BYCOL(A1:B10, LAMBDA(x, SUM(x))))
ReturnsTRUE
if any column contains an error.=ISNA(MAP(A1:A10, LAMBDA(x, x^2)))
ReturnsTRUE
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