Identifying PEAKS and TROUGHS in a data series

[eggsell]

Hi
Using: Excel XP, Windows XP

Is there a way to identify peaks and troughs in a data series? Say, I have these values in a column: 8, 7, 6, 7, 8, 7, 6, 5, 4, 3, 4, 5, 6, 7, 8, 9, and 8
The objective is to identify 6 and 3 as troughs and 8 and 9 as peaks. I have been unable to solve this problem using SMALL/LARGE, as they only identify the kth smallest/largest numbers and not the troughs/peaks.

In a nutshell, I would like to devise a formula that would allow me to identify (x) number of lowest troughs and (x) number of highest peaks in a dynamic range. Please note that numbers that are lower than the second trough or higher than second peak but themselves are not troughs or peaks (i.e. numbers in the immediate vicinity of the lowest trough or highest peak) need to be ignored. In other words, a change of direction is required in order for a number to be considered as a peak or trough.

So far it seems that this is beyond my abilities; while I still work on it, I would be grateful if someone could point me in the right direction.

 

[PaddyD]

quick q - once they're identified, what do you want done?

 

[eggsell]

Hi Paddy

I need to use those in these formulae: 1) "= ceiling(peak_value, 1) - floor(2nd peak_value, 1)"; 2) "= if(peak_value > x AND 2nd peak_value < x, "Notify QC")"; 3) "= if(peak_value > x AND 2nd peak_value > x, "Shot Down the Machine")"
I have not refined the formulae yet, as I've been stuck in the first part, but this is the general idea.

Thanks

 

[PaddyD]

identifying the peaks & troughs is fairly straighforward...just watch out for changes in the slope of the line implied by the data:

Book1
	A	B	C	D
1	1	8
2	2	7	-1.0000
3	3	6	-1.0000	Trough
4	4	11	5.0000	Peak
5	5	8	-3.0000
6	6	7	-1.0000
7	7	6	-1.0000
8	8	5	-1.0000
9	9	2	-3.0000	Trough
10	10	3	1.0000
11	11	4	1.0000
12	12	5	1.0000
13	13	6	1.0000
14	14	10	4.0000	Peak
15	15	8	-2.0000	Trough
16	16	9	1.0000	Peak
17	17	8	-1.0000	Trough
Sheet3

...haven't yet given any thought to making it more elegant, nor to the conditions detailed in your second post.

 

[SydneyGeek]

I did it by adding another column to my dataset.
Let's assume the data runs down from A2. I put this formula in B3 and filled down:

=IF(AND(A2<A3,A4<A3),1,IF(AND(A2>A3,A4>A3),-1,0))

All your peaks are 1, troughs are -1. It should be relatively simple to tag them or use another IF function to complete the analysis.

Hope that helps.


EDIT: Geek - I edited your post so the formula would display - the board interprets less than signs as html tags if they're not flagged as code.[/code]

 

[eggsell]

Paddy and Geek

Thanks, you solved the first part of my problem which was identifying the peaks and troughs (I had a suspicion that this must be the same as the Maxima and Minima problems in Calculus, but I was unable to formulate it properly).

I am still at a loss as to how to implement this in a dynamic range. If I wanted to calculate "= ceiling(peak_value within the last 30 numbers, 1) - floor(2nd peak_value within the last 30 numbers, 1)", how should I proceed?

Thanks again

 

[PaddyD]

I have not done addressed the dynamic range issue, but the following is the sort of thing you want (or one way of getting it):

Book1
	A	B	C	D	E
1	1	8
2	2	7	-1.0000
3	3	6	-1.0000	Trough
4	4	11	5.0000	Peak
5	5	8	-3.0000
6	6	7	-1.0000
7	7	6	-1.0000
8	8	5	-1.0000
9	9	2	-3.0000	Trough
10	10	3	1.0000
11	11	4	1.0000
12	12	5	1.0000
13	13	6	1.0000
14	14	10	4.0000	Peak
15	15	8	-2.0000	Trough
16	16	9	1.0000	Peak
17	17	8	-1.0000	Trough
18	18	9	1.0000	Peak
19	19	4	-5.0000	Trough
20	20	6	2.0000
21	21	7	1.0000
22	22	8	1.0000	Peak
23	23	2	-6.0000	Trough
24	24	2	0.0000	Trough
25	25	10	8.0000	Peak
26	26	3	-7.0000
27	27	1	-2.0000	Trough
28	28	7	6.0000	Peak
29	29	4	-3.0000	Trough
30				Result:	-2
Sheet3

the formula in e30 is:

