Identifying multiple "highs" and "lows"

34sweetness

Board Regular
Joined
Oct 7, 2002
Messages
136
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Check out the MIN and MAX functions.
 
Upvote 0
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.
 
Upvote 0
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"?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Try running this macro (assuming the list is in column A, starts in cell A1 and column B is empty):
Code:
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top