Find the value of the 2nd to last cell in a column that includes blank cells
Hi
I am struggling to write a formulae that will give the value of the 2nd to last cell in a column that includes blank cells. I tried
=LOOKUP(2,2/(B1:B100>0),B5:B88)
However I think that is looking halfway down the column only. I just want the value of the 2nd to last cell with a value.
Any help would be appreciated.
;o)
=INDEX(B1:B100,LARGE(IF(B1:B100<>"",ROW(B1:B100)),2))
Array formula: Press Ctrl+Shift+Enter, not just Enter
try non array
=INDEX(B1:B100,LARGE(INDEX((B1:B100<>"")*ROW(B1:B100),0),2))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hmmm, neither of these seem to work. Each gives 0.00.
well i know were are not both wrong!
Try this array formula**:
=INDEX(B:B,LARGE(IF(B5:B88<>"",ROW(B5:B88)),2))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Sorry to have doubted you Martin, and you too Mama. They are 2 working solutions. Thanks. I must have made a typo. When I get that promotion I will think of you both, before I spend my pay rise on booze.
there you all go array mad again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks