creating league tables
i have used the rank function to put an order to a list against a value.
I am now using the vlookup function to take that list and creat a league table, how ever if two values are the same (therefore have the same rank) the vlookup function is only abkle to display the first value and not the next value in the list.
How do i achieve this please.
I hope i have explained this well enough.
Regards
VLOOKUP will require that you resolve the ranking duplicates prior to using the VLOOKUP. There are several techniques for resolving ties.
Attached is a sheet where I show a couple of ways to break ties. One way just gives the higher rank to whoever is highest on the play list. The other way uses "tie-breaking" scores you provide some other source to create decimal values for consideration when the integer values (main score) resulted in ties.
Last edited by JBeaucaire; 06-23-2009 at 03:09 PM. Reason: Sheet removed...see below for latest version
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Though I loved my decimal value approach, I confess a better technique was shown to me today by Barry Houdini over on MrExcel.
http://www.mrexcel.com/forum/showthread.php?t=398269
Accordingly, I'm changing my reference sheet as it accomplishes the same thing without the need for the helper columns....
=RANK($B2,$B$2:$B$15)+SUMPRODUCT(($B$2:$B$15=$B2)*($C$2:$C$15>$C2))
Last edited by JBeaucaire; 06-23-2009 at 03:14 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks