Identifying multiple "highs" and "lows"

[34sweetness]

I have columns of data with values that increase and decrease.
I want to identify each "low" value and each "high" value.
I have about 600 rows of data that look like this:

-33.35
-31.68
-30.12
-29.25
-32.91
-38.92
-45.94

and other rows that look like this:


-112.62
-116.39
-118.96
-122.26
-125.68
-119.77
-115.13
-110.42

The data continues to go "up" and "down". I want the "lowest" values and the "highest" values throughout the column. So in the example above, I want -29.25 and -125.68.

I have several columns of data each with 600+ rows, and I don't want to do this by visual inspection.

Any ideas?

Thanks.

Kevin

 

[michaelg2708]

If the two columns of data are in A and B, put the following formula in C3 and fill down:

=IF(OR(A3-MAX(IF(LEN($C$2:C2)>0,$A$2:A2,0))>160,MAX(IF(LEN($C$2:C2)>0,$A$2:A2,0))=0),IF(AND(B3>B2,B3>B4),"H",IF(AND(B2>B3,B4>B3),"L","")),"")


this is an array formula so enter with CTRL+SHIFT+ENTER.

If there is a large number of rows it might get slow.

I know this post is seven years old but I stumbled upon it and think it would help me with what I need to do. I'm wondering could someone explain what this formula is actually doing. I know it is looking for peaks and troughs and I know the part where the next point has to be greater than 160 but what is the relevance of the other parts
Thanks in advance

 

[vaskov17]

What do you need to do?

 

[michaelg2708]

thanks for your reply
i need to do the same find the peaks and troughs. the difference is my time in the first column counts down to 0 rather than up.

The "time" is in the first column (recorded in seconds). The thing is, the peaks and troughs do not always spaced aprart by exactly 30 seconds.

I only want the "highest peak" and the "lowest trough" that are spaced about 30 seconds apart, beginning with either the first "highest peak" or the first "lowest trough" depending upon which one comes "first" in the column data.

487-	0.739101
486-	0.77867
486-	0.818423
485-	0.858425
482	-0.89838
480	-0.937342
480	-0.980685
479	-1.01348
478	-1.051844
477-	1.038134
477-	1.018213
476-	1.000168
475-	0.979275
474-	0.898555
473-	0.839383
472-	0.786423
471-	0.745179
471-	0.704203
469-	0.666846
469-	0.65747
468-	0.64864
467-	0.687093
467-	0.702326
466-	0.715232
465-	0.734745
465-	0.760431
463-	0.790142
463-	0.808285
462-	0.808316
461-	0.801661
459-	0.795041
459-	0.770412
458-	0.747683
457-	0.72316
457-	0.698318
456-	0.662828
455-	0.628274
454-	0.597262
452-	0.566451
451-	0.533797
450-	0.501676
450-	0.474234
449-	0.446282
448-	0.394796
447-	0.398476
446-	0.388334
445-	0.372847
444-	0.365559
443-	0.358523
442-	0.351825
441-	0.328258
441-	0.302912
440-	0.274198
439-	0.245197
439-	0.239142
438-	0.224443
437-	0.202943
437-	0.177078
436-	0.172812
435-	0.207643
434-	0.240401
434-	0.272385

<tbody>
</tbody>

i have highlighted the peaks and troughs that i want. even though it is going up and down between these 2 points i want to ignore the in between points
thanks again