+ 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
    1,073

    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
    1,073

    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
    1,073

    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
    Office 365 v 2403
    Posts
    13,396

    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)

Similar Threads

  1. Excel VBA code to return weekday name from a date then autofilters for userdefined weekday
    By studyengineering in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2016, 03:24 PM
  2. [SOLVED] Calculate weekday end date based on sum of weekday start date and cell value
    By Rerock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2014, 09:17 AM
  3. Replies: 1
    Last Post: 06-01-2014, 09:56 PM
  4. Summary of cost for Date Ranges by Month/year
    By m_789 in forum Excel General
    Replies: 13
    Last Post: 08-22-2013, 11:13 AM
  5. Array Formula with Month, Date and Weekday - Breaking down
    By Alienontherun in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-27-2013, 04:56 AM
  6. [SOLVED] nth weekday of the the month date problem
    By Thrashman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2006, 03:15 PM
  7. Excel Date Sort Format: Weekday, Month Day, Year
    By mrsinnister in forum Excel General
    Replies: 2
    Last Post: 03-23-2006, 04:15 PM

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