Offset formula for rolling monthly calculations

Question

In columns AU - AY of the attached file, I am trying to use the OFFSET function (within SUM) to calculate the last 11 months, last 10 months, last 9 months, last 8 months, and last 7 months, respectively. I've done this manually in the AY column and when learning offset, the value being returned in cell AZ18 is correct but when I drag that formula down, cell AZ19 is not correct. Is OFFSET the best option here?

Thank you in advance.

hansvogelaar · Answer

egspen2&nbsp;
See the attached version. It uses a helper column and INDEX.

riny_van_eekelen · Answer

egspen2&nbsp;Not sure I understand the reason for the 44x44 matrix, but I believe you can do the calculations you describe as shown in rows 63:68. Perhaps you'll find it helpful.

Forum Discussion

Offset formula for rolling monthly calculations

Share

Resources