Non-volatile Excel formula to compute sum across the range at arbitrary offset from a given cell

Question

It is well-known that Excel's OFFSET function is volatile and will cause recompute and therefore Excel will bother the user with asking to save any loaded file having such function even when the user made no actual changes.

It is also pretty well-known that this can be worked around using non-volatile INDEX function.

So:

• =OFFSET($A$1,0,5) will return a value of F1 cell and it will be volatile
• =INDEX($A$1,0+1,5+1) will return a value of the same F1 cell and it will not be volatile

And now let's try to do the same for a range, not a single cell:

• =SUM(OFFSET($A$1,0,5,3,1)) will return a sum of values of F1:F3 cells and it will be volatile, as expected
• Trying the same with INDEX leads to a surprise in Office 2007: =SUM(INDEX($A$1:$Z$1000,0+1,5+1):INDEX($A$1:$Z$1000,2+1,5+1)) will return a sum of values of F1:F3 cells but it will be volatile (as confirmed by opening such file and attempting to immediately close it - Excel will ask the user to save the changes).

So, while INDEX() is non-volatile, using INDEX():INDEX() to access the range makes the formula volatile, at least in Office 2007.

Is there any other way to access the range at arbitrary offset that would not cause the volatility?

Update: as per @DjC comment, there is no surprise volatility of INDEX():INDEX() in Office 365, but I am looking for a solution that will work for as many prior versions of Office as possible as I need to maintain backwards compatibility at least all the way to Office 2007.

Answer 1

OK, since there were no answers posted, here is my own summary of the findings based on the comments and my own research.

A. INDEX():INDEX() is not volatile in Office 365, but unfortunately it is volatile in Office 2007. It might be helpful to also establish exactly which prior version of Excel between 2007 and 365 fixes the unwanted volatility issue, but if you need backwards compatibility, INDEX():INDEX() is not your best choice.

B. A solution that is always not volatile regardless of the version of Excel is to use INDEX() with either row or column parameter omitted (or set to 0), this leads to accessing the entire row (or column). Things to know about this solution:

1. It looks like this: =SUM(INDEX(A1:Z1000,,place_your_column_number_here) for columns or =SUM(INDEX(A1:Z1000,place_your_row_number_here,) for rows
2. Specifying 0 instead of omitting the parameter does the same thing: =SUM(INDEX(A1:Z1000,0,place_your_column_number_here) for columns or =SUM(INDEX(A1:Z1000,place_your_row_number_here,0) for rows
3. Limitation: entire row/column is accessed, so you can't specify a smaller range like you can with volatile OFFSET() and/or with INDEX():INDEX()
4. Limitation: exactly one row/column is accessed, so you can't specify a wider/taller range like you can with volatile OFFSET() and/or with INDEX():INDEX()
5. Row/column number is one-based, not zero-based like in OFFSET(), so care must be taken when switching from OFFSET() to INDEX() use.
6. Works fine with not only SUMIF(), as was stated in the comments, but with any other function that takes an array, like SUM(), MIN(), MAX(), AVERAGE(), LINEST() et cetera.

In summary, if you only need to access one row/column, and not an arbitrary rectangular range, then INDEX() with omitted row/column parameter is the best non-volatile choice. If you need an arbitrary rectangular range access, the best but less-than-perfect choice is INDEX():INDEX() as it is non-volatile in at least more recent versions of Excel.

If anyone knows a better solution, please post.