=CEILING(INDEX(B20:B29,MATCH("Peak",D20:D29,0)),1)-FLOOR(INDEX(B20:B29,SMALL(IF(D20:D29="Peak",ROW(B20:B29)-ROW(B20)+1,ROW(B29)+1),2)),1)

which needs to be array entered using control + shift + enter, not just enter. For info on what the second index() is doing, see Chip's abitrary lookup example:

http://www.cpearson.com/excel/lookups.htm#ClosestMatch

 

[eggsell]

Paddy

Thank you so much, I will work on it to adapt to my needs.

Take Care

 

[summerguy]

Hi - I am applying the above formula to find peaks, troughs and recovery but not able to get exactly what i want. I have also been through the other formulas on the boards and adapted them. I am still unable to come up with a single cell formula in column D to give me the peaks, troughs and recovery as per the below. I appreciate any help or ideas on how to solve this.

<html><head><title>Excel Jeanie HTML</title></head><body><b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:68px;" /><col style="width:95px;" /><col style="width:75px;" /><col style="width:152px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; text-align:center; ">Date</td><td style="font-weight:bold; text-align:center; ">Value</td><td style="font-weight:bold; text-align:center; ">DrawDown</td><td style="background-color:#c0c0c0; font-weight:bold; text-align:center; ">Peak/Trough/Recovery</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">Jan-08</td><td style="text-align:right; ">100.00</td><td style="text-align:right; ">0.00%</td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">Feb-08</td><td style="text-align:right; ">102.00</td><td style="text-align:right; ">0.00%</td><td style="background-color:#d8d8d8; "> Peak </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">Mar-08</td><td style="text-align:right; ">95.00</td><td style="color:#800080; text-align:right; ">-6.86%</td><td style="background-color:#d8d8d8; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">Apr-08</td><td style="text-align:right; ">90.00</td><td style="color:#800080; text-align:right; ">-11.76%</td><td style="background-color:#c0c0c0; "> Trough </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">May-08</td><td style="text-align:right; ">98.00</td><td style="color:#800080; text-align:right; ">-3.92%</td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">Jun-08</td><td style="text-align:right; ">99.00</td><td style="color:#800080; text-align:right; ">-2.94%</td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">Jul-08</td><td style="text-align:right; ">105.00</td><td style="text-align:right; ">0.00%</td><td style="background-color:#c0c0c0; "> Recovery </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">Aug-08</td><td style="text-align:right; ">107.00</td><td style="text-align:right; ">0.00%</td><td style="background-color:#c0c0c0; "> Peak </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">Sep-08</td><td style="text-align:right; ">103.00</td><td style="color:#800080; text-align:right; ">-3.74%</td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">Oct-08</td><td style="text-align:right; ">102.00</td><td style="color:#800080; text-align:right; ">-4.67%</td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">Nov-08</td><td style="text-align:right; ">100.00</td><td style="color:#800080; text-align:right; ">-6.54%</td><td style="background-color:#c0c0c0; "> Trough </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">Dec-08</td><td style="text-align:right; ">101.00</td><td style="color:#800080; text-align:right; ">-5.61%</td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; text-align:right; ">Jan-09</td><td style="text-align:right; ">108.00</td><td style="text-align:right; ">0.00%</td><td style="background-color:#c0c0c0; "> Recovery </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">Feb-09</td><td style="text-align:right; ">             110.00 </td><td style="text-align:right; ">0.00%</td><td style="background-color:#c0c0c0; "> Peak </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">Mar-09</td><td style="text-align:right; ">             106.00 </td><td style="color:#800080; text-align:right; ">-3.64%</td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:right; ">Apr-09</td><td style="text-align:right; ">             100.00 </td><td style="color:#800080; text-align:right; ">-9.09%</td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; text-align:right; ">May-09</td><td style="text-align:right; ">               90.00 </td><td style="color:#800080; text-align:right; ">-18.18%</td><td style="background-color:#c0c0c0; "> Trough </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; text-align:right; ">Jun-09</td><td style="text-align:right; ">             110.00 </td><td style="text-align:right; ">0.00%</td><td style="background-color:#c0c0c0; "> Recovery </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; text-align:right; ">Jul-09</td><td style="text-align:right; ">             115.00 </td><td style="text-align:right; ">0.00%</td><td style="background-color:#c0c0c0; "> Peak </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>

<b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=(B2-MAX<span style=' color:008000; '>(B2:$B$2)</span>)/MAX(B2:$B$2)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000;

 

[PaddyD]

so is the rule something like "peak and trough have their usual meanings; 'recovery' is first instance where drawdown = 0% after a trough"

(and if so is that exactly 0% or 0% or greater?)