Google docs spreadsheet formula help

Factual Questions
1

So I’m pretty much a rank novice with spreadsheets (although an experienced C programmer). I’m trying to set up a spreadsheet to do scorekeeping for a games night, and I want it to do something I don’t know how to do.

So let’s assume that the teams are listed down the left, one team per row. Maybe there are 10 teams. Then in a particular column, I’ll end up with the scores the teams achieved in a particular game. So column C might have the values “10, 8, 8, 3, 5, 5, 0” in it, meaning team 1 scored 10 points, team 2 scored 8 pointes, etc.

What I’d like to do is set up column D to give “victory points” or “ranking points” for that round, which sort the scores in column C and give each team points based on its ranking… 1 point for every team you’re ahead of, 0.5 points for every team you’re tied with. So in this case, column D should automatically be filled in with “6, 4.5, 4.5, 1, 2.5, 2.5, 0”.
Does anyone know how to do that, or can you point me in the right direction? Thanks!

2

The keyword you need is COUNTIF(). If column A is the team name and column B is the scores, and for sake of example there are seven teams, then this would go in C1:


=COUNTIF($B$1:$B$7,"<"&$B1)+(COUNTIF($B$1:$B$7,"="&$B1)-1)*0.5

Once that’s there, you can copy/paste it from C1 into C2 through C7 to fill out the table. (I’m assuming you know what the dollar signs are doing here, i.e. keeping those row and/or column references from automatically adjusting when you copy/paste. Alter as desired.)

3

Awesome! Thanks.