Need to find highs and lows formula and how-to

[JRod]

I see a couple of similar questions already posted but they don't 100% help.
I have a list of currency data - similar to stock data. It shows highs and
lows during a specified period of time. It looks like this:

High Low
4/30/2007 6:00 1.9960 1.9951
4/30/2007 6:30 1.9954 1.9944
4/30/2007 7:00 1.9954 1.9942
4/30/2007 7:30 1.9954 1.9947
4/30/2007 8:00 1.9948 1.9923
4/30/2007 8:30 1.9966 1.9926
4/30/2007 9:00 1.9958 1.9921
4/30/2007 9:30 1.9940 1.9922
4/30/2007 10:00 1.9936 1.9902
4/30/2007 10:30 1.9912 1.9894
4/30/2007 11:00 1.9919 1.9891
4/30/2007 11:30 1.9936 1.9912
4/30/2007 12:00 1.9946 1.9929
4/30/2007 12:30 1.9948 1.9935
4/30/2007 13:00 1.9948 1.9928
4/30/2007 13:30 1.9935 1.9929
4/30/2007 14:00 1.9941 1.9925
4/30/2007 14:30 1.9968 1.9931
4/30/2007 15:00 1.9958 1.9944
4/30/2007 15:30 1.9968 1.9945
4/30/2007 16:00 1.9964 1.9953
4/30/2007 16:30 1.9998 1.9959
4/30/2007 17:00 2.0026 1.9991
4/30/2007 17:30 2.0029 1.9994

I am trying to find the first high, then the next low point, then the next
high and so on. If the market is moving down then this would be reversed. I
would find the low, then the next high, then the next low. I am not looking
for a daily high/low or just one high/low. I need to find high points and
low points as the market creates them.

To define high and low, typically it is a point higher and lower than the
previous two or next two time periods. This isn't always the case but is a
rule of thumb.

 

[bj]

when is your rule of thumb not accurate?

try
use 2 helper columns (D:E?)
in D4 enter
=IF(NOT(OR(AND(B4>B2,B4>B3,B4>B5,B4>B6),AND(B4<A1,A3<B3,B4<B5,B4<B6))),D3,IF(AND(B4>B2,B4>B3,B4>B5,B4>B6),"High",IF(AND(B4<B2,B4<B3,B4<B5,B4<B6),"Low")))
in E4 enter
=if(d4<>d3,d4,"")
copy d4 and E4 and paste down where you want them to go

 

[JRod]

BJ

Thanks for your response. The rule of thumb is not accurate during large
price fluxuations such as during economic news. There are instances where a
new low and a new high will be seen in the same time period - in this case a
30 minute time period.

Question for you regarding your formula - are you assuming column A contains
data for "High" and column "B" the data for low? I plugged in the formula
with this assumption and it did indicate only Highs found but not accurately.
Should't I be checking the argument against the High column data first and
then the low column data second?

I did notice that the post I made shifted the column headers to the left and
I believe I have corrected this.

Any further assistance you can give me is greatly appreciated.

 

[JRod]

After my original reply I was able to find highs and lows in two separate
test columns. Now I need to put those two columns into one column by
gathering the numbers from left to right, top to bottom. In other words, A1,
B1, A2, B2, A3, B3 and so on.

Perhaps you can help me with this to finish out my project here. When I
attempt to copy it skips rows.

 

[bj]

try with three helper columns
the first with the highs, the second with the lows
in the third try
=if(and(d3=d2,E3=E2),"", if(and(,D3="High",D3<>D2,E3="Low",E3<>E2),
"Both",if(and(D3="High",D3<>D2)"High",if(and(E3="Low",E3<>E2),"Low",""))))
hide the first and second helper columnscolumns

 

[JRod]

BJ

Thanks again for the response. Let me clarify, however, that what I now
need to get into one column are the values, not "high", "low", or "both". So
I need the actual price alternating back and forth between the high and low
column to get one string of price movements.

Suggestions? Thanks again!