Local max and min in Excell 2

[Speedy]

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

 

[1HVACEngineer]

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.

 

[ab123456]

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.

 

[MintJulep]

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",""))

 

[IRstuff]

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

 

[yakpol]

ab123456,
Great idea!

 

[Speedy]

Guys,

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.

Cheers,
Speedy