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.
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!
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?
- There are times when using a volatile function like NOW, TODAY or RAND is necessary.
- 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.
- Mike Girvin (my initial exposure to the technique)
- Robert H. Gascon (his comment convinced me)
- Microsoft Excel documentation
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.