+ Reply to Thread
Results 1 to 8 of 8

Average Total Time specific to dates using likely sumproduct

  1. #1
    Registered User
    Join Date
    05-20-2015
    Location
    PEI
    MS-Off Ver
    2013
    Posts
    9

    Average Total Time specific to dates using likely sumproduct

    I'm having some problems figuring out the formula that will take the average of a list of values that are within a date range. Example:
    Date Total Time
    1/1/2015 5
    1/15/2015 10
    2/1/2015 5
    2/15/2015 10

    And the table I want to produce would be:
    Jan/1/2015 7.5
    Feb/1/2015 7.5
    Mar/1/2015 0

    I want just one formula that computes the value 7.5 where that value is located in the 2nd table.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Average Total Time specific to dates using likely sumproduct

    You can use pivot table for that kind of work.

    Since you don't add an excel file, I can't show it to you.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    05-20-2015
    Location
    PEI
    MS-Off Ver
    2013
    Posts
    9

    Re: Average Total Time specific to dates using likely sumproduct

    I know I can use a pivot table, and I'm even aware of how to do it. However I hate using pivot tables for a variety of reasons which is why I'm trying to generate it by hand.

    I can only provide dummy data as my company has a non disclosure on me. Normally I would have just attached the sheet.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Average Total Time specific to dates using likely sumproduct

    Use this, then. I have assumed that if a month has no data that a zero value is returned (as suggested by post 1).
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Average Total Time specific to dates using likely sumproduct

    Lookup AVERAGEIFS function in help.

    average_range is the entire "Total Time" column
    criteria_range1 is the entire "Date" column
    criteria1 is ">="&xx, where xx is the reference for the cell containing "Jan/1/2015" (needs to be Excel date serial encoded, not text)
    criteria_range2 is the entire "Date" column
    criteria2 is "<="&EOMONTH(xx,0)], where xx is the reference for the cell containing "Jan/1/2015" (needs to be Excel date serial encoded, not text)

    Copy down.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Average Total Time specific to dates using likely sumproduct

    using Glenn's file, in green the result.

  7. #7
    Registered User
    Join Date
    05-20-2015
    Location
    PEI
    MS-Off Ver
    2013
    Posts
    9

    Re: Average Total Time specific to dates using likely sumproduct

    Thanks that solved it. I'll figure out how to add solved to the thread. Surprised I got stuck on it honestly.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Average Total Time specific to dates using likely sumproduct

    Assuming that your comment was intended for me... glad to have helped! If not, glad you got your problem solved.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

+ 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. Formula or VBA Code to calculate the total and average time
    By vutienhp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2015, 11:30 AM
  2. Trying to Total Dates using SUMPRODUCT
    By mtnovak2 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-22-2012, 12:07 PM
  3. SUMPRODUCT to total dates from multiple worksheets
    By mtnovak2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 10:58 AM
  4. Replies: 3
    Last Post: 09-02-2011, 04:03 PM
  5. Average for specific dates of gap time.
    By rvancura in forum Excel General
    Replies: 5
    Last Post: 03-17-2009, 08:00 AM

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