Rank function

[Marc Shaw]

Here is a sample of the data that I am working with:
Skier Round 1 Round 2 Best Round
Competitor 1 109 105.5 109
Competitor 2 104 109 109
Competitor 3 107.5 107.5 107.5
Competitor 4 104.5 107 107

I am using the column "Best Round" to rank the skiers after they have
completed 2 rounds of competition. I am using the Rank function to create my
list however where the problem comes in is where 2 skiers have the same "Best
Round" I need the formula to be able to look at their other score to see
which skier has the best backup score and rank them higher. Any suggestions
would be greatly appreciated

Thanks,
Marc Shaw

 

[bb67dart]

See double ranking/tie breaking here:

http://www.cpearson.com/excel/Rank.aspx

 

[Gary''s Student]

A neat treat, Column D formula can be
=MAX(B2:C2) to give best score

In column E
=LARGE(B2:C2,2) to give the second largest score for that competitor

Sort first by column D, then by column E. For equal best scores, the
competitor with the the highest second best score will appear on the top.

 

[Bernd P]

Hello,

A general approach without the RANK function which works for numbers
and texts:
Enter into E1:F2
Composite Text Rank
=TEXT(D2,"00000.00")&TEXT(MIN(B2:C2),"00000.00") =COUNTIF($E$2:$E
$5,">"&E2)+COUNTIF($E$2:E2,E2)
and copy E2:F2 down.

Regards,
Bernd

 

[Marc Shaw]

Thanks everyone for your help. I will try these options later and see how
they work.

 

[T. Valko]

Try this (lightly tested):

=RANK(D2,D$2$5)+SUMPRODUCT(--(D2=D$2$5),--(B2+C2<B$2:B$5+C$2:C$5))

Assumes no empty cells. Each competitor has 2 scores to rank.