Calculating rolling average horozontally

[Mr Rodger]

Hi All,

I am trying to calculate the last three weeks averaged values in rows 5-6 in the attached workbook if those values are greater than zero but cannot seem to figure out how to have the average update given the current date. I have weeks and week numbers listed out in rows above but am not familiar enough with offset, match, index functions to use some other methods I saw posted here. Does anyone have suggestions for how to proceed?

rolling averages.xlsx

[ajryan88]

Hi,

The formula in B5 would be

=AVERAGE(OFFSET($C5,0,MATCH($C$2,$D$2:$AL$2,0),1,-3))

I'll break this formula down for you. If there's anything you want me to explain further, just let me know.
1. MATCH($C$2,$D$2:$AL$2,0) - this will look for the value in C2 (9) along the range D2:AL2 (week numbers), and return the column number of the 9 within the range D2:AL2. This means that column D is column 1 within this range. Week 9 is in column 24 (X), which is column 21 within the specified range, hence the MATCH formula returns 21.
2. OFFSET($C5,0,MATCH($C$2,$D$2:$AL$2,0),1,-3) - this will return a range. The reference cell is C5. The next parameter says to offset my range by 0 rows (so stay on row 5). The match formula that returned 21 indicates that the range should be offset by 21 columns, so instead of starting in column C (3), it should start in column X (21 + 3 = 24). The 1 says that the range should be 1 row tall, and the next parameter says to be 3 columns wide, but to the left, not the right, as we want to average the last 2 weeks and the current week. This OFFSET will return V5:X5.
3. AVERAGE(OFFSET($C5,0,MATCH($C$2,$D$2:$AL$2,0),1,-3)) - this will take the average of all cells in the range returned by the OFFSET function. So (25.5 + 0 + 0) / 3 = 8.5.

If I have stuffed something up here and this doesn't provide the answer that you are looking for, let me know and I'll correct the formula.

I hope this helps

[Mr Rodger]

This worked perfectly! Thank you. You have inspired me to delve into the offset and index match formulas world!

[ajryan88]

You're welcome, glad I could help.

Please don't forget to mark this thread as solved and click on the * next to my post to say thanks

Have a great day