Average Weekly Sales before and after an event.

[LouBee1]

I'm looking for a excel formula that will provide average sales before and after a date when a facility was closed. In column A I have the facility location #, and in the row going across I have weekly sales data from 2022 to 2024: During the time of 1/10/2023 to 2/10/2023 the facility was down, and the formula(s) I'm trying to write would provide the Average weekly sales for 5 wks prior to the facility closing on 1/10/2023, then in another cell the average weekly sales 4 weeks after the facility re-opened on 2/10/23.

 

[hagia_sofia]

It is not clear what do you consider to be a start of the week (e.g. Sunday or Monday) and whether "before"/"after" includes the week when the facility closed/reopened or not but generally speaking test this for "before":

=LET(
weeks,SORT(SEQUENCE(5,,WEEKNUM(A37)-1,-1)),
HSTACK(weeks,DROP(REDUCE("",weeks,LAMBDA(a,b,VSTACK(a,AVERAGE(FILTER(B2:B71,WEEKNUM(+A2:A71)=b))))),1)))

 

[LouBee1]

It is not clear what do you consider to be a start of the week (e.g. Sunday or Monday) and whether "before"/"after" includes the week when the facility closed/reopened or not but generally speaking test this for "before":

=LET(
weeks,SORT(SEQUENCE(5,,WEEKNUM(A37)-1,-1)),
HSTACK(weeks,DROP(REDUCE("",weeks,LAMBDA(a,b,VSTACK(a,AVERAGE(FILTER(B2:B71,WEEKNUM(+A2:A71)=b))))),1)))

Hi and thanks for your reply! The week begins on a Monday and ends Sunday. The qualifier also would be there's a $0.00 sales in the week that includes the facility closing date.

 

[LouBee1]

Including a screen shot example of manual average formulas - instead looking for formula to save me A LOT of time...

 

[hagia_sofia]

These are the formulas to test in AH - AK (provided that there are only dates in row 5, i.e. the headers are without "Current 2023 w/e"):

=AVERAGE(CHOOSECOLS(F6:AF6,SEQUENCE(5,,XMATCH(WEEKNUM(C6,2),WEEKNUM(+F5:AF5,2))-1,-1)))

=AVERAGE(CHOOSECOLS(F6:AF6,SEQUENCE(4,,XMATCH(WEEKNUM(D6,2),WEEKNUM(+F5:AF5,2))+1,1)))

=AVERAGE(CHOOSECOLS(F6:AF6,SEQUENCE(4,,XMATCH(WEEKNUM(D6,2),WEEKNUM(+F5:AF5,2))+5,1)))

=AVERAGE(CHOOSECOLS(F6:AF6,SEQUENCE(4,,XMATCH(WEEKNUM(D6,2),WEEKNUM(+F5:AF5,2))+9,1)))