Point Scoring based on Rank/Placement

I had a Multi-Event Competition that I'm trying to do the scoring for and I thought this would be pretty straight forward but I'm really struggling. I'll try and explain as best I can....

First, Gray shaded cells identify when a player was disqualified for an event. The reason for two versions of this spreadsheet is because there are events where a player scored 0 points and we have not determined officially whether people who were disqualified should receive no points or a score identical to someone who played and scored nothing but should receive points for being in the bottom bracket (e.g. See the trivia contest where several people didn't answer any questions correctly, but someone was also Disqualified and earns the same rank). So, I want to make two versions to see how this affects the final outcome of the scores.

I don't know how to make the ranks handle the tie scores. If two people tied for first place, Excel skips identifying someone as second place, and instead goes directly to third. I know this is technically accurate because it's ranking them and not sorting them by which place they got. But, in the case of the first event (Long Distance Soccer), it would be nice if Player 1 and Player 2 could easily read that they tied for second place, even though they are both technically ranked third overall.

If Placement (as opposed to rank) is not critical in determining point distribution, I'm fine with leaving the Rank column as it is and not doing some complicated formula to convert it to place values.

For each event, there should be 136 points to distribute between the players.
-- 16 players gives me... "=PLAYERS*(PLAYERS+1)/2"

Any and all help is greatly appreciated. Thanks!

Google Docs Version, but grid lines formatting makes it a little tougher to read.

***Note: I might end up wanting to make three versions of this spreadsheet, where instead of the disqualified individuals having the same points earned as someone who scored 0, I could give them (the disqualified people) a -1 score so that it would create a new rank for them below those who didn't score anything but were not disqualified.

For ranking disqualified players as if they competed but scored zero then you could use this formula in D4 copied down

=COUNTIF(B$4:B$19,"<"&B4)+(COUNTIF(B$4:B$19,B4)+1)/2

That will always give you a total of 136 no matter what the scores

I'm not sure what you expect the scores to be if you don't award points for disqualified players - assuming that 1st should still get 16 then try this version for D30 copied down

=IF(B30="",0,COUNTA(A$30:A$45)-COUNTIF(B$30:B$45,">"&B30)-(COUNTIF(B$30:B$45,B30)-1)/2)

In your example the total would be 133 because 2 are disqualified so the 3 points for 15th and 16th places aren't awarded. The more players disqualified the less total points awarded......but 1st would always be 16 (assuming no tie), 2nd 15 etc.

Originally Posted by daddylonglegs

For ranking disqualified players as if they competed but scored zero then you could use this formula in D4 copied down

=COUNTIF(B$4:B$19,"<"&B4)+(COUNTIF(B$4:B$19,B4)+1)/2

That will always give you a total of 136 no matter what the scores

I'm not sure what you expect the scores to be if you don't award points for disqualified players - assuming that 1st should still get 16 then try this version for D30 copied down

=IF(B30="",0,COUNTA(A$30:A$45)-COUNTIF(B$30:B$45,">"&B30)-(COUNTIF(B$30:B$45,B30)-1)/2)

In your example the total would be 133 because 2 are disqualified so the 3 points for 15th and 16th places aren't awarded. The more players disqualified the less total points awarded......but 1st would always be 16 (assuming no tie), 2nd 15 etc.

Thanks, daddylonglegs.

The formulas you provided seemed to work great for column D, but it's not working out too well for me when I try and use it in timed events. Since the Rank column isn't the same as the place a competitor earned, I couldn't just reference the Rank column instead of the Data column which has times instead of whole numbers. I may just be modifying the formula wrong. :/

What you did with disqualified players resulting in fewer total points being awarded (133) works.

Thank you for your speedy response!

OK, yes, instead of the scores you could just use the ranks, perhaps that would be simpler, i.e. this version in D4

=COUNTIF(C$4:C$19,">"&C4)+(COUNTIF(C$4:C$19,C4)+1)/2

This worked perfectly for the first table, thank you. I have one last question (hopefully) that I'm hoping to get some help on with this spreadsheet.

Using:
=IF(C28="",0,COUNTA(A$28:A$43)-COUNTIF(C$28:C$43,"<"&C28)-(COUNTIF(C$28:C$43,C28)-1)/2)

excel drops the scoring for the DQ'd players just like you said it would, and in the case of the Long Distance Soccer, it results in the total distributed points being 133 and DQ'd players getting 0 points -- beautiful!

But, how can I incorporate the "=A1*(A1+1)/2" (Or a variation of it) so that if a player was disqualified, it's as if the player wasn't even there. For example, in the case of the first event, there would only be 14 valid players instead of 16 and there would only be 105 points to distribute instead of 136 while at the same time, DQ'd players get no points.

In my suggestion the COUNTA returns 16 because it counts the names - if you want the top score to be 14 (and sum 105) then replace the COUNTA with a COUNT on the rank column, i.e.

=IF(C28="",0,COUNT(C$28:C$43)-COUNTIF(C$28:C$43,"<"&C28)-(COUNTIF(C$28:C$43,C28)-1)/2)