+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT Multiple criteria top/bottom n values

  1. #1
    Registered User
    Join Date
    12-07-2015
    Location
    Kingston, Surrey
    MS-Off Ver
    2007
    Posts
    7

    SUMPRODUCT Multiple criteria top/bottom n values

    I have been going round in circles with this for a while now....

    I have a table as follows;

    A1 ProjectDate ClientName ProjectDuration
    A2 22-Sep-15 Client 1 1:56:20
    A3 22-Sep-15 Client 2 8:33:21
    A4 22-Sep-15 Client 3 2:45:55
    A5 22-Sep-15 Client 4 16:22:01
    A6 22-Sep-15 Client 5 1:00:00
    A7 22-Sep-15 Client 6 23:43:21
    A8 30-Apr-15 Client 1 6:07:54
    A9 30-Apr-15 Client 2 8:33:21
    A10 30-Apr-15 Client 3 0:33:21
    A11 12-Feb-15 Client 6 18:00:21
    A12 12-Feb-15 Client 1 6:43:05
    A13 12-Feb-15 Client 2 9:32:02
    A14 12-Feb-15 Client 3 10:22:32
    A15 12-Feb-15 Client 4 1:22:45
    A16 12-Feb-15 Client 5 10:44:02
    A17 01-Jan-15 Client 6 4:30:00
    A18 01-Jan-15 Client 1 12:10:02

    And I want to know the average of the quickest n projects on a given date. The data columns are named ProjectDate, ClientName and ProjectDuration to avoid complicated range names in the formulas.

    Cell E1 contains the date that I am running the formula on. So say E1=22-Sep-15 for this example and I want to find the average time for the fastest 3 projects started on that date.

    My logic is;
    Step 1 get a list of all the projects on 22-Sep-15
    Step 2 add together only the fastest 3 times
    Step 3 divide the result by 3 to get the average time

    =SUMPRODUCT(SMALL((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3

    However the result is always 0

    Interestingly if I flip the requirements of the formula to "find the average of the SLOWEST 3 projects on 22-Sep-15" using the following formula;

    =SUMPRODUCT(LARGE((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3

    It works absolutely fine giving the result 16:12:54.

    It may be possible to do the same using AVERAGEIF. AVERAGEIF(ProjectDate,E1,ProjectDuration) will return the average project time of every project on 22-Sep-15 but I have not been able to make this work with the extra condition of the fastest 3 projects.

    Any help would hugely appreciated. I have been trawling the internet for a few days now trying to figure out a solution.

    Thanks.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,925

    Re: SUMPRODUCT Multiple criteria top/bottom n values

    You might find it helps to add ProjectDuration<>0

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: SUMPRODUCT Multiple criteria top/bottom n values

    Did you tried this..

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  4. #4
    Registered User
    Join Date
    12-07-2015
    Location
    Kingston, Surrey
    MS-Off Ver
    2007
    Posts
    7

    Re: SUMPRODUCT Multiple criteria top/bottom n values

    Perfect solution;

    =SUMPRODUCT(SMALL(IF(ProjectDate=E2,ProjectDuration,"a"),{1,2,3}))/3

    Thanks.

+ 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