So I have a fitness spreadsheet that tracks my estimated 1 rep max based on my recorded lifts. I want to conditional format the 1RM if it is greater than all calculated 1RM's above that cell. For this I need a dynamic range, as the range to check in the formula constantly increments by 1. Below is an example output:
1RM |
---|
90 |
89 |
91 |
85 |
100 |
I tried utilizing match to create a the dynamic range:
=if(J4=max($J$4:J&match(J4, J:J)), 1, 0)
This would be a helper column to determine if it is the max value (1), or not (0). The conditional formatting could then reference the helper column to determine if it needs to be formatted.
Here J is the column with the 1RM and J4 is the 1st entry. The max formula returns 0 while it should return the value of J4.
Desired output below where K is the column with the formula:
The following should work
=if(J4=max($J$2:$J4), 1, 0)