Forum Discussion

LouRicchiuti's avatar
LouRicchiuti
Copper Contributor
Jun 21, 2024

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 we...
Excel
Formulas and Functions
dscheikey's avatar
dscheikey
Bronze Contributor
Jun 22, 2024

LouRicchiuti 

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.

LouRicchiuti's avatar
LouRicchiuti
Copper Contributor
Jun 22, 2024

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. 

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Jun 22, 2024

    LouRicchiuti 

     

    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.

Resources