+ Reply to Thread
Results 1 to 6 of 6

Find AVERAGE of another column of unsorted list (using INDEX/MATCH ?)

  1. #1
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Question Find AVERAGE of another column of unsorted list (using INDEX/MATCH ?)

    Hi all,

    I have an ever-changing list of data with names and scores. I want to match a name to one in the list and find the average of all scores for that particular name.

    For example:

    Name Y/N Score
    Bill Y 90
    Tom N 95
    Sarah Y 72
    Tom Y 89
    David Y 90
    Bill N 87
    Sarah Y 87
    Bill Y 93
    David Y 88
    Steph Y 97
    Greg N 91

    I want a function that will search all of Bill's scores and get the average for them. I then want the cell below that one to search all of Tom's scores and give me the average...and so on...

    I've included an example spreadsheet as well.

    Any help would be greatly appreciated.

    Thank you in advance,

    Seaottr

    example.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: Find AVERAGE of another column of unsorted list (using INDEX/MATCH ?)

    Put this equation in cell D2 and copy it down.
    =averageif($A:$A,A2,$C:$C)
    Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.

    Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
    If you're happy with an answer given, please click the * under the person's name to boost their reputation.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find AVERAGE of another column of unsorted list (using INDEX/MATCH ?)

    Try this...

    E2:E7 = list of the unique names

    You can generate the list of unique names easily by using the advanced filter:

    http://contextures.com/xladvfilter01.html#FilterUR

    Enter this formula in F2 and copy down to F7:

    =AVERAGEIF(A$2:A$12,E2,C$2:C$12)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Find AVERAGE of another column of unsorted list (using INDEX/MATCH ?)

    Put this array* formula in E2:

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


    then copy down - it will return a list of unique names from column A. Then you can put this formula in F2:

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


    and copy that down to get the averages.

    *An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

    The attached workbook demonstrates the results for you.

    Hope this helps.

    Pete
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Re: Find AVERAGE of another column of unsorted list (using INDEX/MATCH ?)

    Thanks so much Craig, Tony and Pete!!!!

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find AVERAGE of another column of unsorted list (using INDEX/MATCH ?)

    You're welcome!

+ 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