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: Any help would be really appreciated.

Tags :

``````=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"))
`````` Related Questions

Updated April 29, 2019 15:03 PM

Updated February 06, 2019 12:03 PM

Updated January 18, 2019 09:03 AM

Updated May 10, 2019 15:03 PM

Updated March 30, 2019 23:03 PM