# Finding PEAK (not just max) values through a range?

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. 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

• Providing a sample sheet can be helpful to those answering the question. – jonsca Apr 24 '17 at 2:14

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. 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.

• Tried in various configurations e.g. this: =ArrayFormula(max(if(B\$2:B13=B2:B13,C\$2:C13))) but it does not expand to the whole range. – ttarchala Apr 24 '17 at 9:20

Perhaps:

``````=ArrayFormula(max(if(K\$2:K2=K2,L\$2:L2)))
``````