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.

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

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