Identify Peaks and Troughs in a column in Excel sheet

[mailsmithah]

I am new to excel would like to know how to identify peaks and troughs in a column in excel sheet. Able to identify peaks and troughs if they are far apart but if continous values are peak or trough in a sequence it does not identify the peaks and troughs.

 

[mailsmithah]

Have referred this post Peaks and Troughs

But it does not solve the issue

 

[mailsmithah]

Have referred this post Peaks and Troughs

But it does not solve the issue

=IF(ISBLANK(Final!A3),"Y",IF(ISNUMBER(Final!$A2:Final!$A4),IF(OR(AND(NOT(ISBLANK(Final!A2)),NOT(ISBLANK(Final!A4)),ISNUMBER(Final!A2:Final!A4),Final!A3>Final!A2,Final!A3>Final!A4,Final!A3>Final!A5),AND(NOT(ISBLANK(Final!A2)),NOT(ISBLANK(Final!A4)),NOT(ISBLANK(Final!A5)),ISNUMBER(Final!A2:Final!A4),Final!A3<Final!A2,Final!A3>Final!A4,Final!A3<Final!A5),AND(NOT(ISBLANK(Final!A2)),NOT(ISBLANK(Final!A4)),ISNUMBER(Final!A2:Final!A4),Final!A3<Final!A2,Final!A3<Final!A1,Final!A3<Final!A4),AND(ISBLANK(Final!A2),NOT(ISBLANK(Final!A4)),ISNUMBER(Final!A2:Final!A4),Final!A3>Final!A2,Final!A3<Final!A4,Final!A3<Final!A1)),"Y","."),IF(OR(AND(Final!A3>Final!A2,Final!A3>Final!A4),AND(Final!A3<Final!A2,Final!A3<Final!A4)),IF(AND(Final!$A3=Final!$A2,Final!$A3=Final!$A4),IF(Final!A3=MAX((Final!$A$2:Final!$A$100000=Final!$A2),Final!A2:INDEX(Final!A2:Final!A100000,SUMPRODUCT(--EXACT(Final!$A3,Final!$A2:Final!$A$100000)))),".","Y"),"."),".")))

This is the formula i am using

 

[mailsmithah]

=IF(ISBLANK(Final!A3),"Y",IF(ISNUMBER(Final!$A2:Final!$A4),IF(OR(AND(NOT(ISBLANK(Final!A2)),NOT(ISBLANK(Final!A4)),ISNUMBER(Final!A2:Final!A4),Final!A3>Final!A2,Final!A3>Final!A4,Final!A3>Final!A5),AND(NOT(ISBLANK(Final!A2)),NOT(ISBLANK(Final!A4)),NOT(ISBLANK(Final!A5)),ISNUMBER(Final!A2:Final!A4),Final!A3<Final!A2,Final!A3>Final!A4,Final!A3<Final!A5),AND(NOT(ISBLANK(Final!A2)),NOT(ISBLANK(Final!A4)),ISNUMBER(Final!A2:Final!A4),Final!A3<Final!A2,Final!A3<Final!A1,Final!A3<Final!A4),AND(ISBLANK(Final!A2),NOT(ISBLANK(Final!A4)),ISNUMBER(Final!A2:Final!A4),Final!A3>Final!A2,Final!A3<Final!A4,Final!A3<Final!A1)),"Y","."),IF(OR(AND(Final!A3>Final!A2,Final!A3>Final!A4),AND(Final!A3<Final!A2,Final!A3<Final!A4)),IF(AND(Final!$A3=Final!$A2,Final!$A3=Final!$A4),IF(Final!A3=MAX((Final!$A$2:Final!$A$100000=Final!$A2),Final!A2:INDEX(Final!A2:Final!A100000,SUMPRODUCT(--EXACT(Final!$A3,Final!$A2:Final!$A$100000)))),".","Y"),"."),".")))

This is the formula i am using

This is the Excel data

 

[petertenthije]

I've got something, but it does require four work columns.

Column A: =your source data
Column B: =if(A2>A1,"grow",if(A2=A1,"steady","decline"))
Column C: =if(B2="steady",C1,B2)
Column D: =if(C2=C3,"",if(C2="grow","high","low"))
Column E: =if(b3="steady",D3,D2)

 

[mailsmithah]

I've got something, but it does require four work columns.

Column A: =your source data
Column B: =if(A2>A1,"grow",if(A2=A1,"steady","decline"))
Column C: =if(B2="steady",C1,B2)
Column D: =if(C2=C3,"",if(C2="grow","high","low"))
Column E: =if(b3="steady",D3,D2)

I am applying the formula in the second sheet in the same excel, so it shows "Y" for peaks and troughs and a "." If its a normal value.

 

[mailsmithah]

I am applying the formula in the second sheet in the same excel, so it shows "Y" for peaks and troughs and a "." If its a normal value.

The issue arises when there is continous peak or trough values like 4000,6000 and 8000 in the column A, it does not mark all these cells as "Y" in the second sheet.

 

[mailsmithah]

The issue arises when there is continous peak or trough values like 4000,6000 and 8000 in the column A, it does not mark all these cells as "Y" in the second sheet.

Would like to know if it all its achievable with Excel Formula because maximum 3 adjacent column values can be compared within an AND or OR,. After which the formula just grows in length.

 

[petertenthije]

I've made one change to the formule in column E (highlighted red).
Otherwise it seems to work just fine for mw.

Column A: =your source data
Column B: =if(A2>A1,"grow",if(A2=A1,"steady","decline"))
Column C: =if(B2="steady",C1,B2)
Column D: =if(C2=C3,"",if(C2="grow","high","low"))
Column E: =if(b3="steady",E3,D2)

 

[mailsmithah]

I've made one change to the formule in column E (highlighted red).
Otherwise it seems to work just fine for mw.

Column A: =your source data
Column B: =if(A2>A1,"grow",if(A2=A1,"steady","decline"))
Column C: =if(B2="steady",C1,B2)
Column D: =if(C2=C3,"",if(C2="grow","high","low"))
Column E: =if(b3="steady",E3,D2)

Can't these be clubbed to a single formula applied to one cell, in second sheet... Will try that