+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : AVERAGEIFS alternative

  1. #1
    Registered User
    Join Date
    04-08-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    6

    AVERAGEIFS alternative

    Hi,

    I'm using AVERAGEIFS to count specific data from a sheet using 3 criterea, however I also need to extract the actual data into another calculation, so instead of returning the average I want it to return the list of corisponding data against 3 selection criterea.

    ie: I have a list of members who take part in diferent activities which get a score. I want to be able to lookup the member name with a specific activity and then extract the top 10 scores from their total list of scores.

    So A1=surname;B1=forname;C1=activity type;D1=score

    Everytime a member attends they get an entry for that day, the list has multiple member name entries.

    I can obviously achieve the result by filtering then applying a calculation to the filtered result but I need it to be automated because there are a hell of alot of members.

    Any suggestion would be welcomed.

    Thanks Shaun
    Last edited by shaunk; 04-09-2009 at 10:15 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: AVERAGEIFS alternative

    =Large(IF(A1:A10="Name",D1:D10),1)

    confirmed with CTRL+SHIFT+ENTER not just ENTER extracts the Largest score for the name identified...

    =Large(IF(A1:A10="Name",D1:D10),2) confirmed with CSE keys gets 2nd largest... etc.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-08-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: AVERAGEIFS alternative

    Quote Originally Posted by NBVC View Post
    =Large(IF(A1:A10="Name",D1:D10),1)

    confirmed with CTRL+SHIFT+ENTER not just ENTER extracts the Largest score for the name identified...

    =Large(IF(A1:A10="Name",D1:D10),2) confirmed with CSE keys gets 2nd largest... etc.
    OK, so presumably I can nest multiple IF statements because I need to select on surname;forname and acivity

    rgds Shaun

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: AVERAGEIFS alternative

    Yes:

    e.g

    =LARGE(IF(A1:A10="Surname",IF(B1:B10="Forename",IF(C1:C10="Activity",D1:D10))),1)

    confirmed with CSE keys

  5. #5
    Registered User
    Join Date
    04-08-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: AVERAGEIFS alternative (SOLVED)

    EXCELLENT, Just adjusted it for my main sheet and it works perfectly, sometimes you just miss or blank on the formula.

    Thanks for your help. Shaun

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: AVERAGEIFS alternative (SOLVED)

    No problem.

    This is how you mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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