Sheet: Standings
∑ Role contains the sum of the points from the tasks in that row.
=sum(C4:E4,F4:H4,L4:N4,O4:Q4,U4:W4,X4:Z4)
Team Total contains the sum of all points of a team, including ∑ Role, excluding individual rounds as they are accounted for, including initial points.
=sum(K4,T4,AC4,AD4:AD9,I4,R4,AA4,AE4,AF4,AG4,AH4:AH9,AI4,C2)
The points from each round are grabbed with the following formula:
=indirect(concatenate("'Points R",concatenate(right(C$3,1),concatenate("'!E",$I$1+$A4))))
This takes the number of the Round from the title of the column and grabs a value from the corresponding "Points" sheet. The offset to determine the location of the value in said sheet is in the first column, which contains a unique ID for each role/team (along with each team's ID). These correspond directly to that role's location in each Points sheet.
Each Round column contains three columns. The first is the base points, the third is for Brawlmaster votes and winning Bonus Tasks. These must be manually entered. When they are, a "+" will appear in the central column.
=if(E4,"+","")
To calculate each team's current position, a ranking is taken of their current points
=concatenate("Current position: ",rank($AK4,$AK$4:$AK$54))
Sheet: V2P
The Votes To Points conversion table. A "0" column has been added to prevent teams with no votes scoring maximal points.
Sheet: Votes RX
Contains two columns per team. Number of votes is entered manually in the first column. The second column is used to calculate the team's voting ratio, as each team has 6 voting power.
=round(6*D2/sum(D$2:D$7),2)
Alternatively, to remove #div!0 errors and allow copypasting
=iferror(round(6*F2/sum(indirect(concatenate(mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ",column()-1,1),(round((row()+1)/6,0)*6)-4,":",mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ",column()-1,1),"$",(round((row()+1)/6,0)*6)+1))),2),0)
I neglected to add an iferror to this in favour of adding one at a later stage.
Total: Fairly obvious.
=sum(C2,E2,G2,I2,K2,M2,O2)
Sheet: Points RX
Votes: Grabs the number of votes of the task/team specified. Same order as the Votes page.
=IFERROR(CELL("contents", 'Votes R1'!P2),0)
Ah, it's the elusive iferror.
Rank: Uses a rather convenient formula to calculate the rank of each submission compared to other submissions in its task category.
=if(C2=0,0,RANK(C2,C$2:C$7))
Force-prints 0 if there are no votes.
Points: References the Rank value against Sheet V2P to grab the number of points. Two-dimensional vlookup using index-match because I'm cool
=INDEX(V2P!$A$1:$H$7,(MATCH($A$1,V2P!$A$1:$A$7,0)),(MATCH(D2,V2P!$A$1:$H$1,0)))
These values are grabbed by the initial sheet.
A duplicate sheet will work fine assuming there are 6 teams. Less than 6 is fine if you're willing to have empty spaces or modify heavily. Same again for more than 6, but you'll need to modify it a lot.
