+ Reply to Thread
Results 1 to 8 of 8

Finding highest in a range, and assocated data

  1. #1
    Registered User
    Join Date
    06-23-2008
    Posts
    4

    Finding highest in a range, and assocated data

    Here is what i am trying to accomplish.

    I am needed to find the highest 3 values in a range of cells using LARGE. Example:

    NAME DOLLARS
    ________________
    Pat 2112.00
    Rick 114.00
    Greg 1889.00

    Now, I would like to populate on another sheet with the top 3 being displayed in order. Example:

    NAME DOLLARS
    ________________
    Pat 2112.00
    Greg 1889.00
    Rick 114.00

    Now the problem i am having is that I can easily populate the dollars collum however, how to i take the data from the Name collumn associated with the their respective dollars?

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

    =Index(A:A,Match(Large(B:B,1),B:B,0))

    where column A contains names and column B values.

    adjust ranges to suit
    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
    06-23-2008
    Posts
    4
    I am getting a number error. Could it be because the Dollar values are in currency format?

    also, here is what i have exactly

    =INDEX(Recognition!A14:A26, MATCH(LARGE(Recognition!C14:C26,1),Recognition!A14:A26,0))

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

    =INDEX(Recognition!A14:A26, MATCH(LARGE(Recognition!C14:C26,1),Recognition!C14:C26,0))

  5. #5
    Registered User
    Join Date
    06-23-2008
    Posts
    4
    Thank you a ton for that. I am now running into another problem.

    I run a computer repair shop and i have about 14 employees. I am tracking their computers completed, different services we sell, how much revenue they have generated in different categories and what not.

    I am trying to create a "Top Dog" program to where I can see who the top 3 are in a bunch of categories. However, There will be times to where 2 or more people will have the same stats. Say for instance 2 people generated the same revenue. When that happens, the person with the lower cell number (i.e. A1 and A5) will get entered in as #1 and #2.

    Is there any way to avoid that?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Assuming you have the Initial largest 3 values listed in say C1, C2 and C3 of your summary sheet...

    Try:

    =IF(C1<>"",INDEX(Recognition!$A$14:$A$26,SMALL(IF(Recognition!$C$14:$C$26=C1,ROW(Recognition!$C$14:$C$26)-ROW(Recognition!$C$14)+1),COUNTIF($C1:C$1,C1))),"")


    Replace all red C1 with actual cell you began listing the largest 3 values.


    confirm with CTRL+SHIFT+ENTER not just ENTER.

    Then copy down.

  7. #7
    Registered User
    Join Date
    06-23-2008
    Posts
    4
    Sorry for the very delayed reply, but I am uncertain as to where to put that formula

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    I would put it adjacent to the place you have the actual 3 largest values listed.

    In my formula, I assumed you have the list in C1, C2 and C3, so I would put the formula in, say, D1, confirm it with CSE key combo and copy down to D3.

+ 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