last not- ‘blank’ row? (2k but prob all)

Topic

New Reply

I8:I13 contain formulae of the type (in I9)
=IF(E9=””,””,I8-E9+F9)
(that in I8 refers to an absolute reference elsewhere)
What formula can I put in eg F19 which will return the value of the last not-blank cell in the range I8:I13?
Nested IFs I suppose will do it, but are very ugly and hard to follow. And can’t be extended if the range grows to >9 rows, I think.
MAX or MIN won’t do, as non-blank values won’t necessarily follow a pattern.
Thanks in advance.

Reply | Quote

Replies

See the posts in this thread.

Reply | Quote

Thanks, John, Steve’s formula involving the jumbo-sized number does the trick for me.
(Quite why and how defeats me at present!)

Reply | Quote

It returns the content of the highest row with something numeric:

=VLOOKUP(65536,I:I,1)

will do the same (until there’s a version of Excel with more rows).

Reply | Quote

Yes, I’d figured that’s how it worked, and figured equally that the formula as offered contained a Pittsburgh Wind-Up.
But thanks, still.

Reply | Quote

Yes, I’d figured that’s how it worked, and figured equally that the formula as offered contained a Pittsburgh Wind-Up.
But thanks, still.

Reply | Quote

It returns the content of the highest row with something numeric:

=VLOOKUP(65536,I:I,1)

will do the same (until there’s a version of Excel with more rows).

Reply | Quote

Thanks, John, Steve’s formula involving the jumbo-sized number does the trick for me.
(Quite why and how defeats me at present!)

Reply | Quote

See the posts in this thread.

Reply | Quote

With the Morefunc addin,

=LASTROW(Range)

Will accept whole column references.

Reply | Quote