0

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.

0

=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

  • Thanks for the reply, unfortunately I get a "#REF!" error with the description: "Error:Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1. Actual: 2." – pbu50w Mar 22 at 16:49
  • @pbu50w answer updated and added demo sheet. (side note: due to offset this needs to be run from 2nd row onwards) – user0 Mar 22 at 17:46
  • Thanks. The formula seems to work up to a point (only row 11). If I have a large list the results break down after row 11 when adding more data. See my demo sheet docs.google.com/spreadsheets/d/… – pbu50w Mar 22 at 18:34
  • @pbu50w indeed this works only unless there are two consecutive rows with values lower than total acquired max. it could be tweaked to account for more consecutive rows. is your dataset predictable enough that you could say how many rows could have the lower value than acquired max? – user0 Mar 22 at 21:21
  • Unfortunately not. The datasets I will be using this with are dynamic and unpredictable and will vary in length considerably (some being many hundreds of rows). Is there anywhere with an explanation or breakdown of how this formula works so I can understand it better? (Thanks for your input so far). – pbu50w Mar 23 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.