Find the rightmost value

[jkrons]

I have some values in A1:H1. Some cells can be empty or 0. I like a
formula, that will return the rightmost number, larger than zero.
This can be in any cell in the row.

A1 B1 C1 D1 E1 F1 G1 H1
1 2 4 0 5 0

should return 5

A1 B1 C1 D1 E1 F1 G1 H1
0 1 0 0
should return 1

A1 B1 C1 D1 E1 F1 G1 H1
9 8 13 0 2

should return 2

Any ideas?

Jan

 

[Max]

One way

Array-enter* into say, K1:
=INDEX(A1:H1,MAX(IF(A1:H1>0,COLUMN(A1:H1))))
Copy down
*press CTRL+SHIFT+ENTER to confirm the formula

Any good? ring the stars in google
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Ron Rosenfeld]

I have some values in A1:H1. Some cells can be empty or 0. I like a
formula, that will return the rightmost number, larger than zero.
This can be in any cell in the row.

A1 B1 C1 D1 E1 F1 G1 H1
1 2 4 0 5 0

should return 5

A1 B1 C1 D1 E1 F1 G1 H1
0 1 0 0
should return 1

A1 B1 C1 D1 E1 F1 G1 H1
9 8 13 0 2

should return 2

Any ideas?

Jan

=LOOKUP(2,1/(1:1>0),1:1)

--ron

 

[jkrons]

Perfect. Thank you.

Jan

Array-enter* into say, K1:
=INDEX(A1:H1,MAX(IF(A1:H1>0,COLUMN(A1:H1))))
Copy down
*press CTRL+SHIFT+ENTER to confirm the formula

Any good? ring the stars in google
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik












- Vis tekst i anførselstegn -

 

[jkrons]

Shoter, and just as well. Thank you.

Jan

 

[Ron Rosenfeld]

Shoter, and just as well. Thank you.

Jan

Your welcome. Thanks for the feedback.

By the way, if your entries are limited to the first few columns in the row, as
you initially posted, (or if you wanted this lookup formula in the same row as
the data), then you could use something like:

=LOOKUP(2,1/(A1:H1>0),A1:H1)


--ron