Arrayformula for running max of separate column

by pbu50w   Last Updated March 22, 2019 16:03 PM

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 using normal formulas: enter image description here

Any help would be really appreciated.



Answers 1


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

user0
user0
March 22, 2019 16:00 PM

Related Questions


Updated February 06, 2019 12:03 PM

Updated January 18, 2019 09:03 AM

Updated March 30, 2019 23:03 PM

Updated April 13, 2019 13:03 PM

Updated April 13, 2019 13:03 PM