How many cells does it take to exceed X, and stop counting

[Diamond Dave]

Ladies and gentlemen,
I am trying to compare a value ( say C80 ) and figure out how many cells it takes to exceed a value ( say -2 ) in a column ( say E79:E2 ) but then actually stopping the count after that first cell count has been triggered.
I'm in a bind - any help would be highly appreicated.
Dave

 

[taigovinda]

Dave! Sorry for dropping the ball on this one.

See if this does what you want (formula pasted down):

Excel Workbook
	A	B	C	D	E	F
1	Date		High		Close	Answer
2	6-May-09		38.77		37.76	#N/A
3	7-May-09		40.14		39.58	1
4	8-May-09		41.57		41.3	2
5	11-May-09		41.01		40.55	3
6	12-May-09		41.48		40.67	4
7	13-May-09		40.78		39.94	5
8	14-May-09		40.3			6
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F2	=ROW(C2)-LOOKUP(2,1/($E1:E$2<=C2-2),ROW($E1:E$2))

 

[Diamond Dave]

I really appreciate your help with this. I will definately try that out. You would understand where I tried to hit up multiple sources to solve this, and a guy that I work with was able to work it in VB. I am still going to try out your formula since the spreadsheet would not have to call out for a VB macro. Just in case though, here is what he came up with.

Function greaterBy2GoingUp(MatchValue As Variant, SearchRange As Range)
Dim i As Long, test As Single
For i = SearchRange.Cells.Count To 1 Step -1
test = MatchValue - 2
If SearchRange.Cells(i) < test Then
greaterBy2GoingUp = 1 + SearchRange.Cells.Count - i
Exit Function
End If
Next
greaterBy2GoingUp = "#N/A"
End Function
Function greaterBy2GoingDown(MatchValue As Variant, SearchRange As Range)
Dim i As Long, test As Single
For i = 1 To SearchRange.Cells.Count Step 1
test = MatchValue + 2
If SearchRange.Cells(i) > test Then
greaterBy2GoingDown = i
Exit Function
End If
Next
greaterBy2GoingDown = "#N/A"
End Function

Again thank for your help and I will let you know if the formula works!
Dave