# Google docs spreadsheet formula help

**URL:** <https://boards.straightdope.com/t/google-docs-spreadsheet-formula-help/709600>\
**Category:** Factual Questions\
**Created:** [January 12, 2015, 4:08am UTC](https://boards.straightdope.com/t/google-docs-spreadsheet-formula-help/709600 "2015-01-12T04:08:56Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![MaxTheVool](https://avatars.discourse-cdn.com/v4/letter/m/9fc29f/32.png) [@MaxTheVool](https://boards.straightdope.com/u/MaxTheVool)\
**Post date:** [January 12, 2015, 4:08am UTC](https://boards.straightdope.com/t/google-docs-spreadsheet-formula-help/709600/1 "2015-01-12T04:08:56Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Pasta](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@Pasta](https://boards.straightdope.com/u/Pasta)\
**Post date:** [January 12, 2015, 6:07am UTC](https://boards.straightdope.com/t/google-docs-spreadsheet-formula-help/709600/2 "2015-01-12T06:07:33Z")

</div>

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:

```auto

=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.)

---

<div class="post-metadata">

**Author:** ![MaxTheVool](https://avatars.discourse-cdn.com/v4/letter/m/9fc29f/32.png) [@MaxTheVool](https://boards.straightdope.com/u/MaxTheVool)\
**Post date:** [January 12, 2015, 4:08pm UTC](https://boards.straightdope.com/t/google-docs-spreadsheet-formula-help/709600/3 "2015-01-12T16:08:46Z")

</div>

Awesome! Thanks.