Excel Function Friday: ISREF and the IS Functions

ISREF and the IS Functions — although it sounds like the name of a geeky band, it’s not. Today we’ll take a look at ISREF, which is one of the 9 IS functions that are lumped together in the Excel Help files.

How sad — 9 functions that never get to shine on their own. We’ll give ISREF a few minutes in the spotlight, to see how it works.

And remember, you can learn more about functions in my 30 Functions in 30 Days ebook kit.

The Excel IS Functions

The following IS functions are listed on one page in Excel Help:

  • ISBLANK(value)
  • ISERR(value)
  • ISERROR(value)
  • ISLOGICAL(value)
  • ISNA(value)
  • ISNONTEXT(value)
  • ISNUMBER(value)
  • ISREF(value)
  • ISTEXT(value)

They all work in the same way — the function tests a value, and returns TRUE if the value passes the test.

NOTE: There is also a new ISFORMULA function for Excel 2013 and later versions.

IS Function Examples

For example, if cell B2 contains a number, the ISNUMBER formula will result in TRUE:

=ISNUMBER(B2)

isfunctions01

If cell B3 contains anything except text, even if cell B3 is blank, the ISNONTEXT formula will result in TRUE:

=ISNONTEXT(B3)

isfunctions02

The other IS functions work the same way, and give the expected results — except for ISREF.

ISREF Function Problems

With a reference in cell B2, the ISREF function returns TRUE as the result. However, it also returns TRUE when there is a number in cell B3.

isfunctions03

The ISREF function isn’t testing what’s in the referenced cell, it’s testing the reference within the formula. And because both B2 and B3 are references, the result is TRUE.

So, the ISREF function won’t help you assess whether there is a reference another cell.

ISREF Uses

If you can’t use ISREF to detect a reference in another cell, how can you use it? Well, it can check the results of other formulas, to see if they have returned a valid reference. You could use ISREF, instead of ISERROR, in your formulas that need references.

For example, the INDIRECT function returns a reference, and INDIRECT(“D1”) creates a valid reference. If cell B2 contains the text “D1”, this formula results in TRUE:

=ISREF(INDIRECT(B2))

isfunctions04

The first OFFSET formula show below is not valid, because there is no cell that is 1 column to the left of cell A1, so the ISREF result is FALSE

=ISREF(OFFSET(A1,0,-1))

The second OFFSET formulas returns a valid reference to cell B1, so the ISREF result is TRUE.

isfunctions05

Any Other Uses for ISREF Function?

Do you use ISREF in your formulas? Can you think of any other examples for using it?

______________

Save

You may also like...

6 Responses

  1. Luke Wisbey says:

    Hi Debra, are we allowed to extend this to VBA I wonder ?

    I sometimes use ISREF in the context outlined above via Evaluate as an alternative method for validating existence of a sheet…

    If Not [ISREF(Sheet1!A1)] Then
    Sheets.Add.Name = “Sheet1?
    End If

  2. Thanks Luke, it’s great to see the VBA example.

  3. Peter says:

    Hi Debra

    Quite a while since your post but I have found a couple of uses for ISREF. One is to catch an initialisation issue in a time series or table where one needs to reference the previous time period or value. Taking the latter case, one might wish to refer to a previous balance when working out the current balance or the interest due. If one were to define the name ‘previous’ to refer to the row =R[-1] then the previous balance is given as the intersection

    = previous [Balance]

    That is perfect until you consider the opening balance when you would be referring to the header row. You could choose to write a different formula in the first row or to trap the error but it is more elegant to make a specific test of the form

    = IF( ISREF(previous Table1[#Headers]), principal, previous [Balance] ) * interestRate

    That is, if the previous row includes the table header, use the initial loan as the basis for the interest calculation rather than the previous balance. This way the formula is uniform down the table.

  4. vknowles says:

    You can get ISREF to check whether a cell contains a reference (if you needed such a thing) by the following formula:

    =ISREF(INDIRECT(B1))

    … which will indicate whether the _value_ of cell B1 is itself a reference. That includes whether the cell contains the name of a named range.

Leave a Reply

Your email address will not be published. Required fields are marked *