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