How To Use Iferror In Excel
The tutorial looks at the practical uses of the Excel ISERROR office and shows how to test unlike formulas for errors.
When you write a formula that Excel does not understand or cannot calculate, it draws your attention to the trouble by showing an mistake message. The ISERROR function can aid you catch errors and provide an alternative when an error is found.
ISERROR part in Excel
The Excel ISERROR function catches all kinds of errors, including #CALC!, #DIV/0!, #Due north/A, #NAME?, #NUM!, #Naught!, #REF!, #VALUE!, and #SPILL!. The result is a Boolean value: TRUE if an fault is detected, Simulated otherwise.
The function is available in all versions of Excel 2000 through 2021 and Excel 365.
The syntax of the ISERROR part is as uncomplicated every bit this:
ISERROR(value)
Where value is the jail cell value or formula to be checked for errors.
Excel ISERROR formula
To create an ISERROR formula in its simplest class, supply a reference to the cell that y'all want to test for errors. For example:
=ISERROR(A2)
In example any error is found, you lot'll get True. If there is no error in the tested cell, you'll get Simulated:
IF ISERROR formula in Excel
To render a custom bulletin or perform a dissimilar adding when an mistake occurs, use ISERROR together with the IF function. The generic formula looks as follows:
IF(ISERROR(formula(…), text_or_calculation_if_error, formula())
Translated into a human language, it says: if the chief formula results in an error, display the specified text or run another calculation, otherwise render a normal result of the formula.
In the image below, dividing the total by the quantity generates a couple of errors in the Price column:
To supplant all dissimilar error codes with a custom text, you can use the following IF ISERROR formula:
=IF(ISERROR(A2/B2), "Unknown", A2/B2)
In Excel 2007 and afterwards versions, the same result tin can be achieved with the assistance of the inbuilt IFERROR function:
=IFERROR(A2/B2, "Unknown")
It should be noted that the IFERROR formula runs a footling faster because it performs the A2/B2 calculation just once. Whereas IF ISERROR calculates it twice - first to see if it generates an error and then again if the test is FALSE.
IF ISERROR VLOOKUP formula
Using ISERROR with VLOOKUP is, in fact, a detail case of the IF ISERROR formula discussed above. When the VLOOKUP role cannot find the lookup value or fails for any other reason, you display a custom text message by using this syntax:
IF(ISERROR(VLOOKUP(…)), "custom_text", VLOOKUP(…))
For this example, let's pull the times from the lookup tabular array (D3:E10) to the main table (A3:B15). If the lookup value (participant'south name) does not be in the lookup table, we'll return "Not qualified".
=IF(ISERROR(VLOOKUP(A3, $D$3:$E$10, 2, False)), "Not qualified", VLOOKUP(A3, $D$3:$East$10, 2, Simulated))
Tip. If you want to display a custom text merely when a lookup value is not institute (#Due north/A error) ignoring other errors, then apply the IFNA VLOOKUP formula in Excel 2013 and later or IF ISNA VLOOKUP in older versions.
IF ISERROR INDEX MATCH formula
When performing a lookup with the help of the Index Friction match combination, yous tin can trap and handle any possible errors past using the same technique - the ISERROR part checks for errors and IF displays the specified text when whatsoever mistake occurs.
IF(ISERROR(Index (return_column, MATCH (lookup_value, lookup_column, 0)))), "custom_text", Alphabetize (return_column, MATCH (lookup_value, lookup_column, 0)))
Suppose the lookup table has times in the first column. As VLOOKUP is unable to look to its left, we use the Alphabetize Lucifer formula to pull the times from column D:
=INDEX($D$3:$D$ten, MATCH(A3, $East$3:$E$x, 0))
And so, you lot nest it in the to a higher place-mentioned generic formula to supervene upon the caught errors with any text you desire:
=IF(ISERROR(Alphabetize($D$three:$D$10, MATCH(A3, $Due east$3:$Due east$10, 0))), "Not qualified", Index($D$three:$D$10, MATCH(A3, $Eastward$3:$E$10, 0)))
Annotation. As with the IF ISERROR VLOOKUP formula, it makes more sense to trap only #N/A errors and do not disguise potential issues with the formula itself. For this, wrap your Index MATH formula in IFNA in Excel 2013 and higher or IF ISNA in earlier versions.
IF ISERROR Yep/No formula
In all the previous examples, IF ISERROR returned the result of the chief formula if it isn't an fault. All the same, information technology can too work in a different way - return something if error and something else if no error.
IF(ISERROR(formula(…)), "text_if_error", "text_if_no_error")
In our sample dataset, suppose you are not interested in the exact times, you simply want to know which participants from group A are qualified and which are not. To do this, use the MATCH function to compare the name in column A confronting the list of the qualified participants in column D, and then serve the results to ISERROR. If the name is non available in column D (MATCH returns an error), get the IF role to display "No" or "Non qualified". If the proper noun appears in column D (no error), return "Yeah" or "Qualified".
=IF(ISERROR(MATCH(A3, $D$three:$D$10, 0)), "No", "Yes" )
How to count the number of errors
To get the number of errors in a certain cavalcade, you need to check a range, not only one cell. For this, "feed" the target range to ISERROR and coerce the returned Boolean values into 1's and 0's using the double unary operator (--). The SUM or SUMPRODUCT function tin can add up the numbers and deliver the terminal effect.
For example:
=SUM(--ISERROR(C2:C10))
Please note, this works as a regular formula simply in Excel 365 and Excel 2021, which support dynamic arrays. In Excel 2019 and before, you need to press Ctrl + Shift + Enter to create an assortment formula (don't type curly brackets manually, that won't work!):
{=SUM(--ISERROR(C2:C10))}
Alternatively, y'all can utilise the SUMPRODUCT function that handles arrays natively, so the formula can exist completed with the usual Enter key in all versions:
=SUMPRODUCT(--ISERROR(C2:C10))
Difference between ISERROR and IFERROR in Excel
Both the ISERROR and IFERROR functions are used to trap and handle errors in Excel. The difference is every bit follows:
- In its pure class, ISERROR merely tests if the value is an error or not. It'southward available in all Excel versions.
- The IFERROR office is designed to suppress or disguise errors - when an error is found, it returns some other value that you specify. It is available in Excel 2007 and college.
At get-go sight, IFERROR looks like a shorthand alternative to the IF ISERROR formula. On a closer look, however, you can notice the difference:
- IFERROR allows you lot to specify only value_if_error. If there is no error, it always returns the result of the tested value/formula.
- IF ISERROR provides more flexibility and lets you handle both situations - what should happen if an error and what if no error.
To meliorate illustrate the betoken, consider these formulas:
=IFERROR(A1, "Calculation mistake")
=IF(ISERROR(A1), "Calculation fault", A1)
These ii formulas are equivalent - both bank check a formula-driven value in A1 and return "Adding error" if information technology'southward an mistake, otherwise - return the value.
But what if you desire to perform some calculation if the value in A1 is not an fault? The IFERROR part is unable to do that. In case of IF ISERROR, simply type the desired calculation in the concluding statement. For instance:
=IF(ISERROR(A1), "Calculation error", A1*two)
As y'all see, this longer variation of the IFERROR formula, which is often considered outdated, can still be useful :)
Bachelor downloads
ISERROR formula examples (.xlsx file)
You may too exist interested in
How To Use Iferror In Excel,
Source: https://www.ablebits.com/office-addins-blog/iserror-function-excel/
Posted by: nelsonbountly.blogspot.com
0 Response to "How To Use Iferror In Excel"
Post a Comment