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.

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

ISNONTEXT Function Excample

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

=ISNONTEXT(B3)

ISNONTEXT Function Example
ISNONTEXT Function Example

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

ISREF Function Problems

In the screen shot below, there is  a reference in cell B2, with the following formula: =D1

In cell D2, the ISREF function returns TRUE as the result.

However, the ISREF function also returns TRUE in cell D3, even though there is a typed value in cell B3 — the number 7

isfunctions03

How ISREF Function Works

The ISREF function isn’t testing what’s in the referenced cell, it’s testing the reference within the formula.

And because the ISREF formulas in both D2 and D3 contain references, the result is TRUE for both formulas.

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, ISREF 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, in the screen shot below, the INDIRECT function in cell D2 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

ISREF With OFFSET

The first OFFSET formula in the screen shot 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))

Hoever, 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

8 thoughts on “Excel Function Friday: ISREF and the IS Functions”

  1. 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. 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.

  3. 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.

  4. This is really frustrating to read, uncharacteristic for your normally outstanding work; but in fact you’re the 4th of 4 websites I’ve just checked that all were guilty of the same arcaneness on this.

    The problem is that it explains that it is “testing the reference” but what does that arcane phrase mean? Other sites say ~”it tests if there is a reference”. What does that mean??? Does “is a reference” mean, that the cell exists? Or that it refers to a cell or range? Or that it refers to a *VALID* cell or range?

    Extending on (not replacing) that, does TRUE signify that #REF is not produced, and FALSE signify that #REF is indeed produced?

    Sorry, I’m just really crabby, and thought since you are so careful and conscientious that you would realize that even advanced users would not innately understand what “testing the reference” means. I’ll go over the “ISREF Uses” section again to see if more light is shed.

    So I’m wondering if “With a reference in cell B2, the ISREF function returns TRUE” means that B2 refers to some other cell or range? (Curiously, I wonder further, does it ensure that an equal sign exists – i.e. that ISFORMULA is TRUE?)

    But how about this: I’ve actually seen uses in the wild and they appear to be testing for the existence of the REFERRED cell (technically, range). Not “whether there is a ‘reference'” whatever that means. What I’ve seen is “=if(ISREF(Sheet999!A1) then”… for determining if there IS a sheet 999 in the activeworkbook. Is that what “is a reference” means? That it’s a valid one? Moreover that doesn’t produce #REF ?

    Thanks. And thanks for everything. I’m as always grateful. Just crabby.

  5. A reference is a range address. It does not matter what’s in the range the address points to. In fact, ISREF doesn’t even evaluate the range address (I think). So

    =ISREF(A1) is True because A1 is a reference to a range. ISREF isn’t looking at what’s in A1, it’s evaluating that the argument you passed is a reference to a range.

    =ISREF(“A1”) is False because the argument passed, “A1”, is a string even though it resembles a range address.

    =ISREF(1) is False because the argument passed is a number.

    =ISREF(1:1) is True because the argument passed is a reference to the whole row #1.

    =ISREF(A10) when entered in A10 returns True, not a circular error. That’s why I think it doesn’t evaluate the existence/contents of the argument, just whether it’s a valid range reference.

    =ISREF(A3000000) returns False. Even though it has the form of a range address (reference), it’s not valid because there’s not that many rows in a worksheet.

    =ISREF(sheet2!A1) where Sheet2 doesn’t exist will launch the file-open dialog. If you click Cancel, the function returns True, but I don’t think it should. So sometimes it doesn’t have to a “valid” range address.

    So what’s the use case for ISREF? I don’t know, I’ve never used it. But I’ll bet it was invented because some functions return a reference or a value, depending on the context. Debra’s INDIRECT example is one. INDEX is another.

    =INDEX(A1:A10,2) return whatever is in A2. It returns a value, or seems to. In reality, it’s returning a reference to A2, but since that’s the only thing in the cell, Excel evaluates it similar to if you had typed =A2 in the cell. Since index returns a reference – not the value in the cell that it resolves to, but a reference to the actual cell itself, you can do things like

    =SUM(F1:INDEX(F1:F10,COUNT(F1:F10)))

    The INDEX function is right after the colon as if it were a cell reference (because it is). That means

    =ISREF(INDEX(F1:F10,COUNT(F1:F10))) returns True because INDEX returns a reference.

    And…

    =ISREF(SUM(A1:A3)) returns False because SUM isn’t one of the functions that returns a reference – it only returns a value or an error.

    Maybe ISREF was created so you could evaluate whether a function returned a reference or a value. I don’t know. Like I said, the next use case I find for ISREF will be the first.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.