6 Replies Latest reply: May 7, 2011 2:41 AM by KOENIG Yvan
AHALEY Level 1 Level 1 (0 points)

Here is a sample of my sheet that I am having issues with.  I need to get the names of the Top 5 scores.    In the Top 5 column, I have used the =LARGE() function to grab the 1-5th place scores.  I would like to also pair that with the Name next to the score.  I know a simple sort would do the trick, however, I would prefer a formula to automate the info for me.

 

Everytime I create the vlookup formula, I get a formula error.  My formula is as follows:

 

=VLOOKUP(D2,$A$1:$B$19,A1:A19,0)    

 

The error returned is that it is finding "Anthony Cavanaugh" in value 3 where a number is expected.

 

Anthony Cavanaugh

46

Top 5

Name

Anthony Cristaldi

52

73

Carmen Torbus

36

64

Catherine Saks

29

57

Chris Justus

56

56

Chuck Gardner

56

56

Dan Torbus

44

Dave Staton

48

Dylan Byars

42

Glen Allen

57

Jeff Strickland

56

Jesse Andrus

12

Jon Andrus

64

Kevin Markowitz

48

Martin Jacobs

73

Shawn Craver

48

Warren Vorreyer

48

Wayne Edwards

55

  • AHALEY Level 1 Level 1 (0 points)

    ok.  I figured out my first issue (syntax) and have the results I am looking for.  However, As you may see, there are some duplicate rankings.  If one of my top 5 spots is shared among multiple people, is there a way to have the vlookup function provide the next person for the duplicate? 

     

    John S         56

    Bob Dole     99

    Betty D        56

    Clinton P      56

    Jane Doe     70

     

    For the Top 5 (my result)

     

    Bob Dole     99

    Jane Doe     70

    Betty D       56

    Betty D        56

    Betty D        56

     

    What I would like is:

     

    Top 5

    Bob Dole     99

    Jane Doe     70

    Betty D         56

    Clinton P     56

      -OR-

     

    if there are duplicates, return a result as a conditional format or something else.....

  • Jerrold Green1 Level 7 Level 7 (29,925 points)

    AH,

     

    I think you might like the RANK function to pick your top 5. It's not clear to me exactly how you want to handle ties, below or at the 5th rank, so I can't be specific. If all participants tied for best score you would need to accommodate as many winners as participants.

     

    Jerry

  • AHALEY Level 1 Level 1 (0 points)

    I am also using the RANK function as another route, but end up at the same place, unfortunately.  Since scores and ranks change from week to week, the VLOOKUP function appears to be the best way for me in order to not have to manually sort the sheet each time scores are updated.

     

    We are also using weekly averages to break ties, but I need to rank based on a point total as mentioned earlier, then default to weekly 4 game averages for the tie breakers.  See below: (point totals, rank column hidden)

     

     

    Total Points

    Name

    38

    Adam Haley

    46

    Anthony Cavanaugh

    52

    Anthony Cristaldi

    36

    Carmen Torbus

    29

    Catherine Saks

    56

    Chris Justus

    56

    Chuck Gardner

    44

    Dan Torbus

    48

    Dave Staton

    42

    Dylan Byars

    57

    Glen Allen

    56

    Jeff Strickland

    11

    Jesse Andrus

    64

    Jon Andrus

    48

    Kevin Markowitz

    73

    Martin Jacobs

    48

    Shawn Craver

    48

    Warren Vorreyer

    55

    Wayne Edwards

     

    Next, I have ranked these in another sheet to auto-sort for me, but come up against duplicates: (using the RANK function, then Vlookup for the name with the rank)

     

     

    PLACEMENT

    NAME

    1

    Martin Jacobs

    2

    Jon Andrus

    3

    Glen Allen

    4

    Chris Justus

    4

    Chris Justus

    4

    Chris Justus

    7

    Wayne Edwards

    8

    Anthony Cristaldi

    9

    Dave Staton

    9

    Dave Staton

    9

    Dave Staton

    9

    Dave Staton

    13

    Anthony Cavanaugh

    14

    Dan Torbus

    15

    Dylan Byars

    16

    Adam Haley

    16

    Adam Haley

    17

    Catherine Saks

    18

    Jesse Andrus

     

    and the tiebreaker will come off this sheet, where we have weekly averages

     

     


    SHARK


    Week 1

    Week 2

    Week 3

    Adam Haley

    153.25



    Anthony Cavanaugh

    165.00



    Anthony Cristaldi

    173.00



    Carmen Torbus

    152.75



    Catherine Saks

    139.75



    Chris Justus

    183.25



    Chuck Gardner

    183.50



    Dan Torbus

    166.00



    Dave Staton

    167.00



    Dylan Byars

    164.75



    Glen Allen

    186.50



    Jeff Strickland

    174.75



    Jesse Andrus

    0.00



    Jon Andrus

    201.75



    Kevin Markowitz

    168.00



    Martin Jacobs

    209.50



    Shawn Craver

    167.50



    Warren Vorreyer

    171.75



    Wayne Edwards

    180.50



     

     

    Just not sure how to handle the duplicates on that second sheet in order to apply these as the tie-breaker

  • Jerrold Green1 Level 7 Level 7 (29,925 points)

    If you already have a scheme for tie breaking, you're set to go. My favorite tie break scheme is to handle it before ranking, not after. That way you will never have to deal with a tie and you can use either LARGE or RANK without any problem.

     

    Of course you'll want to use LOOKUP no matter what other choices you make because, as you say, it prevents having to sort.

     

    Here's the tie-break scheme. Your total points score is in the 1-100 range and your weekly average is in the 0 to 1000 range. Create a tie-broken TotalPoints aux. column that consists of TotalPoints + WeeklyAverage/10,000. Now two otherwise equal TotalPoints scores will be offset by the difference in their WeeklyAverage scores, divided by 10,000. Unless their weekly averages are identical, there will be no tie. If there is still a possibility of a tie, take a third factor and add it in an even smaller fraction.

     

    Jerry

  • AHALEY Level 1 Level 1 (0 points)

    That looks perfect!!!  I'll give that a shot.

     

    Thanks!!!

  • Level 8 Level 8 (41,790 points)

    Hello.

    As I'm not hunting points, I give my two cents in a thread flagged as responded.

    2011_05_07b.jpg

    In cell C1 is the formula :

    =COUNTIF(B,">="&B1)+ROW()/100000

    In cell D1 is the formula :

    =ROW()

     

    Select columns C & D and apply Fill Down.

     

    In E2, the formula is :

    =LARGE(B,ROW()-1)

    I applied Fill Down to the entire column because I wanted to show that in this case six records are in the five best ranks.

    In F2, the formula is :

    =OFFSET($A$1,VLOOKUP(SMALL(C,ROW()-1),C:D,2)-1,0)

     

    Apply fill down to fill six rows.

    No need for sort, correct treatment of equalities.

     

    Yvan KOENIG (VALLAURIS, France) 7 mai 2011 11:41:21