Rolling Multi-Field Calculation

[yuvalshmul]

Hello,

I am trying to calculate a rolling return rate over a set of data

For example, in the dummy data attached, for every column starting in week 3, I would like to calculate the rolling 2-week return rate.

• So for example, Company A, Week 3 = (Week 3) - (Week 1) / (Week 1) = (40-30)/30 = 33%
• Company D, week 6 = (week 6) - (Week 4) / (Week 4) = (6-4)/4 = 50%

I am okay with either replacing the cell with the new value, or building a new table with the rolling return rates.

however, I have to do this for over 500 companies, for over 2 years (data in weeks), and calculate a 52-week rolling average... so would be great to find a non-tedious way to do this.

Thank you very much for your help!

[Luke_C]

Hi @yuvalshmul 

I would suggest transposing the data and using a multi-row formula to perform the calculation, below is an example of this 

[RolandSchubert · Accepted solution]

Hi @yuvalshmul ,

you can use a Transpose tool to move weeks to rows, calculate the rolling return rate using a Multi-Row Formula tool and use a Cross Tab tool to move weeks back to columns.

I've attached a sample workflow, let me know if it works for you.

Best,

Roland

[yuvalshmul]

Roland, this worked! Thank you very much!