Creating a rolling average by using offset?

I'm not clear on where you're going 6 weeks back from, but let's just say you put whatever date you want to go back from in cell P13, would this formula work? (Change the P13 to whatever date you want to go to)

=AVERAGEIFS($D$6:$D$222,C6:C222,"<="&P13,C6:C222,">="&P13-41)

Oh wow some amazing suggestions here.

I haven't used the filter formula before.

I will attempt all of them!

Thank you Chantelle

How does this automatically move down to the following week though when I enter data for the week ending 12/02?
I am unsure how the offset knows to move down 7?
What would I be entering to trigger the formula to drop the first week and add the newest 6th week?
Apologies if this is a silly response?

I think this would work in principle i would need to add in the selection of all MONDAYS though (in the average of weekday formula)

I think this could be the simplest but I need it to return the average of all the MONDAYS in that six week block and all the TUESDAYS. I will play around trying to insert the weekday component.

Hi Greg,

I thought I was replying directly to the person but unfortunately it just did a generic reply without referencing anyone, my apologies.

I have tried all formulas and they are excellent but I am still not sure they are capturing what I need exactly.

I have reattached a spreadsheet with everyone's brilliants suggestions but they still don't seem to be able to hit it.

I like the idea of having a field in P13 which tells me how far back I need to go - I am struggling though in what formula to put in for it to automatically take a range less 41 days from the date!!!

Thanks Chantelle

I think I understand now. This formula gives all the same answers that you have. It's based on the END DATE being in cell P13, so however you want to determine the end date (6 weeks back from when), you can replace where you see P13.

This goes in cell Q6, then copy down and across:

=AVERAGE(FILTER(D$6:D$222,($C$6:$C$222<=$P$13)*($C$6:$C$222>=$P$13-44)*(WEEKDAY($C$6:$C$222)='Dash Data>>'!$D10)))

This works perfectly!!

Thank you I would have absolutely struggled to get this formula.

Thanks Glenn

I reconciled to the days and noted there was a slight difference which was fixed for F-S if I made the # 41 not 44.

You are all fantastic.

I think I need some more training on these formulas - I have never used a LET formula before combined with a filter. Just reminds you how powerful excel is as a spreadsheet.

If you ever have the time to explain this formula to me it would be amazing. I am unsure what the V is?!

Thanks Chantelle

And I think that's because of the ambiguity of PRIOR 6 weeks. The OP's answers were going back 7 weeks as well, so I assume they are looking for the current week, plus 6 weeks prior to this week. Either way, right, they'll have to adjust the 6 in your formula, or the 44 in mine.