Identifying peaks and troughs by analysing four columns.

[peterinaudo]

Hi, I am fairly new to Excel so would really appreciate some help with this problem.
I am using Excel 2007 running on Vista.

I have been trying to come up with a formula to determine peaks and troughs according to four conditions listed below.

Here is a samlpe of a data set-

Excel 2007

	A	B	C	D	E
Date	High	Low	Type	Peak/Trough

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]31/08/2009
[/TD]
[TD="align: right"]62.56
[/TD]
[TD="align: right"]61.92
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]1/09/2009
[/TD]
[TD="align: right"]62.2
[/TD]
[TD="align: right"]60.6
[/TD]
[TD="align: center"]DOWN
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]2/09/2009
[/TD]
[TD="align: right"]61.29
[/TD]
[TD="align: right"]60.07
[/TD]
[TD="align: center"]DOWN
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]3/09/2009
[/TD]
[TD="align: right"]62.46
[/TD]
[TD="align: right"]61.08
[/TD]
[TD="align: center"]UP
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]4/09/2009
[/TD]
[TD="align: right"]62.62
[/TD]
[TD="align: right"]61.4
[/TD]
[TD="align: center"]UP
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]8/09/2009
[/TD]
[TD="align: right"]65.74
[/TD]
[TD="align: right"]64.75
[/TD]
[TD="align: center"]UP
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]9/09/2009
[/TD]
[TD="align: right"]65.4
[/TD]
[TD="align: right"]64.09
[/TD]
[TD="align: center"]DOWN
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]10/09/2009
[/TD]
[TD="align: right"]66.24
[/TD]
[TD="align: right"]64.3
[/TD]
[TD="align: center"]UP
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]11/09/2009
[/TD]
[TD="align: right"]67.83
[/TD]
[TD="align: right"]66.32
[/TD]
[TD="align: center"]UP
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"]14/09/2009
[/TD]
[TD="align: right"]66.25
[/TD]
[TD="align: right"]65.16
[/TD]
[TD="align: center"]DOWN
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"]15/09/2009
[/TD]
[TD="align: right"]67.1
[/TD]
[TD="align: right"]65.53
[/TD]
[TD="align: center"]UP
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"]16/09/2009
[/TD]
[TD="align: right"]69.46
[/TD]
[TD="align: right"]65.4
[/TD]
[TD="align: center"]OUTSIDE
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"]17/09/2009
[/TD]
[TD="align: right"]69.22
[/TD]
[TD="align: right"]67.8
[/TD]
[TD="align: center"]INSIDE
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"]18/09/2009
[/TD]
[TD="align: right"]69.3
[/TD]
[TD="align: right"]66.8
[/TD]
[TD="align: center"]OUTSIDE
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"]21/09/2009
[/TD]
[TD="align: right"]69.4
[/TD]
[TD="align: right"]63.85
[/TD]
[TD="align: center"]OUTSIDE
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"]22/09/2009
[/TD]
[TD="align: right"]67.39
[/TD]
[TD="align: right"]66.4
[/TD]
[TD="align: center"]INSIDE
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"]23/09/2009
[/TD]
[TD="align: right"]67.27
[/TD]
[TD="align: right"]65.55
[/TD]
[TD="align: center"]DOWN
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"]24/09/2009
[/TD]
[TD="align: right"]67.3
[/TD]
[TD="align: right"]63.5
[/TD]
[TD="align: center"]OUTSIDE
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"]25/09/2009
[/TD]
[TD="align: right"]67.35
[/TD]
[TD="align: right"]63.4
[/TD]
[TD="align: center"]OUTSIDE
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"]28/09/2009
[/TD]
[TD="align: right"]66.1
[/TD]
[TD="align: right"]64.24
[/TD]
[TD="align: center"]INSIDE
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22
[/TD]
[TD="align: right"]29/09/2009
[/TD]
[TD="align: right"]66.2
[/TD]
[TD="align: right"]64.2
[/TD]
[TD="align: center"]OUTSIDE
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23
[/TD]
[TD="align: right"]30/09/2009
[/TD]
[TD="align: right"]66.57
[/TD]
[TD="align: right"]64.7
[/TD]
[TD="align: center"]UP
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24
[/TD]
[TD="align: right"]1/10/2009
[/TD]
[TD="align: right"]64.92
[/TD]
[TD="align: right"]62.76
[/TD]
[TD="align: center"]DOWN
[/TD]
[TD="align: right"][/TD]

