Find the rightmost value

  • Thread starter Thread starter jkrons
  • Start date Start date
J

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
 
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
 
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
Click to expand...

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

--ron
 
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 -
Click to expand...
 
Shoter, and just as well. Thank you.

Jan
Click to expand...

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
 
Back
Top