Rick Rothstein;479208 Wrote:
Maybe this array-entered** formula....

=INDEX(B1:B25,MAX(ROW(B12:B25)*(B12:B25<"")*(B12: B25<0)))

**commit formula using Ctrl+Shift+Enter, not just Enter by itself

--
Rick (MVP - Excel)


"LewBoy" wrote in message
...

Hello, I've just joined, so this is my first post. I'm trying to
return
the contents of the last non-zero/non-blank cell in a column...this
returns the first,=INDEX(B12:B23,MATCH(TRUE,B12:B23<0,0)) (array
function), can someone help me to find the function to return the
last
cell please? I have looked, but all my attempts have failed, like
this

one...=INDEX(B12:B23,(MAX(INDEX((B12:B23<0)*ROW(B 12:B23),1,0))-ROW(B12:B23)+1))
(non array).

Thanks in advance

LewBoy


--
LewBoy

------------------------------------------------------------------------
LewBoy's Profile: 'The Code Cage Forums - View Profile: LewBoy'
(http://www.thecodecage.com/forumz/member.php?userid=801)
View this thread:
' How to calculate the number of non-zero cells in range? - The
Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=122328)


OH......MY.......GOSH!!!!! That's fantastic! Thanks ever so much. Is
there any chance of getting a brief explanation of what's going on
there? I've limited knowledge of Excel functions, but always willing to
know more.
Thanks again.
LewBoy


--
LewBoy
------------------------------------------------------------------------
LewBoy's Profile: http://www.thecodecage.com/forumz/member.php?userid=801
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122328