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

 

[MrKowz]

Check out the MIN and MAX functions.

 

[JackDanIce]

Try:

=MAX(A1:A600)

=MIN(A1:A600)

 

[34sweetness]

MIN and MAX functions only give me two values, the "most" minimum and the "most" maximum.
I need to find all of the "low" values in the trend, and all of the "high" values in the trend for the entire column. This is multiple values.

 

[MrKowz]

MIN and MAX functions only give me two values, the "most" minimum and the "most" maximum.
I need to find all of the "low" values in the trend, and all of the "high" values in the trend for the entire column. This is multiple values.

Can you please provide a bit more data and your desired outcome? What would determine what is a "low" and what is a "high"?

 

[34sweetness]

Here is the first 100 rows of one of the columns of data:

-14.87
-2.13
-3.39
-5.46
-8.40
-23.30
-63.03
-51.39
-47.26
-49.50
-61.58
-67.06
-70.12
-73.65
-77.20
-81.22
-83.84
-87.08
-90.81
-94.69
-97.04
-101.56
-104.46
-108.05
-111.12
-114.93
-118.53
-120.24
-124.98
-128.60
-121.30
-115.89
-110.90
-111.09
-109.95
-107.64
-105.78
-102.85
-100.00
-96.08
-93.43
-89.61
-86.44
-82.43
-80.02
-75.53
-73.07
-69.74
-66.19
-62.38
-59.28
-56.22
-53.35
-49.72
-46.12
-42.57
-39.48
-36.00
-31.99
-25.72
-6.53
-2.03
-3.67
-5.82
-9.39
-32.86
-62.55
-51.00
-46.80
-49.20
-60.83
-67.60
-70.99
-74.19
-76.79
-80.38
-84.48
-88.55
-91.01
-94.47
-96.99
-101.56
-104.49
-107.49
-111.02
-114.85
-118.39
-120.46
-125.03
-128.48
-121.46
-116.09
-111.00
-110.90
-109.78
-109.11
-104.83
-102.93
-99.53
-96.68

As you can see...the data is begins to trend up to "-2.13" (I want that value), then begins to trend downward until "-128.60" (I want that value), then begins to go back up...and so forth for several hundred data points. I want all of the "peaks" whether they be "low" or High", but only the "peaks".

Does that help?

 

[MrKowz]

It does help me identify what you are looking for, however, in looking at the data, I'm seeing other peaks/troughs between the -2.13 and the -128.60.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">-14.87</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">-2.13</td><td style=";">Trough</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">-3.39</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">-5.46</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">-8.4</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">-23.3</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">-63.03</td><td style=";">Peak</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">-51.39</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">-47.26</td><td style=";">Trough</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">-49.5</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">-61.58</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">-67.06</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">-70.12</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">-73.65</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">-77.2</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">-81.22</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">-83.84</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">-87.08</td><td style=";"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet3</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ABS(<font color="Green">A2</font>)<ABS(<font color="Green">A1</font>),ABS(<font color="Green">A2</font>)<ABS(<font color="Green">A3</font>)</font>),"Trough",IF(<font color="Red">AND(<font color="Green">ABS(<font color="Purple">A2</font>)>ABS(<font color="Purple">A1</font>),ABS(<font color="Purple">A2</font>)>ABS(<font color="Purple">A3</font>)</font>),"Peak",""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Assuming your data is in At, insert the above formula into B2 and copy it down.

 

[JackDanIce]

Try running this macro (assuming the list is in column A, starts in cell A1 and column B is empty):

Sub IdentifyPeaksandTroughs()
Application.ScreenUpdating = False
Dim i As Long
Dim Change As Boolean
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Change Then
  If Range("A" & i) > Range("A" & i + 1) Then
    Range("B" & i) = "High"
    Change = False
  End If
Else
  If Range("A" & i) < Range("A" & i + 1) Then
    Range("B" & i) = "Low"
    Change = True
  End If
End If
Next i
Application.ScreenUpdating = True
End Sub

 

[34sweetness]

Yep, MrKowz, you are right.

Thanks for pointing that out...there is also a "time" factor to this that I had not put into my original question. Thanks for reminding me of that.

There should be about "3 minutes" of data between the "highest peak" and the "lowest trough". If there are peaks and troughs within that time interval, I want to "ignore" them.

The "time" is in the first column (recorded in seconds). The thing is, the peaks and toughs do not always spaced aprart by exactly 180 seconds. That's why I said "about"...

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

Can this be done?

 

[MrKowz]

Can you please provide the extra data (the time intervals that you have)? I'll see what I can work out.

Also, what rule do you want to use for "about", because Excel doesn't know "about". Do you want it to be the first peak/trough after 180 seconds? The first peak/trough between 170-190 seconds? The first peak/trough that is greater than 170 seconds?