Formula Challenge

Copper Contributor

Hello,

 

I am trying to create a formula that finds multiple maximum values over a data set with ~18,000 cells. The data I have consists of 120 repetitions that start at 0, peak between 25 and 150, and then return to zero. I need to find the maximum for each repetition so that my 18,000 cells are reduced to 120. I can't figure out how to find a start the new "Max" range after the value returns to zero. Each rep is unique in its duration, so that I can't just perform the "Max" function every 150 cells. I am guessing that I need to create another column that indicates the repetition number, but I am stuck there as well other than manually typing a "1" in each cell until the value reaches zero (indicating the end of the rep), then typing a "2", and so forth, which would take way too much time. Any ideas would be helpful, I have attached the excel sheet to show you what I am working with. 

0 Replies