Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula to match different ranges of time and prices
Hi everybody, I'm from Argentina and I need to solve a formula for an algorithm.
Table Below shows the prices and times where I calculate the data I need: Date time Open High Low Close Volume 1/6/12 16:03 68 68 68 68 53064 1/6/12 15:59 67.96 68.04 67.86 68.04 58543 1/6/12 15:58 67.95 68.03 67.91 67.95 18531 1/6/12 15:57 68.1 68.11 67.91 67.97 38883 1/6/12 15:56 68.08 68.13 68.08 68.1 21993 1/6/12 15:55 68.15 68.15 68.07 68.08 45585 In the table below, I calculated with this formula =+INDEX(C$2:C$52545,MATCH(1,IF(A$2:A$52545=AA3,IF( B$2:B$52545<=$AA$2,IF(ISNUMBER(C$2:C$52545),1))),0 )) the open price at a specific time (9:57 in this case) for a specific date. Date Open 9:57 1/6/12 62.81 1/5/12 58.38 1/4/12 54.65 1/3/12 54.51 What I need now, is another data, that is: If 62.81 (example i use that price for 1/6/12) - 0.25 "62.56" exist from 9:57 to 13:00 give me the time/price when that happens. The problem here, ((I give you a true example)): This was the open price Date Open 9:57 1/6/12 62.81 And from 9:57 to 9:59 Date time Open High Low Close Volume 1/6/12 9:59 62.75 63.13 62.66 62.79 24902 1/6/12 9:58 62.7 62.77 62.4 62.66 34150 1/6/12 9:57 62.81 62.92 62.67 62.72 19369 Yo can see that the price went down for more than 25 cents, It went down to 62.40. If I put to match me Date Open 9:57 1/6/12 62.81-0.25= 62.56 It will give me 0, as if didn;t exist. I would need to know if there's a possible way to give me the time when the price has been below the amount of cents I want. (0.25 in this case) Is that possible???? Thank you very much. Santiago |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a chart showing ranges of prices of different produc | Excel Discussion (Misc queries) | |||
Chart showing ranges of prices of different items? | Charts and Charting in Excel | |||
How to match 2 prices | Excel Worksheet Functions | |||
Graphing A Time Series of Multiple Stock Prices | Excel Worksheet Functions | |||
Bond prices: summarize ranges from different worksheets | Excel Programming |