Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Local max and min in Excell

Local max and min in Excell

Local max and min in Excell


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.


RE: Local max and min in Excell


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


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.


RE: Local max and min in Excell

Great idea!

RE: Local max and min in Excell


Thanks for all your help.

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.


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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close