Forum Discussion
Formula Challenge
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):
