Calculating 1 / 3 / 5 year Returns on a table that varies in length.

[Mike UK]

Hi,
I have a table of monthly performance data where each new month the previous month's performance is annotated. I'm looking to use a formula that calculates the 1, 3, 5 year returns automatically and allows for the fact that the table or performance data increases each month.

This formula works for calculating the returns of the periods using the offset. This is the 1-year calculation

=(PRODUCT(1+(OFFSET('Data'!B131,-11,0,12))/100)-1)*100

However, the row B131 is currently fixed. I need to make the row number a variable that changes each time new data is added to the table

 

[Anthony47]

And which Office version do you use?

Anyway, try replacing your OFFSET portion of the formula with

OFFSET(B1,MAX(IF(B1:B1000<>"",ROW(B1:B1000)-1,"")),0,-12,1)

Depending on your Office version, you might need to enter the formula using Contr-Shift-Enter
If you get a #REF! error then try

OFFSET(B1,-12+MAX(IF(B1:B1000<>"",ROW(B1:B1000),"")),0,12,1)

Both the formulas assumes that you have at least 12 rows of data, or you'll get an error

 

[Mike UK]

And which Office version do you use?

Anyway, try replacing your OFFSET portion of the formula with

OFFSET(B1,MAX(IF(B1:B1000<>"",ROW(B1:B1000)-1,"")),0,-12,1)

Depending on your Office version, you might need to enter the formula using Contr-Shift-Enter
If you get a #REF! error then try

OFFSET(B1,-12+MAX(IF(B1:B1000<>"",ROW(B1:B1000),"")),0,12,1)

Both the formulas assumes that you have at least 12 rows of data, or you'll get an error

Thanks Anthony, first solution worked perfectly!

 

[Anthony47]

Thank you for the feedback