Creating formula/worksheet that tabulates points for votes

[jbryant12]

For the last few years, I've been doing a ranking involving college teams for one of the coaches associations.
Voting coaches send me their top 25 teams and I log them, and each place for each team gets a set of points.

Example
1. State U.
2. College Tech
3. Wassamatta U.
4. Jones Tech
5. Boise State

The team ranked 1 gets 25 points, the second team gets 24, the third 23 and so on until we get down to the team ranked 25, which receives one point.

I'm currently entering the votes on one sheet (just to log them) and then going through the list putting the total number of votes for each school in a column with each ranking. (Including a screen shot)

The formula I'm using is pretty simple, but is there a way to basically automate the process as to where when I type in a school and what it's place is, it generates the numbers for me?

I have the Total votes at the top and bottom more or less to keep me from screwing up. There can be no more than 10 votes per team or position.

Thoughts on how I might be able to solve this. There just seems to be a better way out there.

[BeachRock]

Put this formula in A2 and copy it down through A35. This will rank each team by their total points.

Please Login or Register  to view this content.

You can look at an example I gave earlier to someone else here.

If you need this to track more than just the one vote column, such as through a season, add to the table: Create a column for votes for each week or whatever interval. Enter the number of votes each week for each team in the appropriate column. Set the total points cells to sum the points.

Then sort the table by column A for all columns except the one for the point structure (25 to 1) to put them in order from 1 to 35.

This is untested of course. But I think the idea works or will at least get you close to what you want to do.