Forum Discussion
I'm looking for Excel Formula to pull Average Weekly Sales before and after an event.
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 I'm trying to write would provide the Average weekly sales for 5 wks prior to the facility closing on 1/10/2023, then the average weekly sales 4 weeks after the facility re-opened on 2/10/23.
Hi Lou,
I think you should urgently upload a sample document here. The formula you need must be adapted to your requirements.
I think you need AVERAGEIF().4 weeks are current date - 28 and 5 weeks +35.
It is also unclear which date format you are using. m/d or d/m?Without an example it will be difficult to help.
dscheikey Thank you!! Here's a sample of the data - I have a lot of facilities to get this info for, and manually doing the averages isn't going to work well.
Have a look at my enclosed example file. I have developed the 4 different formulas. Here is the example of 1-4 Weeks After Cousure:
=ROUND(AVERAGE(FILTER($E6:$AF6,(DATEVALUE(RIGHT($E$5:$AF$5,8))>$D6+7-WEEKDAY($D6,12))*(DATEVALUE(RIGHT($E$5:$AF$5,8))<$D6+7-WEEKDAY($D6,12)+28))),2)I did not work with AVERAGEIFS() because a help line for the date would have been necessary here. So I used AVERAGE() in FILTER().
Update: BYCOL() was not necessary! I have adjusted the formulas again.
