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
1	Date	High	Low	Type	Peak/Trough
2	31/08/2009	62.56	61.92
3	1/09/2009	62.2	60.6	DOWN
4	2/09/2009	61.29	60.07	DOWN
5	3/09/2009	62.46	61.08	UP
6	4/09/2009	62.62	61.4	UP
7	8/09/2009	65.74	64.75	UP
8	9/09/2009	65.4	64.09	DOWN
9	10/09/2009	66.24	64.3	UP
10	11/09/2009	67.83	66.32	UP
11	14/09/2009	66.25	65.16	DOWN
12	15/09/2009	67.1	65.53	UP
13	16/09/2009	69.46	65.4	OUTSIDE
14	17/09/2009	69.22	67.8	INSIDE
15	18/09/2009	69.3	66.8	OUTSIDE
16	21/09/2009	69.4	63.85	OUTSIDE
17	22/09/2009	67.39	66.4	INSIDE
18	23/09/2009	67.27	65.55	DOWN
19	24/09/2009	67.3	63.5	OUTSIDE
20	25/09/2009	67.35	63.4	OUTSIDE
21	28/09/2009	66.1	64.24	INSIDE
22	29/09/2009	66.2	64.2	OUTSIDE
23	30/09/2009	66.57	64.7	UP
24	1/10/2009	64.92	62.76	DOWN

<tbody>
</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
1	Date	High	Low	Type	Peak or Trough
2	31/08/2009	62.56	61.92
3	1/09/2009	62.2	60.6	DOWN
4	2/09/2009	61.29	60.07	DOWN	Trough
5	3/09/2009	62.46	61.08	UP
6	4/09/2009	62.62	61.4	UP
7	8/09/2009	65.74	64.75	UP	Peak
8	9/09/2009	65.4	64.09	DOWN	Trough
9	10/09/2009	66.24	64.3	UP
10	11/09/2009	67.83	66.32	UP	Peak
11	14/09/2009	66.25	65.16	DOWN	Trough
12	15/09/2009	67.1	65.53	UP
13	16/09/2009	69.46	65.4	OUTSIDE	Peak
14	17/09/2009	69.22	67.8	INSIDE
15	18/09/2009	69.3	66.8	OUTSIDE
16	21/09/2009	69.4	63.85	OUTSIDE
17	22/09/2009	67.39	66.4	INSIDE
18	23/09/2009	67.27	65.55	DOWN
19	24/09/2009	67.3	63.5	OUTSIDE
20	25/09/2009	67.35	63.4	OUTSIDE	Trough
21	28/09/2009	66.1	64.24	INSIDE
22	29/09/2009	66.2	64.2	OUTSIDE
23	30/09/2009	66.57	64.7	UP	Peak
24	1/10/2009	64.92	62.76	DOWN

<tbody>
</tbody>

Limits2​

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