Hello,
I keep track of marketing costs for the company I work for and I'm trying to make my "pacing" formula more accurate. The issue is we spend much less on the weekends, so if I simply create a formula that averages the cost of every day and multiply it by the days left in the month, it's not as accurate as if I could factor in the different averages over the weekends. The other issue I have is that I can only figure out how to average the numbers for a full range of cells and not between 2 specific dates (beginning of month and yesterday) or month to date only. The formulas I'm using are below:
=AVERAGE(IF(WEEKDAY($A$3:$A$33)={1,2,3,4,5},$C$3:$C$33))
=AVERAGE(IF(WEEKDAY($A$3:$A$33)={6,7},$C$3:$C$33))
Where "A" is the range of dates, and C is the cost each day. I'm using 1,2,3,4,5 for M-F and 6,7 for S-S. My Weekday average is factoring in days that haven't happened yet so it's averaging in $0 for the rest of the month, which I don't want it to do. I also don't think it's very accurate for some reason. My weekend one is giving me a "divided by zero" error. Any help I can get would be great!
Bookmarks