Locating Last Nonzero Cell in a Row
 

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.

Thanks,
Brent


--
wilby31
------------------------------------------------------------------------
wilby31's Profile: http://www.excelforum.com/member.php...o&userid=27287
View this thread: http://www.excelforum.com/showthread...hreadid=467919

Hi!

One way:

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

=MAX(IF(A1:E10,COLUMN(A1:E1)))

Biff

"wilby31" wrote in
message ...

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.

Thanks,
Brent


--
wilby31
------------------------------------------------------------------------
wilby31's Profile:
http://www.excelforum.com/member.php...o&userid=27287
View this thread: http://www.excelforum.com/showthread...hreadid=467919

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!

In article ,
wilby31 wrote:

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.

Thanks,
Brent

"wilby31" wrote in
message ...

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

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

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


--
wilby31
------------------------------------------------------------------------
wilby31's Profile: http://www.excelforum.com/member.php...o&userid=27287
View this thread: http://www.excelforum.com/showthread...hreadid=467919

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:E10,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.