+ Reply to Thread
Results 1 to 3 of 3

If any combinations of multiple criteria met, then find average time elapsed

  1. #1
    Registered User
    Join Date
    07-03-2013
    Location
    Minneapolis
    MS-Off Ver
    Excel 2013
    Posts
    12

    If any combinations of multiple criteria met, then find average time elapsed

    Would someone be able to help me modify an existing formula or rewrite it so that if any combination of the multiple criteria are met, it will find the average time elapsed?

    If "Non-Urgent" in column A and "Approved" in column E or if "Non-Urgent" in column A and "Denied" in column E, then subtract Final Date/Time in column D from Initial Date/Time in column B then divide by the number of Responses that are "Approved" or "Denied" in column E.

    The calculation I have now does not return the correct anwer of 0:30:
    SUMPRODUCT(--('Detail Information'!G2:G1000="Approved")+('Detail Information'!G2:G1000="Denied"),('Detail Information'!F2:F1000-'Detail Information'!E2:E1000))/SUM(COUNTIF('Detail Information'!G2:G1000,{"Approved","Denied"}))
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: If any combinations of multiple criteria met, then find average time elapsed

    you are missing the 'non-urgent' bit

    With your sample try this
    =SUMPRODUCT(--(A2:A1000="Non-urgent"),('Detail Information'!E2:E1000="Approved")+('Detail Information'!E2:E1000="Denied"),('Detail Information'!D2:D1000-'Detail Information'!B2:B1000))/SUM(COUNTIFS(A2:A1000,"Non-Urgent",'Detail Information'!E2:E1000,{"Approved","Denied"}))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    07-03-2013
    Location
    Minneapolis
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: If any combinations of multiple criteria met, then find average time elapsed

    It works! Thanks again, this has been a 'bear' to figure out!

+ 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. [SOLVED] Sum with time elapsed criteria
    By atom29 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-29-2013, 02:55 PM
  2. Replies: 1
    Last Post: 03-12-2012, 12:09 PM
  3. Converting and calculating average elapsed time
    By jamminalley in forum Excel General
    Replies: 7
    Last Post: 03-12-2012, 10:31 AM
  4. Find elapsed time
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-03-2010, 12:43 PM
  5. elapsed time average calculcations
    By relux in forum Excel General
    Replies: 6
    Last Post: 08-26-2005, 03:05 PM

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