Great site, i have an excel challenge and I haven't found a solution yet.

Here it is, any insight would be greatly appreciated.

http://screencast.com/t/1mxOcUoOqhlD

I have 8 cells in columns A-H in Row 3. their contents are

-5, 5, -2, 4, -2, 10, 4, 11

in Cell K1 = 3

I need a formula that will tell me which position in Row 3; where after the value in K1 has been exceeded, I need the position of the next time i have a value of less than or equal to "Zero". Looking at the data, the answer should be "3", but I am having a hard time using "Match" and "Index" and "ISNA" formulas that are not working.

Basically, 3 is exceeded by the second number (5), I want the next time a number is <1 after the 5 ie -2 the third number, so the answer should be "3".


Any insight would be Greatly Appreciated.

thanks in advance.


Will

The best I can figure right at this moment is to copy this formula into column M, and then copy past column M through column T:
=IF(A3=$K$3,COLUMN(A3),"")

Then in column V paste this formula:
=INDEX(A3:H3,1,MIN(M3:T3)+1)

That returns the correct answer but requires the use of those additional columns. I'm still thinking on a more elegant solution.

Hi,

Am Fri, 5 Sep 2014 19:01:49 +0100 schrieb dwg1803:

-5, 5, -2, 4, -2, 10, 4, 11

in Cell K1 = 3

I need a formula that will tell me which position in Row 3; where after
the value in K1 has been exceeded, I need the position of the next time
i have a value of less than or equal to "Zero"

copy following code in a standard module and call the function in the
worksheet in row 3 with
=myCol()

Function myCol() As Long
Dim i As Long
Dim myRow As Long

myRow = Application.Caller.Row
For i = 1 To 8
If Cells(myRow, i) = Range("K1") And _
Cells(myRow, i + 1) <= 0 Then
myCol = i + 1
Exit For
End If
Next
End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Mon, 8 Sep 2014 17:22:09 +0200 schrieb Claus Busch:

copy following code in a standard module and call the function in the
worksheet in row 3 with
=myCol()

there is an error in the last answer.
Try following code:

Function myCol() As Variant
Dim i As Long
Dim myRow As Long
Dim myFlag As Boolean

Application.Volatile
myRow = Application.Caller.Row
For i = 1 To 8
If Cells(myRow, i) = Range("K1") Then
myFlag = True
End If
If myFlag = True And Cells(myRow, i + 1) <= 0 Then
myCol = i + 1
Exit For
End If
Next
myCol = IIf(myCol 8, "NA", myCol)
End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Disregard my response: I realized it does not fulfill the requirements.