Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querycumulative-sum

More Efficient Way to Avoid Multiple Calculations #4?

Need to know how to put Max Offset function into array formula.

=ArrayFormula(MAX(OFFSET(M42:M46,0,0,5,1)))

This is what I am wanting to change it from.

=MAX(OFFSET(M42,0,0,5,1))

Here is the example in sheets. https://docs.google.com/spreadsheets/d/180M_qJCE0Bhz5jRX-tAyBc7DIp7HQpCNVGGlrkuguso/edit?usp=sharing

Solution

  • why not:

    =ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(ARRAY_CONSTRAIN(SPLIT(TRANSPOSE(QUERY(
 TRANSPOSE(ARRAY_CONSTRAIN(IF(IFERROR(SPLIT({"";REPT("♦"&ROW(INDIRECT("A1:A"&COUNTA(M42:M))),
 ROW(INDIRECT("A1:A"&COUNTA(M42:M))))}, "♦"))<>"", , TRANSPOSE(SPLIT(REPT(
 INDIRECT("M42:M"&COUNTA(M42:M)+41)&" ", COUNTA(M42:M)-5+1), " "))), 
 COUNTA(M42:M)-5+1, COUNTA(M42:M))), , COUNTA(M42:M))), " "), COUNTA(M42:M)-5+1, 5)),
 "select "&TEXTJOIN(",", 1, IF(LEN(TRANSPOSE(SPLIT(REPT("♥♠", COUNTA(M42:M)-5+1), "♥"))),
 "max(Col"&ROW(A1:A)&")", ))&"")),
 "select Col2"))

    0