last value

  • Thread starter Thread starter Satya
  • Start date Start date
S

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

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