Locating Last Nonzero Cell in a Row

[wilby31]

I need to locate the last nonzero term in a row. The rows consist o
zeros and ones.

For instance:

__ A B C D E F
1| 1 1 1 0 0 3
2| 1 0 1 0 1 5
3| 0 1 0 1 1 5

As you can see in column F, I have my desired results (the number o
the column of the last 1). I have tried incorporating SUM and COUNTI
functions to place where the last 1 would be in a row with a know
size, but I can't figure out how to account for the 0's that ar
located within the first and the last 1.

Any ideas? This has plagued me for a couple days now.

Thanks,
Bren

 

[Biff]

Hi!

One way:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(A1:E1>0,COLUMN(A1:E1)))

Biff

 

[Domenic]

Try...

F1, copied down:

=LOOKUP(2,1/(A1:E1=1),COLUMN(A1:E1)-COLUMN(A1)+1)

....confirmed with just ENTER, or...

=MATCH(2,1/(A1:E1=1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Bruno Campanini]

I need to locate the last nonzero term in a row. The rows consist of
zeros and ones.

For instance:

__ A B C D E F
1| 1 1 1 0 0 3
2| 1 0 1 0 1 5
3| 0 1 0 1 1 5

As you can see in column F, I have my desired results (the number of
the column of the last 1). I have tried incorporating SUM and COUNTIF
functions to place where the last 1 would be in a row with a known
size, but I can't figure out how to account for the 0's that are
located within the first and the last 1.

Any ideas? This has plagued me for a couple days now.

Another alternative:

{=MAX(OFFSET(A1,,,1,5)*COLUMN(A1:E1))}
FormulaArray

Bruno

 

[Bruno Campanini]

I need to locate the last nonzero term in a row. The rows consist of
zeros and ones.

For instance:

__ A B C D E F
1| 1 1 1 0 0 3
2| 1 0 1 0 1 5
3| 0 1 0 1 1 5

As you can see in column F, I have my desired results (the number of
the column of the last 1). I have tried incorporating SUM and COUNTIF
functions to place where the last 1 would be in a row with a known
size, but I can't figure out how to account for the 0's that are
located within the first and the last 1.

Any ideas? This has plagued me for a couple days now.

Another alternative:

{=MAX(OFFSET(A1,,,1,5)*COLUMN(A1:E1))}
FormulaArray

Bruno

 

[wilby31]

Thanks everyone! Those are all great suggestions! You saved me from a
severe migrane.

 

[Harlan Grove]

Bruno Campanini wrote...
....

Another alternative:

{=MAX(OFFSET(A1,,,1,5)*COLUMN(A1:E1))}
FormulaArray

Never use volatile functions (like OFFSET) unnecessarily. Biff's
similar array formula,

=MAX(IF(A1:E1>0,COLUMN(A1:E1)))

is preferable. Further, there's NEVER a good reason to use OFFSET with
constant 2nd through 5th arguments. Simple cell references would always
be preferable for clarity as well as efficiency.