sorting with RANK/VLOOKUP (problem with equal ranks)
 

Hi, I'm creating a spreadsheet for sports that has a Group of 4 teams.
I've set up a RANK & VLOOKUP function in order to automatically sort
the table however I run into a couple of problems.

1. When 2 teams have the same rank based on points, the cell shows #N/A
so I need to know how to put in tie breakers. For example, a tie
breaker would be another column that has goal differential in it.

2. When no scores are entered in the system it also ranks all teams as
#1 and the same problem arises. I don't know if the same fix would
work for both.

Can anybody help with this?


--
hip
------------------------------------------------------------------------
hip's Profile: http://www.excelforum.com/member.php...o&userid=31954
View this thread: http://www.excelforum.com/showthread...hreadid=516752

sorting with RANK/VLOOKUP (problem with equal ranks)
 

=RANK(A2,$A$2:$A$22)&IF(OR(VALUE(RIGHT(RANK(A2,$A$ 2:$A$22),2))={11,12,13}),"th",IF(OR(VALUE(RIGHT(RA NK(A2,$A$2:$A$22)))={1,2,3}),CHOOSE(RIGHT(RANK(A2, $A$2:$A$22)),"st","nd","rd"),"th"))
I tried it out, if there were ties it worked out the placements
accordingly
also when I entered 0 it gave the last placing the only time I got a NA
or error was when the cell was truely blank
Hope this helps

Here's a site you can use to check out Ranking

http://xldynamic.com/source/xld.RANK.html


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=516752

sorting with RANK/VLOOKUP (problem with equal ranks)
 

Here's a play with tiebreaks which ranks 4 teams
(w/o using RANK)

Sample construct available at:
http://www.savefile.com/files/4120555
Ranking 4 Teams with tiebreaks.xls

2 options are covered:
if higher points = better,
if lower points = better

Assume the source table below is in A1:B5

Teams Points
Team1 1
Team2 2
Team3 2
Team4 4

In C2, copied down to C5:
=IF(B2="","",B2-ROW()/10^10)

Col C = Tiebreaker col for descending sort,
if higher points = better

In D2, copied down to D5:
=IF(B2="","",B2+ROW()/10^10)

Col D = Tiebreaker col for ascending sort,
if lower points = better

In G2, copied down to G5:
=IF(COUNT($B$2:$B$5)<4,"",
INDEX($A:$A,MATCH(LARGE(C:C,ROW(A1)),C:C,0)))

In H2, copied down to H5:
=IF(COUNT($B$2:$B$5)<4,"",
INDEX($A:$A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

G2:G5 will return the full Team ranking (if higher is better)
H2:H5 returns the full Team ranking (if lower is better)

Teams with tied points, if any, will appear in the same relative order
that they are listed in the source table

The Team rankings within G2:G5 & H2:H5 will only display
if there's complete entry of points for the 4 teams in B2:B5
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"hip" wrote in message
...

Hi, I'm creating a spreadsheet for sports that has a Group of 4 teams.
I've set up a RANK & VLOOKUP function in order to automatically sort
the table however I run into a couple of problems.

1. When 2 teams have the same rank based on points, the cell shows #N/A
so I need to know how to put in tie breakers. For example, a tie
breaker would be another column that has goal differential in it.

2. When no scores are entered in the system it also ranks all teams as
#1 and the same problem arises. I don't know if the same fix would
work for both.

Can anybody help with this?


--
hip
------------------------------------------------------------------------
hip's Profile:
http://www.excelforum.com/member.php...o&userid=31954
View this thread: http://www.excelforum.com/showthread...hreadid=516752