</tbody>

Limits1​

The four conditions for determining the Peaks and Troughs are.


Condition 1

If the Type in the current row is "INSIDE" then return a Blank cell.
​

Condition 2
If the Type in the current row is "DOWN" then search up through the Peak/Trough column for the last entry above.

If the last entry above is "Trough" then return a Blank cell.
If all of the cells above are Blank (i.e. near the top of the column) then look at the range from the top of the Type column down to the row of the next "UP"​

Compare the lows column values in that range of cells.​

If the Low of the current row has the lowest value of that range then return "Trough"
If the value of the Low in the current row is not the lowest in that range then return a Blank cell.​

If the last entry above is "Peak" then look at the range from that row down to the row of the next "UP" in the Type column.​

Compare the Low column values in that range of cells.​

If the Low of the current row has the lowest value of that range then return "Trough"
If the value of the Low in the current row is not the lowest in that range then return a Blank cell.
​

Condition 3
IF the Type in the current row is "UP" then search up through the Peak/Trough column for the last entry above.

IF the last entry above is "Peak" then return a Blank cell.
IF all of the cells above are Blank (i.e. near the top of the column) then look at the range from the top of the Type column down to the row of the next "DOWN"​

Compare the High column values in that range of cells.​

If the High of the current row has the highest value of that range then return "Peak"
If the value of the High in the current row is not the highest in that range then return a Blank cell.​

IF the last entry above is "Trough" then look at the range from that row down to the row of the next "DOWN" in the Type column.​

Compare the High column values in that range of cells.​

If the value of the High in the current row has the highest value of that range then return "Peak"
If the value of the High in the current row is not the highest in that range then return a Blank cell.
​

Condition 4
IF the Type in the current row is "OUTSIDE", then search the Type cells above for the nearest either "UP" or "DOWN" Type.

IF the nearest Type above is "DOWN" then search the rows below for the nearest "UP" Type.​

Using the range of cells from the above "DOWN" to the below "UP".
Compare the Low column values in this range.​

If the value of the Low in the current row is the lowest in that range of cells, then return "Trough".
If the value of the Low in the current row is not the lowest in that range then return a Blank cell.​

IF the nearest Type above is "UP" then search the rows below for the nearest "DOWN" Type.​

Using the range of cells from the above "UP" to the below "DOWN".
Compare the High column values in this range.​

If the value of the High in the current row is the Highest in that range of cells, then return "Peak"
If the value of the High in the current row is not the Highest in that range of cells, then return a Blank cell.​

If all of the cells above are either OUTSIDE or INSIDE or combinations of these (i.e. Near the top of the column) then return a Blank cell.




​

Below shows what I am trying to achieve. This sample data doesnt test for all possabilities of the 4 conditions but I thought I shouldnt incude a large data set here.

Excel 2007

	A	B	C	D	E
