Value of last non-blank cell in a column

[ArtySin]

Hi,
I have a spreadsheet that has 52 rows in a particular column, one row
for each week of the year. As the year progresses the column fills up
with data but in the 53rd row of that column I want see the value of
the last cell in that column that is not blank. Can anyone assist
please?
Many thanks
ArtySin

 

[Frank Kabel]

Hi
if you have no blank lines in between use:
=OFFSET($A$1,COUNTA(A:A)-1,0)

If you could have blank rows in between try:

 

[Jason Morin]

One way:

=INDEX(A1:A52,COUNTA(A1:A52))

HTH
Jason
Atlanta, GA

 

[ArtySin]

Thanks guys,

Now works fine.
Cheers
ArtySin

One way:

=INDEX(A1:A52,COUNTA(A1:A52))

HTH
Jason
Atlanta, GA

 

[Guest]

Frank,

I found your solution when I had the same problem of needing the formula to
only select the last non-blank entry. I am curious however in the "OFFSET"
solution which did not work for me (probably because I entered it
incorrectly, since the VLOOKUP did, thanks). The COUNTA(A:A) reference is
likely for the range in question, but what does the $A$1 refer to?

Thanks,
Kem

 

[Peo Sjoblom]

A1 is the first cell offset will start from, so what it basically says is
start at A1, count how many cells are not emty in column A, offset with
that number - 1, so if A1:A8 has contents, offset will retrun what's in cell
A9 -1 which is A8

Unfortunately Frank Kabel is not with us anymore, he passed away in an
accident in early January this year

 

[Guest]

Peo,
I am very sorry to hear about Frank. Thank you for letting me know.
Could I impose upon you further regarding this thread? Normally I can parse
out a formula I find in the groups and figure them out. But the formula
=LOOKUP(2,1/(A1:A1000<>""),A1:A1000)
has me puzzled. The syntax from MS didn't even help.
But the mystery is the 1/(A1:A1000)<>"". I sought a formula that would
always return the last non-blank cell in a series of OLAP date entries (week
1,2.... This formula works and returns the last entry (after modifying the
vector to match the table). So now I am just seeking to understand why it
works? Also, why 2 as a value since the value being looked up isn't in fact
two, but any value in the last cell? Sorry about the long question. My
curiousity is getting the best of me.

Best regards,
Kem