Check value in a time range

Hi,

I have a table with dates and respective stock prices.
Now i'm trying to check if - during a certain time period - the stock price has been below a certain value. Since I will have to do the same thing for more than 1000 values in different time periods, I can't do it manually. Any idea how to do this in a spreasheet without vba? If not, then by vba?

Sheet looks as following:

A1 Starting date of period
B1 Ending date of period
C1 Barrier value (Value that needs to be checked)
A2 - A366 Dates (2007)
B2 - B366 Stock prices (2007)

Hi,

I have posted the same question under the worksheet function thread

http://www.excelforum.com/showthread...98#post1885298

but it's probably more of a programming problem.

I have a table with dates and respective stock prices.
Now i'm trying to check if - during a given time period - the stock price has been below a certain value. Since I will have to do the same thing for more than 1000 values in different time periods, I can't do it manually. Any idea how to do this in a spreasheet without vba? If not, then by vba?

Help would be very much appreciated...

Great, thx a lot.
Had to adjust it a little bit to

=IF(SUMPRODUCT(($A$9:$A$89>=D$2)*($A$9:$A$89<=D$3) *(D$9:D$89<=D$4))>0;"Yes";"No")

but it was of great help.

badin

Follow-up question...

in the excel sheet i have the stocks are not in same coloumns as the values that i would like to check. Even worse, the values that i need to check are not in the same sheet as the stocks. Example attached.

any idea on this?

I'm interested in what you're trying to do with your stock data. This attached file should help you organize how you set up your spreadsheet.

How detailed are you getting with your spreadsheet?

Thanks,
Impala

Thanks...
but it doesn't really work, 'cause i still need to link every barrier with the correct stock manually.

I'm using it for a paper that i'm writing on barrier options (financial derivatives). need to check if certain barriers have been knocked or not.