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

Hi!

Try these:

For the first:

=MATCH(1,A1:J1,0)

For the last:

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

Biff

"anand" wrote in message
...
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" wrote:

Hi!

Try these:

For the first:

=MATCH(1,A1:J1,0)

For the last:

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

Biff

"anand" wrote in message
...
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


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

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

If your range of data was G1:P1

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

If your range of data was V1:AE1

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

Biff

"anand" wrote in message
...


"Biff" wrote:

Hi!

Try these:

For the first:

=MATCH(1,A1:J1,0)

For the last:

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

Biff

"anand" wrote in message
...
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


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

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" wrote in message
...
That's why it's always a good idea to tell us where your data is.

If your range of data was G1:P1

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

If your range of data was V1:AE1

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

Biff

"anand" wrote in message
...


"Biff" wrote:

Hi!

Try these:

For the first:

=MATCH(1,A1:J1,0)

For the last:

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

Biff

"anand" wrote in message
...
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


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

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

"anand" wrote:

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