+ Reply to Thread
Results 1 to 4 of 4

Index/Aggregate formula calculates correct row but does not yield name from Index array

  1. #1
    Forum Contributor
    Join Date
    01-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    107

    Index/Aggregate formula calculates correct row but does not yield name from Index array

    I have entered the following equation in L3 of the REPORT sheet to find the player with the highest average score, but although ALT-F9 calculation key gives me 25 for the row (correct - Carver, Graham with an average score of 40) I cannot make the equation give me his name in cell L3.
    =INDEX($H:$H,AGGREGATE(15,6,ROW($H$2:$H$136)/((J$2:J$136=LARGE(J$2:J$136,ROWS($1:1)))*(COUNTIF(L$2:L2,$H$2:$H$136)=0)),1))
    Attached Files Attached Files
    Last edited by FivestarMac; 11-17-2019 at 03:02 PM.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Index/Aggregate formula calculates correct row but does not yield name from Index arra

    for L2
    =INDEX($H:$H,AGGREGATE(15,6,ROW($H$2:$H$136)/((J$2:J$136=LARGE(J$2:J$136,ROWS($1:1)))/(COUNTIF(L$1:L1,$H$2:$H$136)=0)),1))
    all names are unique. why this part COUNTIF(L$1:L1,$H$2:$H$136)=0)
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    01-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    107

    Re: Index/Aggregate formula calculates correct row but does not yield name from Index arra

    Thanks for the quick and correct reply!

  4. #4
    Forum Contributor
    Join Date
    01-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    107

    Re: Index/Aggregate formula calculates correct row but does not yield name from Index arra

    Tim, you solved the problem as I described it but, when I pull down on your equation to get the top 10 performers (a part of the problem I failed to delineate) I get duplicates...how can we adjust the equation to differentiate duplicate scores?
    Jim

+ 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. Replies: 1
    Last Post: 08-17-2019, 01:11 PM
  2. Convert INDEX MATCH AGGREGATE FORMULA to VBA code
    By samuelkmh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2019, 03:28 AM
  3. [SOLVED] Create List - Index Match or Index Aggregate or other?
    By bambamclint in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-10-2019, 01:30 PM
  4. Replies: 2
    Last Post: 12-05-2018, 07:59 AM
  5. An Aggregate and Index Formula Issue
    By francoiscj1 in forum Excel General
    Replies: 5
    Last Post: 11-19-2018, 09:08 PM
  6. [SOLVED] Formula to find label for top five values using INDEX / AGGREGATE
    By heytherejem in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2017, 06:43 AM
  7. Index, Aggregate, Row - Removing Duplicate W/ Index Help
    By adbexcel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-04-2016, 03:53 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