ISNA, ISERROR, and ISBLANK Formula in Excel
- 3 Comment
Excel has many built-in formulas that will check for errors for you. Were going to take a look at three of those formulas, which are ISNA, ISBLANK and ISERROR. Let’s start by taking a look at the formulas:
ISNA(value)
ISERROR(value)
ISBLANK(value)
Each of these formulas looks at value and evaluatues whether it is #N/A (with ISNA), #Value (with ISERROR), or blank (with ISBLANK).
When You Can Use These Formulas
These formulas come in very handy when you are working with a large group of formulas and need further analysis of the results. Having and #N/A or #Value in your results, as I’m sure you may have experienced, will most likely result in receiving more errors the more that particular result is analyzed. As you’ll see in the examples below, vlookup is a very common formula which will return an #N/A if the lookup value is not found. Or if you try to add a string to a number, the result will return #value.
Examples
Example 1:
Further Explanation
There are other “IS” formulas that can be useful with your projects. We will take a look at these later, as I wanted to get you familiar with some of the main formulas I use every day. The rest of these formulas include:
ISERR(value)
ISLOGICAL(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
Related Hints
3 Comments on this post
Trackbacks
-
Mike said:
Excel has a different handling of ISBLANK() and COUNTBLANK()
More specifically, if a cell appears to be blank due to a formula like
(contents of cell A1)
IF(FALSE;”";”the evaluation is TRUE”)
the cell will be blank (”" is an empty string)
ISBLANK(A1) will give FALSE, however
COUNTBLANK(A1) will return 1.January 15th, 2008 at 6:07 am -
Daniel Bruns said:
I need to add 30 days to a date from another cell, but only if I have a number in a 3rd cell
October 23rd, 2008 at 6:53 am -
john said:
Hi Daniel,
You could try something like this if I understood you correctly:
=IF(ISNUMBER(A1),B1+30,B1) put in cell C1
where A1 contains a number or blank and B1 contains a date.
The result of C1 would be the date plus 30 days if there is a number in A1, and just the date from B1 if there is no number in A1.October 23rd, 2008 at 8:53 am
Loading ...