6 Replies Latest reply: May 7, 2011 2:41 AM by KOENIG Yvan
Level 1

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
Solved by Jerrold Green1 on May 6, 2011 2:24 PM Solved

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

#### All replies

• Level 1

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.....

• Level 7

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

• Level 1

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

• Level 7

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

• Level 1

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

Thanks!!!

• Level 8

Hello.

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

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