Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to find highs and lows formula and how-to
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. -- JRod |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to find highs and lows formula and how-to
when is your rule of thumb not accurate?
try use 2 helper columns (D:E?) in D4 enter =IF(NOT(OR(AND(B4B2,B4B3,B4B5,B4B6),AND(B4<A1, A3<B3,B4<B5,B4<B6))),D3,IF(AND(B4B2,B4B3,B4B5,B 4B6),"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" wrote: 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. -- JRod |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to find highs and lows formula and how-to
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 "bj" wrote: when is your rule of thumb not accurate? try use 2 helper columns (D:E?) in D4 enter =IF(NOT(OR(AND(B4B2,B4B3,B4B5,B4B6),AND(B4<A1, A3<B3,B4<B5,B4<B6))),D3,IF(AND(B4B2,B4B3,B4B5,B 4B6),"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" wrote: 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. -- JRod |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to find highs and lows formula and how-to
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. -- JRod "bj" wrote: when is your rule of thumb not accurate? try use 2 helper columns (D:E?) in D4 enter =IF(NOT(OR(AND(B4B2,B4B3,B4B5,B4B6),AND(B4<A1, A3<B3,B4<B5,B4<B6))),D3,IF(AND(B4B2,B4B3,B4B5,B 4B6),"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" wrote: 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. -- JRod |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to find highs and lows formula and how-to
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="L ow",E3<E2),"Low","")))) hide the first and second helper columnscolumns "JRod" wrote: 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. -- JRod "bj" wrote: when is your rule of thumb not accurate? try use 2 helper columns (D:E?) in D4 enter =IF(NOT(OR(AND(B4B2,B4B3,B4B5,B4B6),AND(B4<A1, A3<B3,B4<B5,B4<B6))),D3,IF(AND(B4B2,B4B3,B4B5,B 4B6),"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" wrote: 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. -- JRod |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to find highs and lows formula and how-to
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! -- JRod "bj" wrote: 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="L ow",E3<E2),"Low","")))) hide the first and second helper columnscolumns "JRod" wrote: 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. -- JRod "bj" wrote: when is your rule of thumb not accurate? try use 2 helper columns (D:E?) in D4 enter =IF(NOT(OR(AND(B4B2,B4B3,B4B5,B4B6),AND(B4<A1, A3<B3,B4<B5,B4<B6))),D3,IF(AND(B4B2,B4B3,B4B5,B 4B6),"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" wrote: 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. -- JRod |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to find the right formula | Excel Discussion (Misc queries) | |||
Counting Higher Highs | Excel Discussion (Misc queries) | |||
Need to find a formula | Excel Worksheet Functions | |||
Plot Highs and lows? | Charts and Charting in Excel | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |