Google-sheets – Arrayformula for running MAX of separate column

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryregex

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more complicated than it actually is. This is what I have by using normal formulas:

enter image description here

Any help would be really appreciated.

Best Answer

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 {QUERY(QUERY(TRANSPOSE(QUERY(TRANSPOSE({
 QUERY(       A2:A,         "limit "&COUNTA(A2:A)), 
 QUERY(OFFSET(A2:A, -1, 0), "limit "&COUNTA(A2:A)-1)}),
 "select "&REGEXREPLACE(JOIN(, IF(LEN(A2:A),
 "max(Col"&ROW(A2:A)-ROW(A2)+1&"),", )), ".\z", "")&"")),
 "select Col2"),
 "limit "&COUNTA(A2:A)),
 {""; QUERY(TRANSPOSE(QUERY(TRANSPOSE({
 QUERY(       A2:A,         "limit "&COUNTA(A2:A)),
 QUERY(OFFSET(A2:A, -1, 0), "limit "&COUNTA(A2:A)-1)}),
 "select "&REGEXREPLACE(JOIN(, IF(LEN(A2:A), 
 "max(Col"&ROW(A2:A)-ROW(A2)+1&"),", )), ".\z", "")&"")),
 "select Col2 
  limit "&COUNTA(A2:A)-1)}}),
 "select "&REGEXREPLACE(JOIN(, IF(LEN(A2:A),
 "max(Col"&ROW(A2:A)-ROW(A2)+1&"),", )), ".\z", "")&"")),
 "select Col2"))

0

demo spreadsheet

Related Topic