Can this complex rate-of-return formula be simplified?

[Mr. Snrub]

=IF(
  ROW()-ROW(Table1[#Headers])<12,
  NA(),
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-1))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-2))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-3))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-4))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-5))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-6))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-7))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-8))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-9))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-10))*
  (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-11))-1
 )

Can this super-complex, memory-intensive formula be simplified? Basically what this does is calculate the rates of return for the current row, combined with each of the previous month's rates for the past year (so there are twelve in all). So Excel needs values of an adjacent cell plus eleven rows back. Is there an easier way to do this? I especially don't like the overuse of ROW() functions just to get the cell n-number of rows upward.

 

[Eric W]

Try this:

=IF(ROW()-ROW(Table1[#Headers])<12,NA(),PRODUCT(1+[@[Monthly RR]]:INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-11))-1)

and confirm with Control+Shift+Enter.

And I agree, one of the most frustrating things about the table nomenclature is that there's no good way to reference a row above or below without some odd ROW construct.

 

[Mr. Snrub]

That worked! Thank you!

 

[Eric W]

Happy to help!