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?
- 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.
Sources
- Mike Girvin (my initial exposure to the technique)
- Robert H. Gascon (his comment convinced me)
- Microsoft Excel documentation
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.
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
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
Pingback: Excel Chess Games Viewer | My Spreadsheet Lab
Could you please explain the “weird” part in your article above. I simply don’t understand how this logic works – why would index return a cell reference instead of a value, when on its own, the same function returns a value?
Hi Nevin
It definitely is a strange behavior but that’s just what it does.
The INDEX(A1:A9,J13) gives us the value in cell A5.
But this =SUM(A2:INDEX(A1:A9,J13)) gives us the sum of A2:A5
Somehow when used directly after the : the index result of 5 is converted into A5.
It’s a strange thing but as Robert and others have pointed out it’s more efficient as the formula doesn’t use a volatile function (an alternative approach would be to use OFFSET which is somewhat volatile).
My Excel file has several examples.
Excel is full of these oddities which makes it fun as there’s always more to learn.
Thanks for reading my post Nevin.
Kevin
Pingback: Flirting with Volatility | My Spreadsheet Lab