Google-sheets – Finding PEAK (not just max) values through a range

google sheets

I'm building an exercise tracker with DATE, EXERCISE, VALUE column. I'm racking my brain to come up with a formula that could populate a new column with the last maximum value of each exercise according to date. i.e.

image

So in this case, my performance history for exercise A is:

1,10,100,10,1000,10,100

Last maxes should be

1,10,100,100,1000,1000,1000

So far, I've managed to sort the VALUE by last largest (though cells that are not MAX are "", instead of filling with last MAX value) with

=if(COUNTIF(MAX($C1:C$2),C1),C1,"")

but can't figure out how to discriminate and track last largest of individual exercise. I can also

Best Answer

Assuming your data is in columns A-C, starting at row 2, this formula, when copy/pasted into the group max column, will give you want you want:

=max(filter(filter($A$2:$Cx, $B$2:$Bx=Bx), {false, false, true}))

where x = current row.

Working example

Explanation:

  • The innermost filter() takes into account only all rows until now from the current row's group
  • the outermost filter() limits the result to the 3rd column
  • max() takes the max of that column.

A challenge: can this answer be improved to a single ARRAYFORMULA that would not require copying/pasting? I was not able to do it.

Related Topic