Clean0nion's Portfolio - 10 cards

[CleanOnion]

Clean0nion's Card Art Portfolio

A bit about me

Welcome to my portfolio. I think that's all you need to know, really. You only came here to look at the art, or to get rid of the pesky notification in "Recent Unread Topics". Or maybe you came from Blab. I won't judge you. You can even hit Ignore Topic if you want; I won't judge you for that either. Have a snazztastic day.

Post-Crucible Cards

Tacks| Spikes

OdinVanguard made the art for Spikes. It has been included in this portfolio for completion purposes.


Unexploded Atomic Bomb | Primed Nuclear Warhead



Gadget Sword | Gadget Shield

Submitted by ZephyrPhantom


Armory | Armory

Submitted by Naesala

Submitted Cards That Are Too Bad To Make It Into Crucible

Cenrtiole | Meristem



Cards That Aren't Even Submitted

Box of X | Box of X Spoiler for Series: Match Box | Match Box Refridgerator | Fridge-Freezer Light Box | Light Box Monolith | Monolith Mirrored Box| Mirrored Box

Tom Riddle | Lord Voldemort

Kindly check post for context

[CleanOnion]

This is my portfolio so I'm going to be using it as an actual portfolio. Screw the rules.

CleanOnion's Portfolio of Spreadsheet Formulas

Spoiler for Hidden:

Spoiler for The RAID 2:

=IF((B3+J3-L3)>P3,P3,IF((B3+J3-L3)<0,0,B3+J3-L3))
To generate Health For Next Round

=sum(iferror(INDEX(R1!G:G,MATCH(A5,R1!A:A,0),0),0),iferror(INDEX(R2!G:G,MATCH(A5,R2!A:A,0),0),0),iferror(INDEX(R3!G:G,MATCH(A5,R3!A:A,0),0),0),iferror(INDEX(R4!G:G,MATCH(A5,R4!A:A,0),0),0),iferror(INDEX(R5!G:G,MATCH(A5,R5!A:A,0),0),0),iferror(INDEX(R6!G:G,MATCH(A5,R6!A:A,0),0),0),iferror(INDEX(R7!G:G,MATCH(A5,R7!A:A,0),0),0),iferror(INDEX(R8!G:G,MATCH(A5,R8!A:A,0),0),0),iferror(INDEX(R9!G:G,MATCH(A5,R9!A:A,0),0),0),iferror(INDEX(R10!G:G,MATCH(A5,R10!A:A,0),0),0),iferror(INDEX(R11!G:G,MATCH(A5,R11!A:A,0),0),0),iferror(INDEX(R12!G:G,MATCH(A5,R12!A:A,0),0),0),iferror(INDEX(R13!G:G,MATCH(A5,R13!A:A,0),0),0),iferror(INDEX(R14!G:G,MATCH(A5,R14!A:A,0),0),0),iferror(INDEX(R15!G:G,MATCH(A5,R15!A:A,0),0),0),iferror(INDEX(R16!G:G,MATCH(A5,R16!A:A,0),0),0),iferror(INDEX(R17!G:G,MATCH(A5,R17!A:A,0),0),0),iferror(INDEX(R18!G:G,MATCH(A5,R18!A:A,0),0),0),iferror(INDEX(R19!G:G,MATCH(A5,R19!A:A,0),0),0),iferror(INDEX(R20!G:G,MATCH(A5,R20!A:A,0),0),0),iferror(INDEX(R1!H:H,MATCH(A5,R1!A:A,0),0),0),iferror(INDEX(R2!H:H,MATCH(A5,R2!A:A,0),0),0),iferror(INDEX(R3!H:H,MATCH(A5,R3!A:A,0),0),0),iferror(INDEX(R4!H:H,MATCH(A5,R4!A:A,0),0),0),iferror(INDEX(R5!H:H,MATCH(A5,R5!A:A,0),0),0),iferror(INDEX(R6!H:H,MATCH(A5,R6!A:A,0),0),0),iferror(INDEX(R7!H:H,MATCH(A5,R7!A:A,0),0),0),iferror(INDEX(R8!H:H,MATCH(A5,R8!A:A,0),0),0),iferror(INDEX(R9!H:H,MATCH(A5,R9!A:A,0),0),0),iferror(INDEX(R10!H:H,MATCH(A5,R10!A:A,0),0),0),iferror(INDEX(R11!H:H,MATCH(A5,R11!A:A,0),0),0),iferror(INDEX(R12!H:H,MATCH(A5,R12!A:A,0),0),0),iferror(INDEX(R13!H:H,MATCH(A5,R13!A:A,0),0),0),iferror(INDEX(R14!H:H,MATCH(A5,R14!A:A,0),0),0),iferror(INDEX(R15!H:H,MATCH(A5,R15!A:A,0),0),0),iferror(INDEX(R16!H:H,MATCH(A5,R16!A:A,0),0),0),iferror(INDEX(R17!H:H,MATCH(A5,R17!A:A,0),0),0),iferror(INDEX(R18!H:H,MATCH(A5,R18!A:A,0),0),0),iferror(INDEX(R19!H:H,MATCH(A5,R19!A:A,0),0),0))
To generate Total Damage Dealt Per Player

