Finding the first value equal to or less than 0

[jatkin]

Basically

i have hundreds of rows

the top row has a date going out for many columns , so picture this


1/1 2/1 3/1 4/1 5/1 6/1
10 8 7 0 -4 -6 -8

and then rows of numbers, with the date only in the first row


I need to find out the first number equal to or less than 0 in each
row and pull the date for that number. So, when searches down this
row in the example above, it sees 0, which is in the fourth column,
that fourth column refers to the date 4/1. Picture it as many bank
accounts that are depleting. At what date will the bank account
become empty or negative

one thing to note, sometimes my data as non-numeric values and is not
in order. but in general, its to be expected to be decrementing
numbers

make sense?

thanks, Josh

 

[T. Valko]

Are there any empty cells?

This assumes no empty cells:

B1:G1 - date headers
B2:G2 = numbers

Entered in I2 and copied down as needed:

=INDEX(B$1:G$1,MATCH(TRUE,INDEX(B2:G2<=0,1,),0))

Format as DATE

Biff

 

[jatkin]

Basically

i have hundreds of rows

the top row has a date going out for many columns , so picture this

1/1 2/1 3/1 4/1 5/1 6/1
10 8 7 0 -4 -6 -8

and then rows of numbers, with the date only in the first row

I need to find out the first number equal to or less than 0 in each
row and pull the date for that number. So, when searches down this
row in the example above, it sees 0, which is in the fourth column,
that fourth column refers to the date 4/1. Picture it as many bank
accounts that are depleting. At what date will the bank account
become empty or negative

one thing to note, sometimes my data as non-numeric values and is not
in order. but in general, its to be expected to be decrementing
numbers

make sense?

thanks, Josh

Got it:

Try this:
Assume your data in row 1

=INDEX(1:1,MATCH(TRUE,1:1<>0,0))

ctrl+shift+enter, not just enter

 

[T. Valko]

Got it:

Try this:
Assume your data in row 1

=INDEX(1:1,MATCH(TRUE,1:1<>0,0))

ctrl+shift+enter, not just enter

That doesn't do what you asked for based on your explanation and sample
data.

Biff