I have data in a worksheet that represents the monthly return on various stocks. A mock up of the data is attached and has:
A: stock name
B-M: monthly return % for Jan - Dec
I have created an array function using FVschedule that I have placed in column P:
{=FVSCHEDULE(1,OFFSET(B2:B10,ROW(B2:B10)-ROW($B$2),0,1,12))}
This calculates the annual compounded return for each stock (I compare it to the non-array calc in Col O)
I am able to calculate the average return across the portfolio manually using AVERAGE(P2:P10), but am unsure how to do this in one cell using the array.
I tried: {=AVERAGE(FVSCHEDULE(1,OFFSET(B2:B10,ROW(B2:B10)-ROW($B$2),0,1,12)))}
but get a #VALUE result. Can anyone help with what I am doing wrong? Thanks in advance.
Bookmarks