0

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.

4
  • 1
    This is not a problem with Office 365, so I'm guessing you're using an older version of Excel. If your only objective here is to SUM a range based on row and column numbers, you could try something like =SUMPRODUCT(A1:Z1000*(ROW(A1:Z1000)<4)*(COLUMN(A1:Z1000)=6)); however, this will only work if all values in range A1:Z1000 are numbers. Alternatively, SUMIFS() will work with a single column returned by INDEX(), for example: =SUMIFS(INDEX(A1:Z1000,,6),criteria_range1,criteria1,...). I tested both with Office 2010 and did not get prompted to save when closing immediately after opening.
    – DjC
    Oct 23, 2023 at 2:42
  • Interesting. I hadn't realised that this semi-volatile property of INDEX had been resolved in O365. Oct 24, 2023 at 6:37
  • If.. the range is actually not (terribly) a lot.. =OFFSET($A$1,0,5)+OFFSET($A$1,0,5+1)+OFFSET($A$1,0,5+2) should do.. right?
    – p._phidot_
    Oct 25, 2023 at 4:13
  • No, the range of 3 values in this post is for illustration purposes only, the problem at hand is to access a range at arbitrary offset and of arbitrary size. Oct 29, 2023 at 14:01

1 Answer 1

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .