Offset formula for rolling monthly calculations

Occasional Contributor

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.

2 Replies

@egspen2 

See the attached version. It uses a helper column and INDEX.

@egspen2 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.