+ Reply to Thread
Results 1 to 8 of 8

Weekday Average Cost, Month to Date Only

  1. #1
    Registered User
    Join Date
    10-09-2019
    Location
    Austin, Texas
    MS-Off Ver
    16.26
    Posts
    4

    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!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    941

    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.
    Attached Files Attached Files
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Registered User
    Join Date
    10-09-2019
    Location
    Austin, Texas
    MS-Off Ver
    16.26
    Posts
    4

    Re: Weekday Average Cost, Month to Date Only

    Hi loginjmor,

    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. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    941

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-09-2019
    Location
    Austin, Texas
    MS-Off Ver
    16.26
    Posts
    4

    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. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Weekday Average Cost, Month to Date Only

    Cool! Thanks for the Rep!

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    10,672

    Re: Weekday Average Cost, Month to Date Only

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

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

  8. #8
    Registered User
    Join Date
    10-09-2019
    Location
    Austin, Texas
    MS-Off Ver
    16.26
    Posts
    4

    Re: Weekday Average Cost, Month to Date Only

    Thanks! done

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1