Forum Discussion
Formula Challenge
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.
Somehow your challenge was lost in the shuffle 5+ years ago! I think you may not have received a response because there's some uncertainty with what constitutes 1 repetition. It's understood a given repetition begins at zero, peaks at between 25 to 150, returns to 0, and then the next repetition begins. There are quite a few instances where zero repeats more than 1 time between repetitions.
I approached this challenge presuming consecutive 0s should be treated as (1) instance of 0. Additionally, Max aggregations less than 25 are discarded.
I realize Tavioni may no longer frequent this forum but I will offer a solution. It's a good opportunity to use GROUPBY.
=LET( rep_arr, SCAN(0, torque, LAMBDA(acc, v, IF(v = 0, acc + 1, acc))), no_zero, torque <> 0, agg, GROUPBY(rep_arr, torque, MAX, , , , no_zero), agg_torque, TAKE(agg, , -1), adjusted, FILTER(agg_torque, agg_torque >= 25), reps, SEQUENCE(ROWS(adjusted)), HSTACK(reps, adjusted) )Patrick2788 even though this isn't one of your challenges it sort of is. I don't have those fancy new functions so I just used a SCAN and FILTER with a twist:
=LET(peaks,SCAN(0,VSTACK(torque,0),LAMBDA(p,q,IF(q<=0,IF(p>=25,-p,0),IF(q>p,q,p)))), FILTER(-peaks,peaks<0))The inner SCAN does a basic max function by choosing the new value if it is > the old value with a slight twist being if the new value is <=0 and the previous value was a peak (>25) then it assigns the output as -p (negative of the held max).
The filter then just filters for those 'negative max' values and negates them
My results are a bit different than yours because there are cases where the values jump negative and then back positive. Possibly a noise filter on the data is needed to filter that out.
Alternatively if you replace the IF(q<=0 part with IF(q=0 then my results are nearly identical with yours with the only exception being your output has 1 more peak at the end that my formula (and my eyes) doesn't find. But again this version works because it relies on those 0s and if the data was more sinusoidal and could 'jump' over the 0 crossings to negative values the above formula with <=0 would be more appropriate.
I like these solutions because they're efficient and easy to read. I like how you avoided filtering twice and avoided using REDUCE which would've crippled the calculation speed.
The visuals for the two formulas are similar:
Also, a good opportunity to use a Radar chart (Based on 2nd formula):
