3

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

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

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.

  • 1
    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
1

Perhaps:

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

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.