Search code examples
google-sheetstransposegoogle-sheets-formulaarray-formulasgoogle-sheets-query

More Efficient Way to Avoid Multiple Calculations #3?

Looking for a more efficient way, possibly array formula for Min/Max down a column. Not sure if array formulas work with this function as I can't get it to.

=ArrayFormula(MAX(INDIRECT("Data!E"&(K42:K169)&":E"&(K43:K170-1))))

=ARRAYFORMULA(MAX(VLOOKUP(K42:K169, {ROW(Data!A:A), Data!E:E}, 2, 0)&VLOOKUP(K43:K170-1, {ROW(Data!A:A), Data!E:E}, 2, 0)))

Note that I am using ROW(Data!A:A) instead of simply ROW(A:A) because the range in current sheet doesn't fit that of sheet Data!.

Here is my current code copied down columns in my sheet.

=MAX(INDIRECT("Data!E"&(K42-1)&":E"&(K43-1)))

Just need a more efficient way. Am I correct that using INDIRECT results in slow calculation times.

Solution 
  • =ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(
 INDIRECT("Data!E"&K42&":E"&MAX(K42:K))&","&IF(MOD(ROW(
 INDIRECT("Data!A1:A"&COUNTA(L42:L)*K41)), K41)=0, "♦", ),,999^99), "♦")), ","), 
 "where Col2 is not null", 0)),
 "select "&TEXTJOIN(",", 1, IF(LEN(L42:L),
 "max(Col"&ROW(A42:A)-ROW(A42)+1&")", ))&"")),
 "select Col2"))

    0