Last non-zero value in a vertical list of numbers

[Neil]

I want a formula that will identify the row number of the last
non-zero value in a vertical list of numbers [non-sequential, cannot
sort list and many zeros within list]

For example

Row Value
1 789
2 0
3 0
4 456
5 1234
6 23
7 345
8 0

In this case the answer is row 7

Please note I don't want to add extra columns or use macros

Thanks in advance!
Neil

 

[Frank abel]

Hi
try the following formula:

 

[Jim May]

Frank, what's with the "2" argument? Why 2?
I see that the second argument converts to either
1 or #DIV/0!, 3 arg is row number..
TIA,
Jim

Hi
try the following formula:

-----Original Message-----
I want a formula that will identify the row number of the last
non-zero value in a vertical list of numbers [non- sequential, cannot
sort list and many zeros within list]

For example

Row Value
1 789
2 0
3 0
4 456
5 1234
6 23
7 345
8 0

In this case the answer is row 7

Please note I don't want to add extra columns or use macros

Thanks in advance!
Neil
.

 

[Frank Kabel]

Hi Jim
the second parameter of this formula returns an array
consisting of 1 and zeros. If you now use LOOKUP to search
for 2 it returns the last valid number that is smaller or
equal to 2. I choose 2 just as it is larger than 1. I
could also choose 1.1 as first parameter but 2 is two
keytrokes less.
The third parameter is the return vector. And as the OP
wanted the row number he just gets this

I think Aladin Akuyek (hopefully I got his last name
correct - if not my apologies) came up with this formula
combination. You find this approach for finding the last
entry in a range. e.g.
=LOOKUP(2,1/(A1:A10000<>""),A1:A10000)
or for real blank cells
=LOOKUP(2,1/(1-ISBLANK(A1:A10000)),A1:A10000)

So just adapt the second parameter so it returns '1' for a
match or an error (#DIV/0) in case of a non match

-----Original Message-----
Frank, what's with the "2" argument? Why 2?
I see that the second argument converts to either
1 or #DIV/0!, 3 arg is row number..
TIA,
Jim

Hi
try the following formula:

-----Original Message-----
I want a formula that will identify the row number of

the
last

non-zero value in a vertical list of numbers [non- sequential, cannot
sort list and many zeros within list]

For example

Row Value
1 789
2 0
3 0
4 456
5 1234
6 23
7 345
8 0

In this case the answer is row 7

Please note I don't want to add extra columns or use macros

Thanks in advance!
Neil
.

.

 

[Neil]

CHEERS FRANK!!!

Hi
try the following formula:

-----Original Message-----
I want a formula that will identify the row number of the last
non-zero value in a vertical list of numbers [non- sequential, cannot
sort list and many zeros within list]

For example

Row Value
1 789
2 0
3 0
4 456
5 1234
6 23
7 345
8 0

In this case the answer is row 7

Please note I don't want to add extra columns or use macros

Thanks in advance!
Neil
.

 

[Jim May]

Frank, thanks for the explanation;
it's much appreciated...
Jim

Hi Jim
the second parameter of this formula returns an array
consisting of 1 and zeros. If you now use LOOKUP to search
for 2 it returns the last valid number that is smaller or
equal to 2. I choose 2 just as it is larger than 1. I
could also choose 1.1 as first parameter but 2 is two
keytrokes less.
The third parameter is the return vector. And as the OP
wanted the row number he just gets this

I think Aladin Akuyek (hopefully I got his last name
correct - if not my apologies) came up with this formula
combination. You find this approach for finding the last
entry in a range. e.g.
=LOOKUP(2,1/(A1:A10000<>""),A1:A10000)
or for real blank cells
=LOOKUP(2,1/(1-ISBLANK(A1:A10000)),A1:A10000)

So just adapt the second parameter so it returns '1' for a
match or an error (#DIV/0) in case of a non match

-----Original Message-----
Frank, what's with the "2" argument? Why 2?
I see that the second argument converts to either
1 or #DIV/0!, 3 arg is row number..
TIA,
Jim

Hi
try the following formula:
=LOOKUP(2,1/(A1:A1000<>0),ROW(A1:A100))

-----Original Message-----
I want a formula that will identify the row number of the
last
non-zero value in a vertical list of numbers [non-
sequential, cannot
sort list and many zeros within list]

For example

Row Value
1 789
2 0
3 0
4 456
5 1234
6 23
7 345
8 0

In this case the answer is row 7

Please note I don't want to add extra columns or use
macros

Thanks in advance!
Neil
.

.