header image
[ # ] Last value of a data range
June 25th, 2015 under Excel

I was faced with the problem of finding the last value of a data range in Excel. Assume the range resides in the column A. Today there are 50 values in A1:A50. Over the time new values will be added and the goal is to get the most recent (the last) value.

The solution is the INDEX() function. It allows you to select a value in a range by index. Therefore, the following solves my issue if I know there won’t be more than 100 values

1
INDEX(A1:A100;COUNT(A1:A100))
INDEX(A1:A100;COUNT(A1:A100))

Otherwise

1
INDEX(A:A;COUNT(A:A))
INDEX(A:A;COUNT(A:A))
gives the answer as long as there aren’t any empty cells between the values.



Flickr
Scuba DivingEigerEiger, MönchKleine Scheidegg

View All Photos

Recent Posts
Quotes
Boyd: God says you look for excuses to be alone.
Dr. House: See, that is exactly the kind of brilliance that sounds deep, but you could say it about any person who doesn't pine for the social approval of everyone he meets - which you were cleverly able to deduce about me by not being a moron. Next time, tell God to be more specific.”
IP
[FSF Associate Member]
[FSF Associate Member]
Michael Pfeuti
Profil von Michael Pfeuti auf LinkedIn anzeigen
profile for Michael Pfeuti at Stack Overflow, Q&A for professional and enthusiast programmers