return the last value from an excel column

[rschaaf]

How can I use the last value from a column in excel for a formula in a specified cell?

I am using excel to calculate yields of investments; today's rates are added at the bottom of column X, and weekend are left blank. In Cell Y1 the current yield must be calculated.
Until now, I change the formula in cell Y1 to reflect today's value but there must be a more easy way I guess...

 

[KenWright]

Is your data in a range that doesn't have blanks? If so then you can use OFFSET() to get you what you need, eg:-Assuming you have data in the range A3:Axx and you have no blanks from row 3 to row xx, then the following will get you the last value:-

=OFFSET($A$1,COUNTA($A:$A)+1,0)

or

=INDIRECT("A"&COUNTA($A:$A)+2)

You can also try

Numeric value
=LOOKUP(9.99999999999999E+307,A:A)

or

Value
=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>""))))

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------