last value   

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

date: Fri, 3 Oct 2008 22:38:01 -0700   author:   Satya

Re: last value   

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
--- 
"Satya" wrote:
> 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

date: Sat, 4 Oct 2008 03:59:01 -0700   author:   Max

Re: last value   

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

date: Sat, 4 Oct 2008 04:04:00 -0700   author:   Max

Re: last value   

On Fri, 3 Oct 2008 22:38:01 -0700, Satya 
wrote:

>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

date: Sat, 04 Oct 2008 07:31:50 -0400   author:   Ron Rosenfeld

Re: last value   

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?
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Satya"  wrote in message
news:97F72FAB-5E51-443D-B8FF-166525BB984F@microsoft.com...
> 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

date: Sat, 4 Oct 2008 13:14:25 -0700   author:   Ragdyer