last value

[Satya]

Help me with a function which will return the value of the last cell >0 in a
series of non contiguous cells in the same row.

for example:

D I N S X AC AH
AM
1 2 1 3 0 0 0 0
=function?

I want the last value >0 to return in AM1

Thanks in advance

 

[Max]

One way
In AM1, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(A1:AL1,MATCH(MAX((A1:AL1>0)*(COLUMN(A1:AL1))),(A1:AL1>0)*(COLUMN(A1:AL1)),0))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,500 Files:362 Subscribers:59
xdemechanik

 

[Max]

Dismiss the earlier. This will suffice, array-entered:
=INDEX(A1:AL1,MAX((A1:AL1>0)*(COLUMN(A1:AL1))))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,500 Files:362 Subscribers:59
xdemechanik
---

 

[Ron Rosenfeld]

Help me with a function which will return the value of the last cell >0 in a
series of non contiguous cells in the same row.

for example:

D I N S X AC AH
AM
1 2 1 3 0 0 0 0
=function?

I want the last value >0 to return in AM1

Thanks in advance

Normally entered:

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

will check every cell.

If you only want to check the cells in the seven columns you list above, then
one way would be to download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

and use:

=LOOKUP(2,1/ARRAY.JOIN(D1,I1,N1,S1,X1,AC1,AH1),ARRAY.JOIN(D1,I1,N1,S1,X1,AC1,AH1))
--ron

 

[Ragdyer]

Check your other post in the functions group.

It's best not to multi-post, but just stick to one thread.

There, you had a pattern of every 3rd column.
Here, your pattern is every 5th column.

What pattern are you actually looking for?