Microsoft Excel IFNA, IFERROR &
ISNA Functions are relates to multiple errors arise during the use of excel
formulas, so these functions handle & trap those errors.
The Microsoft Excel IFNA function
is the one of member of errors trapping or handling functions family. The Microsoft
Excel IFNA function returns a specific result when a formula generates the #N/A
type error, and a standard result when no error is detected. IFNA Function is
an elegant way to trap and handle only #N/A type errors.
The Microsoft Excel IFERROR
function is the most popular member of the errors trapping & handling
functions family. The Microsoft Excel IFERROR function returns a custom result
when a formula generates an error, and a standard result when no error is
detected. IFERROR Function is an elegant way to trap and handle multiple types
of errors (like #NUM!, #NAME?, #N/A, #VALUE!, #REF!, #DIV/0!, or #NULL!)
without using the IF Statement.
As well as IFNA & IFERROR, the Microsoft
Excel ISNA function is another basic member of the errors trapping &
handling functions family. The Microsoft Excel ISNA function returns TRUE when
a cell contains the #N/A error and FALSE for any other value, we can use the
ISNA function with the IF function test for an error and display a friendly
message when it appears.
Purpose:
Test for errors & handle it.
Syntaxes of IFNA, IFERROR & ISNA Functions:
The syntaxes of IFNA, IFERROR
& ISNA Functions in Excel are as follows;
- =IFNA(value, value_if_na)
- =IFERROR(value, value_if_error)
- =ISNA(value)
Return value:
TRUE or False Values, the value
we supplied or specified for error.
Arguments:
- Value - The value, reference, or formula to check for any type of error.
- value_if_na - The value to return if #N/A error is found.
- value_if_error - The value to return if any type of error is found.
You can download free
the Complete Practice of Concern Topic of Microsoft Excel File, Click on the below
download icon,
This article explains in simple terms how to
use IFNA, IFERROR & ISNA Functions. It takes a step-by-step approach, &
following the below contents we will touch in this post.
- Microsoft Excel IFNA Function explanation through various examples.
- Microsoft Excel IFERROR Function explanation through various examples.
- Microsoft Excel ISNA Function explanation through various examples.
Let’s start to learn something
new about the IFNA, IFERROR & ISNA Functions of Microsoft Excel.
1. Microsoft
Excel IFNA Function - Example 1.
Microsoft Excel IFNA Function is
used to handle & trap only #N/A error, these types of error is mostly
arise when we using lookup functions like INDEX & MATCH, HLOOKUP OR VLOOKUP
etc., so now we explain IFNA Function through various examples, as shown in below
example,
Explanation: - In this
example we have taken the information of a company budget coding system,
from "Row 2" to "Row 10" which contains the
information of Organization Budget Code, System Generated Budget Code,
Recorded Code (Code for lookup), Function of Index & Match (Lookup
Function is used) & the last "F" column contains the formula
that how we can use the IFNA Function to get our specific return. As we
see that the cell "E6" contains the "#N/A"
error & in IFNA Function we have already mentioned that if #N/A error
found in "E3:E10" range then we will handle the #N/A error
with "" (Empty Cell), so the result shown by "F6" cell which is Empty.
In this scenario, we have used a
formula like below,
=IFNA(E6,"")
|
Example 2.
If in the case when we use a
friendly message if "#N/A" error appears, so then see the
below example,
Explanation: - In this
example we see that from "Row 2" to "Row 10"
which contains the information of Organization Budget Code, System
Generated Budget Code, Recorded Code (Code for lookup), Function of
Index & Match (Lookup Function is used) & the last "F"
column contains the formula that how we can use the IFNA Function to get
our specific return. As we see that the cell "E4" contains the
"#N/A" error & in IFNA Function we have already mentioned
that if #N/A error found in "E3:E10" range then we will
handle the #N/A error with a friendly message "Not Available", so
the result shown by "F4" Cell is "Not Available".
In this scenario, we have used a
formula like below,
=IFNA(E4,"Not
Available")
|
2. Microsoft Excel IFERROR Function - Example 1.
Microsoft Excel IFERROR
Function is used to handle & trap the errors like #NUM!, #NAME?, #N/A,
#VALUE!, #REF!, #DIV/0!, or #NULL!, these types of errors mostly arise when
we using other formulas, so now we explain IFERROR Function through various
examples, see the below example,
Explanation: - In this
example we see that from "Row 2" to "Row 10"
which contains the information of Organization Budget Code, System
Generated Budget Code, Recorded Code (Code for lookup), Function of
Index & Match (Lookup Function is used) & the last "F"
column contains the formula that how we can use the IFERROR Function to
get our specific return. As we see that the cell "E6" contains
the "#N/A" error & in IFERROR Function we have already
mention that if any type of error found in "E3:E10" range
then we will handle the error with "0" (Zero), so the result
shown by "F6" Cell is "0.00".
In this scenario, we have used a
formula like below,
=IFERROR(E6,0)
|
Example 2.
If in the case when we use a
friendly message if any type of error appears, so then see the
below example,
Explanation: - In this
example we see that from "Row 2" to "Row 10"
which contains the information of company’s Production, Units,
Function of Average & the last "E" column contains
formula that how we can use the IFERROR Function to get our specific
return. As we see that the cell "D8" contains the "#DIV/0!"
error & in IFERROR Function we have already mentioned that if any type of
error found in "D3:D10" range then we will handle error
with a friendly message "Put the # of Units", so the result
shown by "E8" Cell is "Put the # of Units".
In this scenario, we have used a
formula like below,
=IFERROR(D8,"Put
the # of Units")
|
3. Microsoft Excel ISNA Function - Example 1.
Microsoft Excel ISNA Function is used to
handle & trap only #N/A error, this types of error is mostly arise
when we using lookup functions like INDEX & MATCH, HLOOKUP OR VLOOKUP etc.,
& ISNA Function returns TRUE When such error found, otherwise FALSE, so now
we explains ISNA Function through various examples, let’s see the below
example,
Explanation: - In this
example we see that from "Row 2" to "Row 10"
which contains the information of Organization Budget Code, System
Generated Budget Code, Recorded Code (Code for lookup), Function of
Index & Match (Lookup Function is used) & the last "F"
column contains formula that how we can use the ISNA Function to get
our specific return. As we see that the cell "E6" contains the
"#N/A" error, in the definition of ISNA Function we have mentioned
that if concern error found then the return will be TRUE, so the result
shown in "F6" Cell is "TRUE".
In this scenario, we have used a
formula like below,
=ISNA(E6)
|
Example 2.
If in the case when we use a
friendly message if "#N/A" error appears, so then see the
below example,
Explanation: - In this
example we see that from "Row 2" to "Row 10"
which contains the information of Organization Budget Code, System
Generated Budget Code, Recorded Code (Code for lookup), Function of
Index & Match (Lookup Function is used) & the last "F"
column contains formula that how we can use the ISNA Function to get
our specific return. As we see that the cell "E10" contains
the "#N/A" error, & in ISNA Function we have already
mention that if concern type of error found in "E3:E10"
range then we will handle error with a friendly message "Error
Confirmed", so the result shown by "F10" Cell is "Error
Confirmed".
In this scenario, we have used a
formula like below,
=IF(ISNA(E10),"Error
Confirmed","Correct Value")
|
No comments:
Post a Comment