finding first non-zero entry in a row

[ljvb]

I'm trying to select out the first (left-most) non-zero entry in a row. Any ideas on a formula for this?

 

[Aladin Akyurek]

On 2002-07-02 12:30, ljvb wrote:
I'm trying to select out the first (left-most) non-zero entry in a row. Any ideas on a formula for this?

Array-enter:

=OFFSET(A2,0,MIN(IF(2:2>0,COLUMN(2:2)))-1,1,)

to get the left-most non-zero value in row 2.

Again, array-enter:

=MIN(IF(2:2>0,COLUMN(2:2)))

to get the column number of the left-most cell housing a non-zero value in row 2.

 

[ljvb]

Aladin, thanks for the post. This is clost but doesn't work because the if statement puts zeros in if the logical statement is false, which defeats the purpose of trying to select the zeroes out. I slightly modified what you sent to change the if statement to put a large number in if the logical statement is false (i.e. zero values) and that seems to work. I also changed the >0 to <>0 because I was looking for non zero entries.

=OFFSET(A2,0,MIN(IF(2:2<>0,COLUMN(2:2),EXP(100)))-1,1,)

Thanks.

 

[Aladin Akyurek]

On 2002-07-03 02:16, ljvb wrote:
Aladin, thanks for the post. This is clost but doesn't work because the if statement puts zeros in if the logical statement is false, which defeats the purpose of trying to select the zeroes out. I slightly modified what you sent to change the if statement to put a large number in if the logical statement is false (i.e. zero values) and that seems to work. I also changed the >0 to <>0 because I was looking for non zero entries.

=OFFSET(A2,0,MIN(IF(2:2<>0,COLUMN(2:2),EXP(100)))-1,1,)

Thanks.

My fault. I forgot to include neg values.

You can also keep the original by changing it to:

=OFFSET(A2,0,MIN(IF(2:2,COLUMN(2:2)))-1,1,)

The IF(2:2 bit means: anything non-zero in 2:2 evaluates to TRUE, otherwise FALSE.

Aladin