Find last value in a list

  • Thread starter Thread starter JonoB
  • Start date Start date
J

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
 
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)
 
eaieauie
haber iletisinde þunlarý said:
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)
Click to expand...
 
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
 
LOL
1) you don't like me try to put the formula into a merged cell.
Click to expand...

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