contest ranking help needed

[gn82]

Hi,

My friends asked me to keep track of a blog contest but the whole thing seems to eat up my spare time so I was hoping somebody can lend a hand to put it together in an elegant fashion using spreadsheets.

The idea is quite simple my friends are playing a fantasy football game and they're trying to guess the results of all matches, in terms of 1,X,2, ahead of the computer actually auto-calculating them. So here's my input data and the output that I'm currently doing manually in terms of ranking:

A[matches] B[ActualResult] C[Contestent1-EstimatedResult]
a vs b 1 1
c vs d 2 1
e vs f 2 1
g vs h X 2
i vs j 1 1
k vs l X X
.. the same for each of the 5 contestants

Now I have automated the checkup mechanism with a simple IF and a SUM to see how many correct guesses everyone had each stage but I don't know how to automatically put together a ranked of contestants in OpenOffice as it is bellow:
matchStage 1

1. Contestant4 - 5points
2. Contestant5, Contestant3, Contestant2 - 4points
3. Contestant1 - 3points

Thanks in advance !

 

[shawnhet]

I believe that the following will work. I had to add a couple extra helper cells, but you can just hide those as needed(I don't need your column C though). Data in A4:C8

add the following from D4 to H4 respectively Please note that the tie break only considers the first three letters of the name: Ashley and Ashton will still tie if their scores are tied, but it would be possible to adjust the formula in D to correct for this.

D4=B4-(CODE(A4)/100+CODE(MID(A4,2,1))/10000+CODE(MID(A4,3,1))/1000000)
E4=RANK(D4,$D$4:$D$8,0)
F add ranks directly 1 to whatever starting in column 4(ie f4=1,F5=2...f8=5 for ranks 1 to 5)
G4=INDEX($A$4:$E$8,MATCH(F4,$E$4:$E$8,0),1)
H4=INDEX($A$4:$E$9,MATCH(F4,$E$4:$E$8,0),2)

And copy down for columns D,E, G,H.

Cheers,

 

[gn82]

Thanks a lot shawnhet. Your solution works perfectly.

 

[shawnhet]

You're more than welcome! Have a great day!