Find last value in column >0

[ForrestGump01]

Hello all,

I'm trying to find the last value in a column that is greater than one. I've got a bunch of columns each with 110 rows of expense data. Each column is a different product type, and each row is a date of the price. Some dates have a zero for the input, as there was no charge on that date.

I'm hoping to write a formula to find the last number in the range, that is greater than zero for each column, that is to say I'm looking for the most recent price per charge. Any suggestions?

 

[SupremeDr]

Is this in VBA or just an excel formula?

 

[tyija1995]

Last value in a column greater than 1 or greater than 0? The title suggests greater than 0 but you have stated greater than one in the description.

Try this for column A anyway:
=LOOKUP(2,1/(A:A>0),A:A)

Or if you need greater than 1:
=LOOKUP(2,1/(A:A>1),A:A)

EDIT: If text underneath is skewing these formulas then try incorporate ISNUMBER in to the formula as such:

=LOOKUP(2,1/(ISNUMBER(A:A)*(A:A>0)),A:A)

Or change the range to a more suitable range instead of the entire column

 

[ForrestGump01]

Just a formula if possible!

 

[ForrestGump01]

Last value in a column greater than 1 or greater than 0? The title suggests greater than 0 but you have stated greater than one in the description.

Try this for column A anyway:
=LOOKUP(2,1/(A:A>0),A:A)

Or if you need greater than 1:
=LOOKUP(2,1/(A:A>1),A:A)

EDIT: If text underneath is skewing these formulas then try incorporate ISNUMBER in to the formula as such:

=LOOKUP(2,1/(ISNUMBER(A:A)*(A:A>0)),A:A)

Or change the range to a more suitable range instead of the entire column

Perfect! The lookup formula worked exactly as needed. Thank you.

For clarity, I was intending to say that I'm looking for values greater than zero.

Cheers!