May 30 2007

ISNA, ISERROR, and ISBLANK Formula in Excel

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.


[ad#in-post-ad]

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.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

8 Comments on this post

Trackbacks

  1. Top Posts in 2008 | Excel Hints wrote:

    [...] ISNA, ISERROR, and ISBLANK Formula in Excel [...]

    January 1st, 2009 at 4:52 pm
  2. Check if a Cell Contains Text | Excel Hints wrote:

    [...] are numerous other similar functions built into Excel (such as isNumeric, ISBlank, ISNA and ISERROR) that work similarly to IsText. We will explore those at a later [...]

    July 8th, 2009 at 4:03 pm
  1. 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
  2. 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
  3. 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
  4. Eng Fook Kang said:

    It great info for end user.

    Thanks

    September 19th, 2009 at 11:58 pm
  5. Bing said:

    John: are you also including the date in reference? if you do, the formula is making it 31 days instead, not 30

    April 26th, 2010 at 7:41 pm
  6. Marcia said:

    Looking for a formula that will look up a different work sheet and cell and IF there is no Value, return the Comment “NO DATA” and if there is a value return the value.

    August 1st, 2013 at 9:59 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives