Identifying both cell references & values in peaks & troughs of a multi-wave data set

[robem10]

I have a data set which is a recording of vertical ground reaction force during multiple hops. 30s long trial, 1000 Hz = 30,000 samples.

For each wave, I need to identify:

• cell reference where value increases above a set threshold (e.g. >0.05 - marking the onset of the wave)
• peak value of each wave (even if there is a double peak, I need the largest value)
• cell where that peak value occurs
• cell reference where value drops below a set threshold (e.g. <0.05 – marking the trough of that wave)
• subsequent cell reference where value increases above a threshold after (e.g. >0.05 - marking the onset of the next wave)

Additional things:
All data sets will have a consistent 30,000 data points… however, the number of waves within that will fluctuate (so far there has been as little as 30ish, and up to 68 waves), and the magnitude of each wave will differ each time. If the number of waves changes for each data set, is there a way of counting the number of waves within the 30,000 samples and displaying that? My initial thoughts are have it set up for 80 waves (for example) so that all waves will be included when finding the above.

Below is an example force trace that I've made up, but it follows the same pattern that we would see for the first 4 jumps during a 30 second trial (1000Hz). When the jumper is in flight, the force never drops to exactly 0 due to noise in the system, so that's why I've put in the small positive and negative numbers around 0, so that it is truly representative of what we see in our normal files.

I hope that was clear enough, and I really appreciate any help that anyone can give!

[TABLE="width: 65"]
<!--StartFragment--> <colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65, align: right"]0.9[/TD]
[/TR]
[TR]
[TD="align: right"]0.85[/TD]
[/TR]
[TR]
[TD="align: right"]0.9[/TD]
[/TR]
[TR]
[TD="align: right"]0.9[/TD]
[/TR]
[TR]
[TD="align: right"]0.7[/TD]
[/TR]
[TR]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]0.8[/TD]
[/TR]
[TR]
[TD="align: right"]1.1[/TD]
[/TR]
[TR]
[TD="align: right"]1.6[/TD]
[/TR]
[TR]
[TD="align: right"]1.8[/TD]
[/TR]
[TR]
[TD="align: right"]1.9[/TD]
[/TR]
[TR]
[TD="align: right"]1.7[/TD]
[/TR]
[TR]
[TD="align: right"]1.3[/TD]
[/TR]
[TR]
[TD="align: right"]0.7[/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]0.04[/TD]
[/TR]
[TR]
[TD="align: right"]0.02[/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]0.03[/TD]
[/TR]
[TR]
[TD="align: right"]-0.01[/TD]
[/TR]
[TR]
[TD="align: right"]0.02[/TD]
[/TR]
[TR]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD="align: right"]0.7[/TD]
[/TR]
[TR]
[TD="align: right"]1.3[/TD]
[/TR]
[TR]
[TD="align: right"]1.9[/TD]
[/TR]
[TR]
[TD="align: right"]1.6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1.8[/TD]
[/TR]
[TR]
[TD="align: right"]1.4[/TD]
[/TR]
[TR]
[TD="align: right"]0.6[/TD]
[/TR]
[TR]
[TD="align: right"]-0.02[/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]0.03[/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]-0.01[/TD]
[/TR]
[TR]
[TD="align: right"]-0.02[/TD]
[/TR]
[TR]
[TD="align: right"]0.02[/TD]
[/TR]
[TR]
[TD="align: right"]0.8[/TD]
[/TR]
[TR]
[TD="align: right"]1.3[/TD]
[/TR]
[TR]
[TD="align: right"]1.8[/TD]
[/TR]
[TR]
[TD="align: right"]1.3[/TD]
[/TR]
[TR]
[TD="align: right"]0.6[/TD]
[/TR]
[TR]
[TD="align: right"]-0.02[/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]-0.03[/TD]
[/TR]
[TR]
[TD="align: right"]0.02[/TD]
[/TR]
[TR]
[TD="align: right"]0.8[/TD]
[/TR]
[TR]
[TD="align: right"]1.7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1.4[/TD]
[/TR]
[TR]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]-0.02[/TD]
[/TR]
[TR]
[TD="align: right"]0.04[/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]-0.02[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[konew1]

Here is my take on it, with helper column and "onset" seeded in B3

Excel Workbook
	A	B	C	D
2				peak
3	0.9	onset	1
4	0.85
5	0.9
6	0.9
7	0.7
8	0.5
9	0.8
10	1.1
11	1.6
12	1.8
13	1.9
14	1.7
15	1.3
16	0.7
17	0.01	trough	1	1.9
18	0.04
19	0.02
20	0.01
21	0.03
22	-0.01
23	0.02
24	0.3	onset	2
25	0.7
26	1.3
27	1.9
28	1.6
29	2
30	1.8
31	1.4
32	0.6
33	-0.02	trough	2	2
34	0.01
35	0.03
36	0.01
37	-0.01
38	-0.02
39	0.02
40	0.8	onset	3
41	1.3
42	1.8
43	1.3
44	0.6
45	-0.02	trough	3	1.8
46	0.01
47	0.01
48	-0.03
49	0.02
50	0.8	onset	4
51	1.7
52	2
53	1.4
54	0.5
55	-0.02	trough	4	2
56	0.04
57	0.01
58	-0.02
Sheet3

 

[robem10]

@konew1: I do apologise that my reply has been so late - but with your help I managed to tweak it to get it to work with my data set. Massive help, has saved me hours and hours of time per week! Many thanks for your help again, it was and is much appreciated!!

 

[konew1]

Thanks for the feedback. HTH