That's right, it's me, Tan, back at it again with another one of these. I bet you can't wait to see what's in store for you this time! Well, one that you probably already know about it Zac stepping down from the community manager role. He made a thread about that himself, and you'll probably be better served reading that rather than me writing about it, so go do that if you haven't already and then come back, I'll wait.
I won't wait, this has already been posted by the time you're reading it, that's how this works. I'm sorry for rusing you. That said, I will miss Zac but I understand.
Anyway, in the news that you don't already know, we are announcing two (2) new content managers. To be more accurate, one old one returning and one new one entirely. We would like to welcome Francis Ugondus back to the team, as well as welcome Sam Gladwin to it for the first time. Francis' previous work as part of the team speaks for itself, and frankly Sam has already done plenty of the kind of work that would be associated with the content manager position. Please clap.
Finally, we are a bit short on community managers currently and we will be looking to address this in the near future. Keep your eyes peeled. Not literally though, that would be rough.
What is this thread? Well, I've had two things that I've wanted to have around for a while now, but neither of them seemed particularly compelling as concepts on their own, for separate reasons. The first of these ideas was to make a thread centered around showcasing new characters or characters that have been away for a significant timeframe and familiarize them with people in a more brief form than the profile board would allow. Effectively, a quick and easy cool little intro of "Hey here's this dude I made, here's what kind of stuff they do in their matches, here's a little bit of info about them, here's a couple of pictures maybe". Easy enough, low commitment enough to justify doing it even if they're not in a fed, but also has a lot of overlap with the profile board.
The second idea was to make a thread to effectively pitch characters openly to any fed head who wants them in their fed or might have an idea for them, for cases where, for example, you can't think of what fed to stick a given character in. The worry here is and was I have no idea how much demand there'd actually be for something like this on the fed head side. I know it's something I'd potentially find useful in the future with CoFE tours and projects that aren't TANW, but I have no idea if other fedheads would feel the same way.
Then it hit me that those concepts are basically the same thing so long as you treat them as being basically the same thing, so here's a thread where you can post a few pictures and a cool little writeup about new characters or concepts you have or characters that have been out of the mix for a while to give them a little showcase and drum up interest in them, and if a fedhead likes what they see and want to do so they can make a pitch to you in private. Boom, I'm a genius.
This is very self serving because I'm going to use it a lot. That was a lot of reading, so I'll provide an actual example of what I mean below for a real reference, but feel free to do whatever, you don't have to do it the way I'm doing it.
Spoilers contain images that would stretch the page.
Hello, today I am here because I have an obsession.
I am too obsessed with automating booking spreadsheets.
Now, in response to this, you may be asking yourself such questions as "what," and "uh," and "wait what," and rest assured I will not answer any of those. Instead, I will show you the kinds of things I have learned how to automate in a booking spreadsheet and how you can do it too.
We're getting off on a weird start here because I'm starting off with something I didn't actually personally do, I got this one from Hinoa. With how many signups people are allowed in TANW, there's actually a page on the TANW document dedicated specifically to how many of a given person's slots are used. There's two, in fact, because 2K18 has a separate one to 2K17, but I digress. The 2K18 one is currently a mess because I have some weird colour coding going on relevant to which CAWs I have or need to make or need for E or what have you, so I'm going to use the 2K17 one for my example here.
You sure did, Hinoa. So, for those of you that don't know what the deal is with TANW's signups or just what you're looking at here in general, each person has six (6) signup slots available in total, with a twist. Three can be anything you want them to be. Two have to either be managers or members of a team. The final slot requires you to either have signups in both gender divisions or to make that sixth said signup.
You see the colours in the "And slot"? Those aren't automated, but that's how I track whether someone qualifies for the sixth signup. As in this example Def has signups in both gender divisions but Derry does not, Def's is marked green and Derry's is marked red. That's not what we're here for, I'm getting sidetracked. See those numbers on the left? Those are how many of the slots are taken up, and that's made to happen using the following script:
=6-COUNTBLANK(B#:G#) & " / 6"
Now, if you're following along at home and you've tried to put that directly into a sheet in the H column, you've probably noticed that doesn't work. The value I've replaced with # there corresponds to which row you're looking at. For example, since Def is on Row 5, in his case the B#:G# would be replaced by B5:G5. Similarly, for Derry it would be B6:G6.
"That sounds like a lot of work to set up," you might be saying to yourself. "Replacing all those values for each column is going to take forever." Well, never fear, because I'm going to show you how to make it suck significantly less. Watch out, here it comes. Take a look at this clip
Not sure what just happened? Well, lucky I'm here to explain then because I'm bloody obsessed. Google sheets, as it turns out, is pretty smart. If you copy a data string (in this case, B1:G1) and paste it in another row or column, it will automatically adjust by the number of rows or columns you moved it by. Obviously in this case I was moving it in rows so it was going from B1 to B2 to B3 etc but if I'd moved it in columns it would have also gone from B1 to C1 to D1, etc. Very handy. There are obviously cases where you won't want this, and in those cases all you have to do is hit enter before pasting so the textbox is open, at which point it will paste the raw data without adjustment.
There's also conditional formatting going on in the base document, hence Def's number is blue. I'll give a quick rundown of this since it's much simpler. When you open conditional formatting, there's a number of options relating to how the formatting works, and the one we're looking at in this case is "Text is exactly". There are three conditional formats done here. At 4 / 6 and 5 / 6, conditional formatting is applied to make the background of the field blue. At 6 / 6, it's formatted to make the background of the field red. Here's how it should look when it's set up properly (though obviously you'll likely not need it all the way down to H1007):
There's not much more to explain over here, so let me swoop over to Koffing Booking really quick where there's a very different kind of setup in place.
"Alright what the hell am I looking at this time," you're probably thinking to yourself right now. This is how the CoFE roster is laid out. Each of the white boxes would typically be coloured to represent which faction the person belongs to at a glance, but as I'm not looking to spoil anything at all on this you get some white boxes. Big sorry.
The first things that might draw your attention is that each column has 15 boxes but the options only go up to 10. This was just to give a bit more flexibility in alignments and such. Because of this, the COUNTBLANK that we used before won't work in this case. Actually, that's a slight lie. If we do =15-COUNTBLANK(B2:B16) & "/10", for example, it would work. But I don't want it to calculate based around 15 when it's centered on 10, that grinds my gears. No one else will ever notice it, but I will.
As you can probably guess by all of this, there is one.
=COUNTUNIQUE(#2:#16) & "/10"
In this case, the # is not set to represent the row, but rather the column. In these cases, they would be replaced by B, C or D depending on the column. As with the code before, this will adjust automatically when copied into a new column. The purpose of a COUNTUNIQUE function is, as you may have guessed, to count the number of unique values within a range, while the COUNTBLANK from before will take non-unique values as well. For example, if I were to put "a" in both lightweight face and lightweight tweener, it would only count it once. As a quick proof of point:
Obviously, as a result this one won't always be ideal, but for these purposes it works perfectly fine.
As for the one in the bottom left that displays 30/30, that one's pretty simple. It's the same as all the others, but instead of being, say, A2:A16 from a direct copy, it's B2:D16, thus covering all three rangesets.
That's all from the CoFE document, so let's take a look at an adjusted version of the setup on the PRIZMA document.
Let's take a look at row 1 first. Let's start with A1, specifically. Here's our calculation:
="Male: " & 30-COUNTBLANK(B2:B31)
This one's simple enough, it's just using COUNTBLANK again, only this time the & is on the other side of the argument. For I1, just replace the B in the argument with F and change "Male: " to "Female: " to get ="Female: " & 30-COUNTBLANK(F2:F31). This is the easy part. Now here comes E1, and I assure you we're still on the simple part here.
"tan i thought you said before you didnt like doing numbers that didnt correspond why are you using 60 in a 30 calculation"
first of all how dare you
Second of all I'd like to draw your attention to the note that COUNTUNIQUE only counts UNIQUE values. It won't work here because of the nebulous signup known only as RESERVE, which will appear multiple times with each needing to be taken into consideration. I tried using COUNTUNIQUE in conjunction with COUNTIF to make it work and it was just throwing errors. It would have been a flawed calculation regardless as you would've had to slip in a -1 to account for the extra one counted on the first registering as unique, and then if there are none that messes it up, plus it's just overcomplicated and, as previously established, doesn't work anyway.
Anyway, this one's basically just combining the results of the male and female ones into a singular by bumping up the number being removed from to account for the doubled range and then adding the COUNTBLANKS together. You'll need to remember how this works as we go forward. Incidentally, for right now we're going to skip row 2 and go over the much simpler row 3 which will establish what we'll be using to make the much more complicated row 2 work. So, for the data in E3, this is as follows:
I mentioned during the previous section a function called COUNTIF, and this is it. It counts all instances of the specified data value within the range and spits it out as a raw value. In the case of the screenshot, that raw value is 2 because there's one in each column. This is just using that in conjunction with the concept introduced in E1. Now that you're familiar with how COUNTIF works, I'm sorry, but it's time for A2.
This is significantly simpler than it looks at face value. All this is is three different instances of COUNTIF within the same cell. First it counts the instances of "Face", then of "Heel", then of "Tweener". They're all kept separately since that's the point. In the case of I2, it's the same but H instead of D.
However, based on what happened with E1 you can probably guess where this is going. I'm sorry.
I actually initially wrote this part as a joke but then I calculated it out and now I have it working so uhh whoops
So, what the hell is all of this? Well, B is the ELO that Carmen and Rosaline had going into TANW D. Specifically, B1 and B3 are Carmen's and B2 and B4 are Rosaline's. That's the easy part. Let's talk about C. C1 uses the calculation of:
C2 simply swaps it to B1-B2 instead of B2-B1. C3 and C4 are copies adjusted to be based on B3 and B4 instead. I'm not a mathematician, I'm not going to bother trying to explain how this all works, but that's how this works at its core. Let's move on to the win column and loss column:
In D2 and E2, this is the same but for C2 instead. In D3 and D4, however, it's slightly different as the 64 is changed for 96. That's the K factor, which is adjusted in title matches in TANW. However, there's always the question of whether it actually works, so let's check the calculator I've used traditionally:
And there you have it, it works. Repeat the process for whatever numbers you need for whatever given ELO you use and you're good. Or are you? What about multi-man matches? Let's look at tag teams first to get an idea of what we're actually doing here.
vs. A and vs. B are calculated with the (1/(1+10^((B2-B1)/400)))*100 from before, but it's split into multiples. C2 is B4-B2, for example. C3 is B4-B3. D2 is B5-B2. D5 is B3-B5. That part should hopefully be easy enough to figure out with the numbers in place, the complicated part's the next one. This is based on TANW ELO divided by 2 for sydeshow, but let's take a look at the formula first; E, the only column I have filled out, is respectively:
There's a reason I only calculated out the one that was actually canon in this case. That's a lot of data and a lot of small adjustments to make. One who gets the pin gets the 24 and 16, one who doesn't gets the 8 and 16, one who eats the pin gets the 24 and 8, one who doesn't gets the 16 and 16.
But what about non-team based multimans? I'm going to quickly touch on the one triple threat that happened in TANW as the example here, wherein Declan pinned T-Bizkit. Declan gets a K of 96 in this as 64 doesn't divide cleanly in thirds, and Bizkit and Gladwin respectively give up 64 and 32 to make that happen. Calculating the part with lots of decimals can't be done quite as cleanly here as in the tag, so I've dedicated columns specifically to certain opponents and left self matchups blank. Here's what I have:
This is... mostly accurate to what I have. I say mostly because I somehow managed to absolutely butcher T-Bizkit's ELO when I was writing it down. Sorry big man, I'll get you justice if I ever redo it all. Here's the code I'm using to get to this point in the F column (I'm assuming you can figure out C through E at this point):
Pre-show (set to be recorded on the 12th of June live at TANW SydeShow): T.T.K. vs. The Good Boys
Every match on this main show will be a first round match within the CrossSuplex tournament
-Ciaran Fawkes vs. Mark Anderson -Big Boy Gladwin vs. Snakeman -Trevor Edwards vs. Mike Emerick -Roze O. Flavio vs. Declan A'Shiya -Percy Belfast vs. ??? -FLAME MAN vs. Rohan Khanda -Lakeem vs. Tyler Thomas -Lyle Hedley vs. Brand