=if(or(iferror(INDEX(R1!$B:$B,MATCH($A5,R1!$A:$A,0),0),1)=0,iferror(INDEX(R2!$B:$B,MATCH($A5,R2!$A:$A,0),0),1)=0,iferror(INDEX(R3!$B:$B,MATCH($A5,R3!$A:$A,0),0),1)=0,iferror(INDEX(R4!$B:$B,MATCH($A5,R4!$A:$A,0),0),1)=0,iferror(INDEX(R5!$B:$B,MATCH($A5,R5!$A:$A,0),0),1)=0,iferror(INDEX(R6!$B:$B,MATCH($A5,R6!$A:$A,0),0),1)=0,iferror(INDEX(R7!$B:$B,MATCH($A5,R7!$A:$A,0),0),1)=0,iferror(INDEX(R8!$B:$B,MATCH($A5,R8!$A:$A,0),0),1)=0,iferror(INDEX(R9!$B:$B,MATCH($A5,R9!$A:$A,0),0),1)=0,iferror(INDEX(R10!$B:$B,MATCH($A5,R10!$A:$A,0),0),1)=0,iferror(INDEX(R11!$B:$B,MATCH($A5,R11!$A:$A,0),0),1)=0,iferror(INDEX(R12!$B:$B,MATCH($A5,R12!$A:$A,0),0),1)=0,iferror(INDEX(R13!$B:$B,MATCH($A5,R13!$A:$A,0),0),1)=0,iferror(INDEX(R14!$B:$B,MATCH($A5,R14!$A:$A,0),0),1)=0,iferror(INDEX(R15!$B:$B,MATCH($A5,R15!$A:$A,0),0),1)=0,iferror(INDEX(R16!$B:$B,MATCH($A5,R16!$A:$A,0),0),1)=0,iferror(INDEX(R17!$B:$B,MATCH($A5,R17!$A:$A,0),0),1)=0,iferror(INDEX(R18!$B:$B,MATCH($A5,R18!$A:$A,0),0),1)=0,iferror(INDEX(R19!$B:$B,MATCH($A5,R19!$A:$A,0),0),1)=0,iferror(INDEX(R20!$B:$B,MATCH($A5,R20!$A:$A,0),0),1)=0),"Y","N")
Check if a player has died

=if(or(iferror(search("swap",iferror(INDEX(R1!E:E,MATCH($A5,R1!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R2!E:E,MATCH($A5,R2!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R3!E:E,MATCH($A5,R3!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R4!E:E,MATCH($A5,R4!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R5!E:E,MATCH($A5,R5!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R6!E:E,MATCH($A5,R6!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R7!E:E,MATCH($A5,R7!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R8!E:E,MATCH($A5,R8!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R9!E:E,MATCH($A5,R9!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R10!E:E,MATCH($A5,R10!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R11!E:E,MATCH($A5,R11!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R12!E:E,MATCH($A5,R12!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R13!E:E,MATCH($A5,R13!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R14!E:E,MATCH($A5,R14!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R15!E:E,MATCH($A5,R15!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R16!E:E,MATCH($A5,R16!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R17!E:E,MATCH($A5,R17!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R18!E:E,MATCH($A5,R18!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R19!E:E,MATCH($A5,R19!$A:$A,0),0),1)),0),iferror(search("swap",iferror(INDEX(R20!E:E,MATCH($A5,R20!$A:$A,0),0),1)),0)),"Y","N")
Check if a player changed their element at any point

Spoiler for Brawl #5:

Spoiler for Auction:

=0=if(index(indirect("'Highest Bids'!$C$2:$C"), match(indirect("$C"&row()),indirect("'Highest Bids'!$B$2:$B"),0))=indirect("$B"&row()),1,0)
Conditional formatting to make names go red

=query('Form responses 1'!B1:D, " select max(D), C where B <> '' group by C label max(D)'Highest Bid',C 'Player' ",1)
Formula to generate columns A and B of the highest bid sheet

=index(arrayformula(Filter('Form responses 1'!B:B,'Form responses 1'!C:C=B2)), COUNTA(arrayformula(Filter('Form responses 1'!B:B,'Form responses 1'!C:C=B2))), 1)
Thanks UTA!
Formula to generate column C of highest bid sheet

=query('Highest Bids'!A2:C, "select B where C = 'A Toadally Newtral Team' and C <> '' ")
Formula to generate list of players in each team, excluding Boss

=query('Highest Bids'!A2:C, "select sum(A) where C = 'A Toadally Newtral Team' label sum(A) '' ")
Formula to generate points spent by each team

Spoiler for Standings:

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.