Find first non-blank or non-zero in a column of data
=INDEX(A:A,MAX(IF(ISNUMBER(A1:A65535)*(A1:A65535< 0),ROW(A1:A65535))))
This is an array formula Jim, unlike the previous, so you need to commit it
with Ctrl-Shift-Enter
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"DerbyJim" wrote in message
...
Hi Bob,
Thanks for the reply... how would I alter the calc to discount zeros if my
info contained them.
i.e. if I had a zero near the end of the column, I would not want it to
return that.
Thanks again,
Jim
"Bob Phillips" wrote:
Jim,
If it is just numbers, use
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"DerbyJim" wrote in message
...
I have my data in, say row A - specifically A1:A50 - and want my
calculation
to look from the bottom of A50 to the top of the row (A1) and return
the
first non-blank OR the first non-zero within that data.
What is the best way to do this?
E.g.
A1 = 4
A2 = 3
A3 = 0
A4 = blank cell
A5 = blank cell
Answer would be 3 as it looks from the bottom.
Many thanks,
Jim
|