Replacement of Indirect Function for Variable Size Range

Hi,

I'm currently using the INDIRECT function to vary the size of a range specified within an OFFSET function.

See attached example.

In this example, I use the following formula to break up the list of data into discrete groups and then find the maximum value with that group. The reason for using the indirect function is that the size of the groups vary depending upon the case I'm looking at. So I may want 3 items in the group, or 4 or 5. If you vary the Yellow cell you'll see how the data changes. (For the purpose of this file I simply use a RAND() generator to generate a random set of data.).

=MAX(OFFSET(INDIRECT("b1:b"&E$2),E$2*(D7-1),0))

I'd like to replace this formula with one that doesn't use the INDIRECT function as it is impacting the performance of the spreadsheet and I'm getting error messages due to volatility.

Any help is appreciated! Thanks,
AB

Indirect and Offset.xlsx

Try this in E6 and filled down

=MAX(OFFSET($B$1,(D6-1)*$E$2,0,$E$2,1))

I think I read somewhere that INDEX:INDEX is also volatile.
Not index by itself, but the syntax of Index:Index
Or even just A1:INDEX(...) or INDEX(..):A100

To avoid volitile functions alltogether, try this array formula entered with CTRL + SHIFT + ENTER
=MAX(IF($A$1:$A$27>=((D6-1)*$E$2)+1,IF($A$1:$A$27<=D6*$E$2,$B$1:$B$27)))

Well the OP's goal according to the title was just to eliminate Indirect.
Which was acccomplished.

Then somehow it got switched to eliminating all volatile functions...though not by the OP.

While my original question did ask for a replacement for the INDIRECT function I was really looking for a way to make the formula not volatile. I'm dealing with a significant amount of data to post process.

I'll try some of the suggestions and compare how each work.

Thanks!
AB

So I tried replacing with ARRAYS and now my sheet has slowed to a crawl. I have to stop the recalculation process as it is taking way too long (8 Core Intel i7 2.8 GHz with 16 GB RAM). It is currently at 3% after about 5 minutes.

I may have no choice but to start looking into VBA if the INDIRECT and OFFSET functions are volatile and if arrays are too slow.