Excel formulas 201...

August 1, 2008 5:20 PM

Intermediate level Excel formula question.... Say I have a column with an array of dates in order and a matching column with an array of values. I need a formula that outputs the date at which one of the values first falls below a certain number.

For example. Let's say I have in rows 1-5 and columns A-B...

1/1/05 10
1/2/05 20
1/3/05 30
1/4/05 15
1/5/05 26
1/6/05 43
1/7/05 12


I'd like the date of the first value greater than 21 to be output (ie 1/3/05).

For bonus points, how do I output the date at which the value is greater than 21 for the second time (ie 1/5/05).

posted by drpynchon to Computers & Internet (2 answers total)

Best answer: For the first smallest date:
=small(if(b1:b7>21,a1:a7),1) and ctrl+shift+enter
For the second:
=small(if(b1:b7>21,a1:a7),2) and ctrl+shift+enter
posted by milkrate at 5:30 PM on August 1, 2008

Response by poster: Thank you SO much, milkrate.. That was awesome!
posted by drpynchon at 9:19 PM on August 1, 2008