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