# Find First Negative Number after 'X' has been reached

#### dwg1803

##### New Member
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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?

Hi Oldbrewer,

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

thanks.

Will

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

thanks.

Will

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...

thanks for looking into oldbrewer. enjoy some Brews!!

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?

 -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>

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

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.

Replies
3
Views
283
Replies
4
Views
548
Replies
4
Views
237
Replies
3
Views
533
Replies
46
Views
2K

### Forum statistics

Threads
1,221,052
Messages
6,157,632
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back