# Weekday Average Cost, Month to Date Only

1. ## Weekday Average Cost, Month to Date Only

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!

2. ## Re: Weekday Average Cost, Month to Date Only

Hi -

I used the AVERAGEIFS() formula (although you could use AVERAGEIF() also. To make things simple, I inserted a helper column to translate the day of the week to a numeric value with MOnday as 1 and Saturday/Sunday as 6/7.

Attached is a copy of your file with the described changes.

3. ## Re: Weekday Average Cost, Month to Date Only

This definitely got me closer. The problem is, it's still averaging out the full month, including days with \$0 cost. I'm trying to limit it to only average days from the first day of the month until yesterday, or month to date. I know there is data in the file through 10/13 but the main functionality I need is for the formula to be dynamic and only calculate days that have occurred. If that makes sense...

4. ## Re: Weekday Average Cost, Month to Date Only

OK - Simple enough. Good thing I set this up using AVERAGEIFS. So, all we need to do is add another criteria saying we only want to include values greater than \$0 in the average. The first formula would then look like this:

=AVERAGEIFS(\$D\$3:\$D\$33,\$B\$3:\$B\$33,"<6",\$D\$3:\$D\$33,">0")

I have attached the updated file.

Hope this helps.

5. ## Re: Weekday Average Cost, Month to Date Only

Awesome, that last function was what I couldn't figure out. Thanks a lot! This will work.

6. ## Re: Weekday Average Cost, Month to Date Only

Cool! Thanks for the Rep!

7. ## Re: Weekday Average Cost, Month to Date Only

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

8. ## Re: Weekday Average Cost, Month to Date Only

Thanks! done

There are currently 1 users browsing this thread. (0 members and 1 guests)