finding last row of data

[kemperohlmeyer]

What is the formula to tell excel to look for the last row of data (i.e. return data of stocks) and then use that as a reference point to calculate from?

ie, find last data point and use the previous three data points from there to calculate a qtr's return?

ko

 

[Andrew Poulsom]

As a worksheet function or in VBA?

 

[kemperohlmeyer]

To be honest i will probably need it for both. If you could help it would be great.

 

[Andrew Poulsom]

This:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

will return a reference to the last non-empty cell in column A, assuming it contains numbers, and:

=SUM(OFFSET(INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),-2,0,3,1))

will sum the last 3 entries.

In VBA this:

Dim Rng As Range
Set Rng = Range("A" & Rows.Count).End(xlUp)

will create a Range object that refers to the last non-empty cell in column A on the ActiveSheet.