Find last value in a list

[JonoB]

I have a list of values named UnitPhase and I want to find the firs
value in the list that is greater than zero. I do this by using a
array formula as follows, which works perfectly:
{=MATCH(TRUE,UnitPhase>0,0)}

I now want a similar formula that does the same thing, but finds th
LAST value in the list that is greater than zero.

Any help much appreciated

 

[Peo Sjoblom]

One way

=MAX((UnitPhase>0)*(ROW(UnitPhase)))-ROW(UnitPhase)+1

array entered
with ctrl + shift & enter

=MAX((UnitPhase>0)*(ROW(UnitPhase)))

will give you the row number counted from A1

this part ROW(UnitPhase)+1

is to offset if your named range starts in any other row but the first in
case you would use

=INDEX(UnitPhase,MATCH(TRUE,UnitPhase>0,0))

to return the value, so this would work

=INDEX(UnitPhase,MAX((UnitPhase>0)*(ROW(UnitPhase)))-ROW(UnitPhase)+1)

 

[halil ozsoy]

eaieauie

One way

=MAX((UnitPhase>0)*(ROW(UnitPhase)))-ROW(UnitPhase)+1

array entered
with ctrl + shift & enter

=MAX((UnitPhase>0)*(ROW(UnitPhase)))

will give you the row number counted from A1

this part ROW(UnitPhase)+1

is to offset if your named range starts in any other row but the first in
case you would use

=INDEX(UnitPhase,MATCH(TRUE,UnitPhase>0,0))

to return the value, so this would work

=INDEX(UnitPhase,MAX((UnitPhase>0)*(ROW(UnitPhase)))-ROW(UnitPhase)+1)

 

[JonoB]

Thank you pPeo,

Absolutely perfect

 

[Guest]

thanks to everybod

it works I don't know why but hey! one thing for aynybody following you need to make sure that
1) you don't like me try to put the formula into a merged cell
2) that after entering the formula you press control alt delete to make it wor

----- JonoB > wrote: ----

Thank you pPeo

Absolutely perfect

 

[Ken Wright]

LOL

1) you don't like me try to put the formula into a merged cell.

Avoid using merged cells like the plague of you can - Format / Cells / Alignment
Tab / Horiontal / Centre across selection will give a similar appearance

2) that after entering the formula you press control alt delete to make it

work

I haven't seen the post that you refere to, but I'm guessing you meant
CTRL+SHIFT+ENTER if you are looking to array enter a formula. CTRL+ALT+DELETE
will either give you Task Manager, or take you to a logon screen in either Win
NT/2K/XP etc