Search code examples
google-sheetsgoogle-sheets-formula

Calculate the "running max" of data for each set of rows identified by some particular values in another cell

Please see attached photo.

On Google sheet, I want that rows with a particular Month and Week combination be treated as a separate set. Within each of such set, I would like to implement a formula to calculate a 'running Max' of data in column C (I have shown the formula implementation in column J for your understanding). How do I realize the "Running Max" column as indicated?

Here is the link to the Google Sheet: https://docs.google.com/spreadsheets/d/1Af5-pYMFWZ1QtLoaAbPZYMGRvk43JBslUp4KyOFADfA/edit?usp=sharing

enter image description here

Solution

  • Try for MAX

    =ArrayFormula(transpose(query(
if(row(C3:C)>transpose(row(C3:C)),,C3:C)*(transpose(E3:E)=E3:E)*(transpose(D3:D)=D3:D),
"select " & join(",","max(Col" & sequence(1, count(C3:C)) & ")") &
"label "  & join(",","max(Col" & sequence(1, count(C3:C)) & ") ''") & "" )))

    enter image description here

    for MIN

    =ArrayFormula(transpose(query(
if(row(C3:C)>transpose(row(C3:C)),9^9,C3:C)*(if(transpose(E3:E)=E3:E,1,9^9))*(if(transpose(D3:D)=D3:D,1,9^9)),
"select " & join(",","min(Col" & sequence(1, count(C3:C)) & ")") & "label "  & join(",","min(Col" & sequence(1, count(C3:C)) & ") ''") & "" )))

    enter image description here