how do I obtain position of specific value in a row of numbers

[Guest]

I have a row of 10 binary values in a row (column a to J for example). an
example follows:

0010011000

I need a formula that will return the position (numerically) of the first
"1" and another that will give the position of the last "1".

for the above, the formulas would return 3 and 7 respectively.

Can anyone advise?

anand

 

[Biff]

Hi!

Try these:

For the first:

=MATCH(1,A1:J1,0)

For the last:

=LOOKUP(2,1/(A1:J1=1),COLUMN(A1:J1))

Biff

 

[Guest]

Where your data is in A1:
First position:
=FIND(1,A1)

Last position (array entered using Control+Shift+Enter):
=MAX(FIND(1,A1,ROW(INDIRECT("1:"&LEN(A1)))))

 

[Guest]

Hi!

Try these:

For the first:

=MATCH(1,A1:J1,0)

For the last:

=LOOKUP(2,1/(A1:J1=1),COLUMN(A1:J1))

Biff




Close but not quite for the 2nd one.

The data is entered in repeating blocks spread out by about 15 columns. So
there is a block of data of 10 columns every 15 columns (i.e. 5 blank columns
between each). The 2nd formula works ok if the data is in column A to J but
does not work for other columns.

Is there a version that will work for other positions?

For what it is worth, the cells into which the formula will be placed is
always 2 spaces to the left of the data block of interest. E. g if the first
of the 10 columns with the binary data is G, then the formula will go in E.
If the first of the 10 data columns is V, the formula will paste into T.

Can you advise?

anand

 

[Guest]

Hi!

Try these:

For the first:

=MATCH(1,A1:J1,0)

For the last:

=LOOKUP(2,1/(A1:J1=1),COLUMN(A1:J1))

Biff




Close but not quite for the 2nd one.

The data is entered in repeating blocks spread out by about 15 columns. So
there is a block of data of 10 columns every 15 columns (i.e. 5 blank columns
between each). The 2nd formula works ok if the data is in column A to J but
does not work for other columns.

Is there a version that will work for other positions?

For what it is worth, the cells into which the formula will be placed is
always 2 spaces to the left of the data block of interest. E. g if the first
of the 10 columns with the binary data is G, then the formula will go in E.
If the first of the 10 data columns is V, the formula will paste into T.

Can you advise?

anand

 

[Biff]

That's why it's always a good idea to tell us where your data is.

If your range of data was G11

=LOOKUP(2,1/(G11=1),COLUMN(G11)-COLUMN(G1)+1)

If your range of data was V1:AE1

=LOOKUP(2,1/(V1:AE1=1),COLUMN(V1:AE1)-COLUMN(V1)+1)

Biff

 

[Biff]

That's why it's always a good idea to tell us where your data is.

If your range of data was G11

=LOOKUP(2,1/(G11=1),COLUMN(G11)-COLUMN(G1)+1)

If your range of data was V1:AE1

=LOOKUP(2,1/(V1:AE1=1),COLUMN(V1:AE1)-COLUMN(V1)+1)

Biff

 

[Biff]

This may be less confusing:

{1,2,3,4...,10}

Represents the total number of cells in the range and thus, the values
position:

=LOOKUP(2,1/(B1:K1=1),{1,2,3,4,5,6,7,8,9,10})

Biff

 

[Biff]

This may be less confusing:

{1,2,3,4...,10}

Represents the total number of cells in the range and thus, the values
position:

=LOOKUP(2,1/(B1:K1=1),{1,2,3,4,5,6,7,8,9,10})

Biff