Date	High	Low	Type	Peak or Trough
Trough
Peak
Trough
Peak
Trough
Peak
Trough
Peak

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]31/08/2009
[/TD]
[TD="align: right"]62.56
[/TD]
[TD="align: right"]61.92
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]1/09/2009
[/TD]
[TD="align: right"]62.2
[/TD]
[TD="align: right"]60.6
[/TD]
[TD="align: center"]DOWN
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]2/09/2009
[/TD]
[TD="align: right"]61.29
[/TD]
[TD="align: right"]60.07
[/TD]
[TD="align: center"]DOWN
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]3/09/2009
[/TD]
[TD="align: right"]62.46
[/TD]
[TD="align: right"]61.08
[/TD]
[TD="align: center"]UP
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]4/09/2009
[/TD]
[TD="align: right"]62.62
[/TD]
[TD="align: right"]61.4
[/TD]
[TD="align: center"]UP
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]8/09/2009
[/TD]
[TD="align: right"]65.74
[/TD]
[TD="align: right"]64.75
[/TD]
[TD="align: center"]UP
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]9/09/2009
[/TD]
[TD="align: right"]65.4
[/TD]
[TD="align: right"]64.09
[/TD]
[TD="align: center"]DOWN
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]10/09/2009
[/TD]
[TD="align: right"]66.24
[/TD]
[TD="align: right"]64.3
[/TD]
[TD="align: center"]UP
[/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]11/09/2009
[/TD]
[TD="align: right"]67.83
[/TD]
[TD="align: right"]66.32
[/TD]
[TD="align: center"]UP
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"]14/09/2009
[/TD]
[TD="align: right"]66.25
[/TD]
[TD="align: right"]65.16
[/TD]
[TD="align: center"]DOWN
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"]15/09/2009
[/TD]
[TD="align: right"]67.1
[/TD]
[TD="align: right"]65.53
[/TD]
[TD="align: center"]UP
[/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"]16/09/2009
[/TD]
[TD="align: right"]69.46
[/TD]
[TD="align: right"]65.4
[/TD]
[TD="align: center"]OUTSIDE
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"]17/09/2009
[/TD]
[TD="align: right"]69.22
[/TD]
[TD="align: right"]67.8
[/TD]
[TD="align: center"]INSIDE
[/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"]18/09/2009
[/TD]
[TD="align: right"]69.3
[/TD]
[TD="align: right"]66.8
[/TD]
[TD="align: center"]OUTSIDE
[/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"]21/09/2009
[/TD]
[TD="align: right"]69.4
[/TD]
[TD="align: right"]63.85
[/TD]
[TD="align: center"]OUTSIDE
[/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"]22/09/2009
[/TD]
[TD="align: right"]67.39
[/TD]
[TD="align: right"]66.4
[/TD]
[TD="align: center"]INSIDE
[/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"]23/09/2009
[/TD]
[TD="align: right"]67.27
[/TD]
[TD="align: right"]65.55
[/TD]
[TD="align: center"]DOWN
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"]24/09/2009
[/TD]
[TD="align: right"]67.3
[/TD]
[TD="align: right"]63.5
[/TD]
[TD="align: center"]OUTSIDE
[/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"]25/09/2009
[/TD]
[TD="align: right"]67.35
[/TD]
[TD="align: right"]63.4
[/TD]
[TD="align: center"]OUTSIDE
[/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"]28/09/2009
[/TD]
[TD="align: right"]66.1
[/TD]
[TD="align: right"]64.24
[/TD]
[TD="align: center"]INSIDE
[/TD]

[TD="align: center"]22
[/TD]
[TD="align: right"]29/09/2009
[/TD]
[TD="align: right"]66.2
[/TD]
[TD="align: right"]64.2
[/TD]
[TD="align: center"]OUTSIDE
[/TD]

[TD="align: center"]23
[/TD]
[TD="align: right"]30/09/2009
[/TD]
[TD="align: right"]66.57
[/TD]
[TD="align: right"]64.7
[/TD]
[TD="align: center"]UP
[/TD]

[TD="align: center"]24
[/TD]
[TD="align: right"]1/10/2009
[/TD]
[TD="align: right"]64.92
[/TD]
[TD="align: right"]62.76
[/TD]
[TD="align: center"]DOWN
[/TD]

</tbody>

Limits2​

Would appreciate any thoughts or help on this problem.
Thank you.​