+ Reply to Thread
Results 1 to 4 of 4

AVERAGEIFS to average times in a range if criteria met

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    890

    AVERAGEIFS to average times in a range if criteria met

    Static table is setup in column A and B. Column A contains Products Origin and column B contains Start Time. Dropdown option in E2. When a region is selected in E2, would like AVERAGEIFS to calculate average time based on Produce Time in C2:C20, Start Time in B2:B20, and time interval range in E5 to E14. Desired result should be in F5:F14.

    Sample file attached.
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,234

    Re: AVERAGEIFS to average times in a range if criteria met

    is this what you are after
    =IFERROR(AVERAGEIFS($C$2:$C$20,$A$2:$A$20,$E$2,$B$2:$B$20,">="&E5,$B$2:$B$20,"<"&E6),"")

    so it looks at the time in cell E5 and anything greater than E5 but less that E6 is th erange of times to include in the average

    the last entry will be wrong for 2PM as no value in E15
    E15 - needed for my average of time - I guess we could just use E14 and add 1 hour
    Attached Files Attached Files
    Last edited by etaf; 12-25-2016 at 08:17 PM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    890

    Re: AVERAGEIFS to average times in a range if criteria met

    etaf: works like a charm! Desired solution. Thanks etaf for the solution.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,234

    Re: AVERAGEIFS to average times in a range if criteria met

    you are welcome, thanks for the rep

+ 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] AVERAGEIFS using two different criteria for one range
    By KomicJ in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-30-2016, 03:51 PM
  2. [SOLVED] AVERAGEIFS formula where criteria range can be either of two value
    By lostest in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-27-2015, 01:01 PM
  3. [SOLVED] Averageifs formula same range but two different criteria
    By Alphabex in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-21-2015, 05:48 PM
  4. [SOLVED] AverageIFS multiple criteria in one range
    By AndreaJean18 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-02-2015, 10:30 AM
  5. AVERAGEIFS and range for criteria?
    By MaverickBlack in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-13-2015, 05:35 AM
  6. [SOLVED] Macro for AverageIFS, with multiple criteria in the same criteria range
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 05-24-2014, 01:13 AM
  7. Using AVERAGEIFS function to average a smaller subset of criteria data
    By RJS5062 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-22-2012, 04:19 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