22 Jan 2019

Robert H. Gascon’s comment from my last post convinced me that I should be using the INDEX function for dynamic ranges not the volatile OFFSET function.

 

What’s Your Opinion?

Vlookup vs Index/Match debate has jumped the shark. There’s other things worth discussing.

Here’s a BIG one:

Whenever possible we should avoid using volatile functions

 

 

 

Where are you on this scale?

For years I’ve been cautious of overusing volatile functions (beware of INDIRECT!). Robert has convinced me to move further to the right on my scale. Why? I’ve used volatile OFFSET to create dynamic ranges when I could’ve used INDEX.

 

What’s a Volatile Function?

Volatile functions include: Today, Now, Indirect, Rand, Randbetween, Offset, etc.

Non volatile functions only calculate when required. =SUM(A1:A10) will not recalculate unless one of the values in range A1:A10 changes. Volatile functions recalculate frequently. In-depth explanations: Liam Bastick, Charles Williams.

 

Dynamic Range?

Sometimes we need to extract a subset of data from a large range. Examples include:

  • Average 5th to 20th values in a column
  • Sum a column from a list of columns
  • Get minimum of the values from a row

We need to dynamically change the range, column or row so we can’t hard code the references.

 

INDEX Reference Form Technique

It requires careful study and practice but it’s worth the effort. Let’s start with this:

 

 

 

 

 

 

=SUM(A2:A7)  SUM function adds numbers in range A2:A7

We want to change the range to A2:A5 or A2:A9 or A2:A7 without touching the formula.

=SUM(A2:INDEX(A1:A9,J13))  Let’s walk through this:

This part  =SUM(A2:  is familiar. SUM adds numbers. The range starts with cell A2.

…and now it gets weird 🙂

INDEX(A1:A9,J13)  normally this returns a single number. If cell J13 is 5 then we get 328 (cell A5 value). BUT we’re including the INDEX after the colon inside the SUM function. What we get isn’t value 328 but a reference to cell A5 !

Dynamically our formula gives us  =SUM(A2:A5)  HUH? Incredible! Life altering!

Change cell J13 to 8 and it gives us =SUM(A2:A8) Isn’t that cool ! We can also make A2 dynamic!

 

Excel File

Download my Excel file to learn more. With practice you’ll feel comfortable with this approach!

 

Why Was I Using OFFSET?

I use INDEX function a lot but not for dynamic ranges. I’ve used OFFSET as I was more comfortable with the syntax and because it was easier to explain OFFSET to business people that I help.

I should’ve incorporated the Reference Form INDEX technique into my skill-set years ago. Overusing OFFSET can increase calculation time (same for overusing array formulas or UDFs).

If there’s an alternative non volatile function then we should use it. Being aware of a concept is not enough. Now I’m putting in the time to learn it thoroughly.

 

Are There Exceptions?

  1. There are times when using a volatile function like NOW, TODAY or RAND is necessary.
  2. You have numbers in column A. You want a cumulative sum in column B. You also want to be able to easily delete a row without breaking the cumulative formula. See sheet (5) in my Excel file.

 

Sources

 

About Me

My name is Kevin Lehrbass. I’m a Data Analyst living in Markham Ontario Canada.

Although I’ve been helping others in Excel for almost 2 decades I’m still learning. Thanks to Robert H. Gascon for convincing me to implement the Index Reference Form syntax into my active skill-set.

It’s not enough to be aware of a concept. It takes time to learn it well enough to be able to apply it when there’s time pressure at work. Index Reference Form is definitely an odd bird but because it’s non volatile it saves calculation time and is well worth learning.

2 Comments

  1. Robert H. Gascon says:

    Hello Kevin,
    OFFSET can be avoided in the cumulative SUM formula by using a relative named range, as follows:
    =SUM(A2,CellAbove)
    CellAbove is a relative named range with this formula:
    ='(5)’!$D1
    You liked the relative named range solution of Jon Wittwer in his post here: https://www.vertex42.com/blog/excel-formulas/create-a-running-balance-in-excel.html
    Jon loves, but avoided, the volatile OFFSET function, I firmly believe that INDEX can do anything that OFFSET can do, so I don’t simply avoid it, I eschew it.
    Cheers,
    Robert

    1. Kevin Lehrbass says:

      Hi Robert,
      I thought that I had replied but looks like my reply didn’t go through 🙁
      Anyway…you’re right that the relative named range technique is the way to go. Minimal effort to get both the desired answer without adding volatility.
      I’ve really been re-evaluating my thoughts on volatility in Excel thanks to you! Most Excel users aren’t even aware of what volatility is. Those that are aware don’t take it very seriously. More awareness is definitely needed. It’s too late when someone is in trouble and says “my workbook is so slow help!”.
      With effort we can reduce the use of volatile functions (and conditional formatting) to minimal cases. I would think that there are some rare cases in which you would use today(), now() or rand() ?
      Thanks for the input Robert!
      Cheers,
      Kevin

Post a comment