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