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 !

 

[wigi]

Not looking at the ranking, do you already have the formulas in place to sum the points per contestant?

 

[gn82]

Yeah, I have a quite ugly but efficient sum for each contestant.. so C14 is the sum of correct guesses for contestant 1, D14 for contestant 2,, and so on for all 5 contestants
C14=SUM(IF(B4=C4;1;0);IF(B5=C5;1;0);IF(B6=C6;1;0);IF(B7=C7;1;0);IF(B8=C8;1;0);IF(B9=C9;1;0);IF(B10=C10;1;0);IF(B11=C11;1;0);IF(B12=C12;1;0))

Not looking at the ranking, do you already have the formulas in place to sum the points per contestant?

 

[wigi]

Then, you can use the RANK function. See the files on its usage.
After that, you use a simple VLOOKUP function, based on the rankings 1 to 5, to get the contestants in the correct order.

 

[wigi]

By the way, your formula could be reduced to:

=SUM(--(B4:B12=C4:C12))

confirmed with Ctrl-Shift-Enter as this is an array formula.

 

[gn82]

Thanks. Your formula is elegant and works great for column B but I still have problems with the rank formula. I have no idea how to do the rank/vlookup thing to get columns C and D look exactly as bellow

A[contestants] B[score] C[chart-position] D[player]
Player1 5 1 Player1
Player2 3 1 Player4
Player3 4 2 Player3
Player4 5 3 Player2
Player5 2 4 Player5

By the way, your formula could be reduced to:

=SUM(--(B4:B12=C4:C12))

confirmed with Ctrl-Shift-Enter as this is an array formula.

 

[wigi]

Column C is just the RANK function applied on column B...

For instance

=RANK(B1,$B$1:$B$5)

 

[gn82]

Column C should be the (ascending) ordered Rank function results but the problem is that C1=RANK(B1;$B$1:$B$5) has a different output in which it skips position #2 which should be occupied by the current 3rd position.. like in the example in the post above (about columns A to D )
ColumnC (suggested rank output) >> even unordered it should be this way
1 >> 1
4 >> 3
3 >> 2
1 >> 1
5 >> 4

Column C is just the RANK function applied on column B...

For instance

=RANK(B1,$B$1:$B$5)

 

[wigi]

You will find interesting information on ranking here:

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

Please do not PM me, I do everything I can to answer as many people and topics as possible. Future PMs will not be answered. Thank you for consideration.

 

[gn82]

I managed to fix the formula for the ranking order but I could really use some help with the 2nd part of my problem: arranging the chart in a descending order for both scores and player names (desired output data)

C1=SUMPRODUCT(--(C1<$C$1:$C$5);1/COUNTIF($C$1:$C$5;$C$1:$C$5&""))+1

Input data: A[player] B[score] C[unordered rank]
Mike 50 1
Jane 30 3
Josh 40 2
Alex 30 3
Dana 20 4

Desired output data: D[rank chart] E[name chart] F[score chart]
1 Mike 50
2 Josh 40
3 Jane 30
3 Alex 30
4 Dana 20