Hi
I would be grateful for some help. Attached is a spreadsheet with two tabs, the "data" tab shows the monthly returns of three investment portfolios and the "main" tab is a summary of the performance i.e. returns ytd, 1m, 3m, 6m and 12m. The main tab uses offset for the dynamic range of dates so that the performance automatically updates each month, Cell B1.
I want to find a quick way of increasing the column references when dragging the formulae down. For example, the three month performance calculation is:
{=(PRODUCT(INDEX(Data!$B:$B,MATCH($B$1,Data!$A:$A)):INDEX(Data!$B:$B,MATCH($B$1,Data!$A:$A)-2)+1))-1}
What I want to be able to do is copy the formulae down, which increases the columns from B to D etc... I am stuck, I think it is a ROWS and COLUMNS and is probably quite simple, but need some help figuring it out..!
Thanks
Nick
Bookmarks