# I'm looking for Excel Formula to pull Average Weekly Sales before and after an event.

Copper Contributor

# 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.

3 Replies

# Re: I'm looking for Excel Formula to pull Average Weekly Sales before and after an event.

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.

# Re: I'm looking for Excel Formula to pull Average Weekly Sales before and after an event.

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

# Re: I'm looking for Excel Formula to pull Average Weekly Sales before and after an event.

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.