×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Local max and min in Excell2

## Local max and min in Excell

(OP)
Folks,

I have a set of values with repetive peaks and troughs. Is it possible to idenfity the local maxs and mins, either on the graph or in the table of values.

There are literally thousands of values, so doing it manually would take some time.

Cheers,
Speedy

### RE: Local max and min in Excell

Speedy,

Within excel there are built in functions, which are predefined formulas that perform calculations by using specific values.  The functions you will need to use on the table of values are as follows:

The function MAX(number1,number2,...), returns the largest value in a set of values.  If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then: MAX(A1:A5) equals 27.

The function MIN(number1,number2,...), returns the smallest number in a set of values.  If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then: MIN(A1:A5) equals 2.

Assuming that all of the sets of values are set up the same way, you can select the range and copy the formula to all of your sets of values.

### RE: Local max and min in Excell

Speedy,

If I understand your problem correctly you have a number of max and min points you need to identify. If your data is in order you could use conditional formatting,

example if your data is in column B.
In cell B2, select format >>> conditional formatting and set cell value is .. not between ... =b1 ... =b3   (make sure there are no \$ signs in the cell address) then select format and change to text colour or shading.

Then using paste special to extend this conditional formatting to the rest of the range.

This will change the format of the max and min but will not differentiate between them.

### RE: Local max and min in Excell

I think you need to identify the inflection points.  Try something like this:

A        B
1   1
2   2
3   3
4   4
5   5       peak
6   4
7   3
8   2
9   1       trough
10  2

In cell B2, enter the following formula, then copy it down:

=IF(AND(A2>A1, A2>A3),"peak",IF(AND(A2<A1, A2<A3),"trough",""))

### RE: Local max and min in Excell

Generally, there is no simple algorithm for doing this, since it's so highly dependent on the frequency of the min's and max's.  Also, any algorithm will be subject to the noise content of your signal.  If the frequency of the noise is comparable to your signal, then you need to decide how much to filter out and the allowable degradation to your signal.

TTFN

ab123456,
Great idea!

### RE: Local max and min in Excell

(OP)
Guys,

I think MintJulep's suggestion will work the best as the curve generally rises over time also, i.e. the final min becomes greater than the original max.

Cheers,
Speedy

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!