Offset vs Index

[Jeffrey Mahoney]

I keep getting told that the function OFFSET is volatile and very inefficient. That each time a calculation is done, Offset always refreshes even if the formulas with Offset have nothing to do with the edited cell. I use Offset in dynamic named range formulas like in the example below.

Will it make a difference in a named range?

Is the efficiency gain going to be realized only if I change a large number of cell formulas?

Is this:
=INDEX($A$3:$A$4,2,1):INDEX($A$3:$ZZ$123456,MATCH(1E+300,$A:$A,1)-ROW($A$3)+1,40)

better than:
=OFFSET('Header Data'!$A$3,1,0,MATCH(1E+300,'Header Data'!$A:$A,1)-ROW('Header Data'!$A$3),40)

 

[Eric W]

Your basic description of how OFFSET works, and why it's volatile is largely correct. And some people avoid all volatile functions as a matter of principle. HOWEVER, the story is not as simple as that!

I'm relying on some other information I've found, some of which I'll include links for, some of which I can't find anymore. I can't independently verify what I read, so read the following links with a critical eye.

First, I read an article by a Microsoft engineer which said that OFFSET is very fast, so you shouldn't worry about performance with it. Yes, it may calculate more often, but even so, you may end up better off than with a workaround that takes longer to calculate. I suspect that you'll never see a difference unless you have a "large" number of them in your worksheet. "Large" of course is dependent on many factors, so it's impossible to say that you're OK if you use less than 1000 (for example) OFFSETs. If you actually perform some comparisons, feel free to post your results.

Second, if I read this article correctly:

Talking 'dirty' about volatile functions in Excel

To speed up Excel workbooks, modellers should plan to keep the number of volatile functions and range names to a manageable minimum.

www.fm-magazine.com

your improved, theoretically non-volatile INDEX function is actually volatile too! That sort of negates switching OFFSET to INDEX in many cases. I like the final section "Word to the Wise" that basically states that a good spreadsheet is a balancing act, and you have to balance the use of volatile functions, with clarity, functionality, Conditional Formatting, and other factors. Just excluding all volatile functions is not necessarily the "best" solution.

This article:

Volatile Functions – What’s the Big Deal?

A Volatile Function such as RAND, TODAY, OFFSET, or INDIRECT is recalculated every time Excel recalculates. That may be a problem, or not.

www.vertex42.com

also gives a good rundown of volatile functions, with the pros and cons. A lot of times on this forum, when you're just focused on a single formula and not the bigger picture, it's just easier to avoid volatile functions, so as to avoid this kind of discussion. But I don't think that it's always that simple.

Anyway, that's my 2 cents worth, hope you get something useful from it.

 

[Jeffrey Mahoney]

Hey, thank you for the response.

The first article pointed out that Index can be volatile. The author of the other article seemed to embrace volatile functions as if they're not that big of a deal.

I'm going to need to keep an open mind while doing some more research. Meanwhile, I was in the middle of changing all my dynamic named ranges with offset to index. That is coming to a grinding halt because I don't think it'll make enough of a difference.