Replace Volatile OFFSET with INDEX

May 25, 2022 - by Bill Jelen

Guerrilla Data Analysis Using Microsoft Excel

Problem: OFFSET is a volatile function. It breaks the smart recalc feature of Excel. A single OFFSET function near the top of the worksheet will cause every formula in the calculation chain to recalc constantly.

Strategy: There is a very unusual syntax of INDEX which replaces OFFSET. The advantage of INDEX is that the function is not volatile.

Normally, a formula such as =INDEX(A5:A11,C5) will look in C5 to find the number 4. INDEX will then return the 4th cell from A5:A11, or the 8 from cell A8:

When INDEX() is next to a colon, it will return a cell reference instead of the value stored in that cell. — Figure 493. The INDEX in C8 is the “normal” use of INDEX

However, when the INDEX function is adjacent to a colon, the behavior changes. The formula in C9 says to SUM from A5:INDEX(). If you use Evaluate Formula to watch the formula get calculated, you will see the INDEX function:

While evaluating the formula, you get to the step SUM(A5:INDEX()). — Figure 494. Immediately before evaluating the INDEX function

returns a cell address of $A$8:

After evaluating one more step, the Evaluate Formula dialog shows SUM(A5:A8) — Figure 495. Click Evaluate and the INDEX returns $A$8 instead of the 8 stored in A8.

You should be able to rewrite most OFFSET functions to use this form of INDEX. Your worksheets will calculate faster because INDEX is not volatile.

Additional Details: A few guys in Australia were working on a new add-in to audit formulas in worksheets. Their add-in “stumbled” when it started uncovering this odd syntax of INDEX. As they tried to improve their add-in, they realized that INDEX had this seemingly undocumented behavior. Thanks to Joe McDaid and Dan Mayoh for passing this technique on to me at one of the ModelOff finals.

This article is an excerpt from Power Excel With MrExcel

Title photo by Laura Ockel on Unsplash