Home

Formula Challenge

%3CLINGO-SUB%20id%3D%22lingo-sub-327663%22%20slang%3D%22en-US%22%3EFormula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-327663%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20that%20finds%20multiple%20maximum%20values%20over%20a%20data%20set%20with%20~18%2C000%20cells.%20The%20data%20I%20have%20consists%20of%20120%20repetitions%20that%20start%20at%200%2C%20peak%20between%2025%20and%20150%2C%20and%20then%20return%20to%20zero.%20I%20need%20to%20find%20the%20maximum%20for%20each%20repetition%20so%20that%20my%2018%2C000%20cells%20are%20reduced%20to%20120.%20I%20can't%20figure%20out%20how%20to%20find%20a%20start%20the%20new%20%22Max%22%20range%20after%20the%20value%20returns%20to%20zero.%20Each%20rep%20is%20unique%20in%20its%20duration%2C%20so%20that%20I%20can't%20just%20perform%20the%20%22Max%22%20function%20every%20150%20cells.%20I%20am%20guessing%20that%20I%20need%20to%20create%20another%20column%20that%20indicates%20the%20repetition%20number%2C%20but%20I%20am%20stuck%20there%20as%20well%20other%20than%20manually%20typing%20a%20%221%22%20in%20each%20cell%20until%20the%20value%20reaches%20zero%20(indicating%20the%20end%20of%20the%20rep)%2C%20then%20typing%20a%20%222%22%2C%20and%20so%20forth%2C%20which%20would%20take%20way%20too%20much%20time.%20Any%20ideas%20would%20be%20helpful%2C%20I%20have%20attached%20the%20excel%20sheet%20to%20show%20you%20what%20I%20am%20working%20with.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-327663%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Tavioni
Occasional Visitor

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.