Find first non-blank or non-zero in a column of data
 

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

Find first non-blank or non-zero in a column of data
 

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

Find first non-blank or non-zero in a column of data
 

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

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