Formula to match different ranges of time and prices

[santiago_dl]

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

[venkat1926]

your target alert is first open-0.25. but which price open, low or close should reach this target. I presume it is low. as soon as low is reached target you want to get an alert. when target is not reached you don't want to get any message.


the first open will be entered in C3
subsequent times will be downloaded in later rows.

if you right click sheet tab and click view code and park this event code and save the file. if you have the same sheet sheet1 for each day (that is you clear the sheet1 and start downloading the data) then this is ok. if you have different sheets for each day you have to park the file in each sheet.
try this and if there is problem post back, clearly describing what you have done what error you got etc

Please Login or Register  to view this content.