Find First Negative Number after 'X' has been reached

[dwg1803]

Hello All, i am in a pinch.

I have read several threads and the content looks great. I just haven't found a solution for my challenge yet.

Here it is, any insight would be greatly appreciated.

my screen shot: 2014-09-05_1302 - willgrimmer's library

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.

Any insight would be Greatly Appreciated.

thanks in advance.


Will

 

[oldbrewer]

3 is exceeded by the second number (5)

you want the next time a number is <1 after the 5 ie -2 the third number = 3 is this correct?

 

[dwg1803]

Hi Oldbrewer,

yes, that is exactly what i am looking for. I'm glad you were able to decipher my explanation.


thanks.


Will

 

[dwg1803]

Hi Oldbrewer, any ideas? I've been looking around online and haven't found any solutions yet.


thanks.


Will

 

[oldbrewer]

I am thinking around using offset match to find your chosen number in one row and in the row below offset match that only triggers if chosen number detected in row above UK here beer time if u still need it tomorrow I will think on...

 

[dwg1803]

thanks for looking into oldbrewer. enjoy some Brews!!

 

[Aladin Akyurek]

Hello All, i am in a pinch.

I have read several threads and the content looks great. I just haven't found a solution for my challenge yet.

Here it is, any insight would be greatly appreciated.

my screen shot: 2014-09-05_1302 - willgrimmer's library

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.

Any insight would be Greatly Appreciated.

thanks in advance.


Will

Row 3, K1 =3 , result is "3". Such makes things hard to follow...

Care to stipulate how "3" obtains?

 

[oldbrewer]

	-5, 5, -2, 4, -2, 10, 4, 11							number	3
	1	2	3	4	5	6	7	8
	-5	5	-2	4	-2	10	4	11
	0	1	0	0	0	0	0	0
	0	1	2	3	4	5	6	7	8
	0	-2	0	0	0	0	0	0	0	-2	3
										first neg	position
												a bit convoluted butwould this be ok if tidied up ?

<colgroup><col span="13"></colgroup><tbody>
</tbody>

 

[mikerickson]

This solution uses names

Name: FirstCell RefersTo: =Sheet1!$A$3
Name: LastCell RefersTo: =Sheet1!$H$3
Name: dataRange RefersTo: =FirstCell:LastCell

Name: AdjustedRange
RefersTo: =INDEX(dataRange,1, MIN(IF(dataRange>Sheet1!$K$1,COLUMN(dataRange)))):LastCell

Then the formula =MIN(IF(AdjustedRange<0,COLUMN(AdjustedRange))) should return the column number of the cell in question.

Note this column number is relative to the sheet, not the dataRange

 

[Rick Rothstein]

I think this array-entered** formula may ultimately break down to be the same as Mike's formula (I did not check), but without the Defined Names...

=MIN(IF((INDEX(3:3,,1+MIN(IF(A3:H3>3,COLUMN(A3:H3)))):H3)<0,COLUMN(INDEX(3:3,,1+MIN(IF(A3:H3>3,COLUMN(A3:H3)))):H3)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.