I am having trouble creating a ranking spreadsheet that is formed from ballots and voting

[imijetatsk]

I am creating my own "top 25" for this years college football season with a group i am in. I am trying to figure out if their is a way to create a uniform "Ballot" that people send to me with the names of each school ranked from 1-25. The creation of that ballot is not a problem.

The problem is establishing a point value for each school based on their rank from the various ballots that I can then compile without manually going through and scoring/ranking them.


For an example i will just use a single conference of teams:

Choices:

Iowa State Cyclones

Kansas Jayhawks

Kansas State Wildcats

Oklahoma Sooners

Oklahoma State Cowboys

Baylor Bears

TCU Horned Frogs

Texas Longhorns

Texas Tech Red Raiders

West Virginia Mountaineers

<colgroup><col></colgroup><tbody>
</tbody>

Each person will rank the teams #1-#10

I would ideally like to be able to copy their rankings into a master spreadsheet where it will sum up the rankings for each team.

ie.
Oklahoma got voted 1nd 8x and 2nd 2x so it gets 12 points (2x2+1x8)
Kansas State got voted 1st 2x and 2nd 8x so it has 18 points (1x2+2x8)

I know this is complicated as hell but we are supposed to have about 30 voters and its 1-25 of all the schools in College Football

 

[oldbrewer]

set 1st = 20 points 2nd = 12 points 3rd = 8 points 4th = 5 points 5th = 2 points

team 1 has 8x1 5x2 7x3 4x2 1x5 = 306
say team 2 ends up with 325 points so it is higher than team 1

each person will presumably give each team a ranking

eg

team A 3
team B 4
team C 1
team D 2

assumes only 4 teams

now add a few more rankings

team A 3 2 1 3 1
team B 4 3 4 4 2
team C 1 4 2 1 3
team D 2 1 3 2 4

it is now simple to convert to ranking points

either by multiplying every ranking by its value
or an approximation

team A average ranking = 2 = 24
team B average ranking = 3.4 = 3.4 x 6.2 = 21.08

 

[imijetatsk]

I see how that would work and will probably have to go that way. I was just hoping their was a way that they could enter the names in order and I could take that and plug it in to recognize cell A1 is Oklahoma so Oklahoma would then have x points added to its total in the master list.

 

[oldbrewer]

that would need a macro - if you are sent the list of rankings you could paste it next to the existing rankings, say in col c - the macro would then cycle thru cells(2,2)=cells(2,2) + cells(2,3) etc then delete col C