1

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.

Improve this question
1
  • 1
    Always give expected results.
    – user55949
    Aug 20, 2019 at 11:40

1 Answer 1

Reset to default
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

Improve this answer
6
  • 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."
    – PaulB
    Mar 22, 2019 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, 2019 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/…
    – PaulB
    Mar 22, 2019 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, 2019 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).
    – PaulB
    Mar 23, 2019 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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