# Identifying multiple "highs" and "lows"

#### 34sweetness

##### Board Regular
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### MrKowz

##### Well-known Member
Check out the MIN and MAX functions.

#### JackDanIce

##### Well-known Member
Try:
Code:
``=MAX(A1:A600)``
Code:
``=MIN(A1:A600)``

#### 34sweetness

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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.

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

#### JackDanIce

##### Well-known Member
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``````

#### 34sweetness

##### Board Regular
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

##### Well-known Member
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?

Replies
9
Views
399
Replies
4
Views
180
Replies
6
Views
294
Replies
2
Views
135
Replies
29
Views
659

1,190,615
Messages
5,981,946
Members
439,746
Latest member
VBANewbieJohn

### 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.

### Which adblocker are you using?

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

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