Tales of Yip

You click on one of the cells (the boxes) in the google spreadsheet, you eager prereader you, and you see a formula shows up at the top. What is that strange, otherworldly thing? Suddenly, you click on it. Everything changes. A value that used to say “5” now says “420”. What do you do?

Well, you’d normally call Yipyapper or ChromeMyriad. Oh, but they’re he’s gone doing coding things, or they he died is off curing cancer! What do you do now, you big screw-up you?

Let’s learn how to use Google Sheets using a chronological process:

First, the information that gets sent into the submission form is pasted onto the lowest row on the “Tier One” page. You can see a timestamp, the name, etc—as a note, that =GoogleClock() thing in the timestamp? Your first formula. The equal sign (=) tells you that the cell is equal to the method—or “command”, “function”, “formula”, whatever you want to call it—which here equates to what the current time is. GoogleClock() is your first method.

From then on, queue stuff happens. You reject a fic, and it gets sent all the way to the rejected section. Here’s where some data really gets going!

On the PR Stats page, some information is fetched from the rejected stories (T1) tab. As an example, I’ll use Yipyapper’s formula, =COUNTIF('Rejected Stories (T1)'!J:J,A15) to get his number of rejected stories for the current period of time. What could all these things possibly mean?! IT’S NOT MY JOB TO FORMULA YOU CANADIAN PRI—

Eh. Anyway, you might need it for whatever reason, so here’s what each part of this line does:

=COUNTIF(X, Y) is a formula used to count how many instances of something you want to find are in a range of cells. X is the range you’re looking for, and in the case of:

=COUNTIF('Rejected Stories (T1)'!J:J,A16)

...then ‘Rejected Stories (T1)’ (the text has to match the sheet letter for letter) has a ! after it to signify that it’s fetching cells from that sheet. It then looks in the range of J:J, and if you look at the Rejected Stories (T1) tab, then it’s one column. Gee, we’re looking in the “claimed by” column! Now we have successfully set a range for the formula to derive information from. We can also expand by saying, say, J:L, which looks in columns J, K and L, but we don’t need to. J will do just fine for now.

But now we have to specify what to look for, which is what Y is for in the =COUNTIF(X, Y) statement. In Yip’s case, it says “A15”, but no other text. That means it’s in that sheet, and it’s only one cell. If you know coordinates, then you can find A15 on the PR Stats page... oh, Yipyapper is in that cell! Now you can see that it looked up that exact text and finds every instance of it in the J column inside the Rejected Stories (T1) tab. If you wanted to do this for, say, Golden Vision, you’d say A2 because that’s where his name is.

What this does is return all instances of when this condition is met, and returns a number. If my name comes up for 5 rejected stories, it will leave a value of 5. Hurray, we did formula!

But what about all that other scary stuff in the other sheets?

Well, Billy/Kyle/Bobby Joe, that’s done in a similar way. The “Passed (T1)” column in the PR Stats sheet uses countif statements, too (note that capitalization doesn’t matter. Yip likes to caps the methods and Chrome doesn’t):

=COUNTIF('Tier-Two Stories'!J:J,A6) + countif('Rejected Stories (T2)'!J:J,A6) + countif('Approved Stories'!J:J,A6) + countif('Approval Pending'!J:J,A6)

...is the command there. It may look daunting, but it’s merely just this format:

=COUNTIF(X, Y) + COUNTIF(X, Y) + COUNTIF(X, Y)

...but using very similar stuff to the one for rejected stories. What this does is look for what’s in A6, in this case ToixStory, and looks in Tier-Two stories for J column, Rejected Stories (T2) for J column and Approved stories for J column, all of which are the columns for “Passed (T1)”. Huzzah, we learned!

(Also I forgot Approval Pending in the explanation but whatever, you get the point).

So, let’s take some more looks at the formulas (which you can just type into the cell and have it happen—don’t forget the = sign though). The T2 stats are a little different!

=countif('Rejected Stories (T2)'!R:U,(A11 & "A")) for Rejected stories in T2, but this isn’t rejected stories. It’s the formula for approved stories for Kaldanor, who is in the 11th row of the PR Stats page. The only difference is the “&” sign, which is simply adding the text in the double quotation marks (“”) and adding it to A11. A11 is Kaldanor, so this equates to KaldanorA. The formula now looks in R:U columns in Rejected Stories (T2) for KaldanorA, which will return a value of every time Kaldanor approved a story but had it rejected!

Aw, it returned a number higher than 0. That means people failed what he enjoyed.

But anyway, back to the matter at hand. How does the KaldanorA actually happen inside Rejected Stories (T2)? Well, as you may have noticed, the R:U columns are not where people claimed them. The R:U ones are empty of a header. How does that work?

Well, it does get values from the K:N columns, which are where people claim the stories—you can see a similar thing happening in the Tier-Two Stories sheet if the rejected stories sheet is empty. Let’s look at the formula:

=(left(K5,search(" ",K5)-1)) & if(iserror(search("approved",K5)),if(iserror(search("rejected",K5)),if(iserror(search("pend",K5)),"","P"),"R"),"A")

...That is a doozy. Let’s break it down into chunks!

It starts off with brackets and no method. This means that the value of the cell is equal to whatever is laid out. If it said =hue, then it would return as hue. The brackets are to show that all of the stuff inside of them will equal something, and the & sign means that the thing after it will be added to that something.

In this case, it’s GarnotA we’re getting back in the Tier-Two Stories sheet. To find Garnot, we use the first side of the equation, which is (left(K5,search(" ",K5)-1)). What this does is use a left function that uses the base left(X, Y) where X is the start of what you want to get and Y is the end of what you want to get. In this case, we want X to start at G for Garnot and Y is t for the last letter in his name.

K5 is equal to X, so it starts off with G and keeps going until it has a stopping point, which is Y with search(" ",K5)-1), which can also be search(A, B). The search function looks for where the A value is, and B is where it looks as a range. Because B is K5, it looks in cell K5 for a space, as shown by “ “, the A value. You can also use “butts” or “wow”, but we don’t need those now. It finds the “ “ and returns that location for a stopping point, so now we have Garnot_ returned.

That’s where the -1 comes in. Because the Y of the left function has an -1 on it, the location of the stopping point goes one space back. Now we have Garnot. Success!

But that second part looks awfully worse than the first. Let’s deal with it in simple terms, shall we?

if(iserror(search("approved",K5)),if(iserror(search("rejected",K5)),if(iserror(search("pend",K5)),"","P"),"R"),"A")

This one looks like a killer. iserror? if? WHAT IS GOING ON—well, you see search(). That’s familiar I guess, right? It’s always nice to see familiarity. Always.

Anyway, we’ll break this into chunks. We’re trying to find out if it’s P for approval pending, R for rejected or A for approved, right? Well, if you notice, the three instances of “if” each include a section of their own. Let’s look at some syntax:

if(X, Y, Z) and iserror(A, B).

The IF() statement is possibly the most used one in all of coding—it takes an argument, X, and if it works out to be true, then Y happens. If it works out as false, then Z happens. It’s a little muddled with all of those parentheses, but the first if statement has a very large amount of words for X and then results in Z if it’s false, which is “A”. How this specific formula works is a little tricky, in that it’s a ladder of tests with ISERROR to see if any of the 3 letters at the end will happen, which are the three Z values of the if statements.

So let’s downsize it a little to simplify that complex blurb I just made. For “P” as a Z value, you have the formula IF(ISERROR(search("pend",K5)),"","P"), where iserror(search("pend",K5)) needs to return false. If it’s true, then “” happens. This returns either “P” or “” to the end of Garnot. To find out what ISERROR does, we need to break that down too (too many breakdowns for my mind):

ISERROR(X), where X checks to see if something works out fine. If it works fine and we find—using the search value—that “pend” is in the K5 cell (which is what we want to find if the prereader marked it approval pending), we get false returned, which will in turn give us “P” like we want because the Z value happens if the statement is false. If pend isn’t there, it returns “”. Then we are sad. If we don’t find “pend”, then it returns true as an error, because it’s checking to see if there is an error. Understand that? ISERROR = is there an error?

This is a boolean. You have seen integers, which are just basically whole numbers, and elsewhere you have probably seen decimals, which are doubles/floats/whatever. A boolean is a variable like it, except it only has two values, 1 and 0 (or true and false).

Anyway, back to things. So we just used an if statement, and now that you understand those two formulas, we have knowledge about the entire operation. So let’s go back to the original thing and break it down again:

if(iserror(search("approved",K5)),if(iserror(search("rejected",K5)),if(iserror(search("pend",K5)),"","P"),"R"),"A")

What this does first is check and see if “approved” is in the K5 box. If it isn’t, then ISERROR returns true—which means the Y value in if(X, Y, Z) happens, since the Y value is if the X value is true. If it’s false (so we do find “approved” and NOT get an error), we ignore all of Y and go straight to Z, which is “A”. We just got GarnotA.

We basically do the same thing for every instance we need, so the entire Y statement of the original if() function is doing the same thing—except this time, returning “R” if “rejected” is found and going to the 3rd if statement as mentioned above if there isn’t “rejected”. Note that if the search value sees one typo, it won’t find the proper word. So “rejcted” will mean that you done goofed. So spell properly. You’re a prereader, you should be able to spell “rejected”.

Got all of that? I hope you did. It was a mouthful. The only other things used in the doc are stuff like =SUM(X, Y, Z, etc) for however many times you want to add things, so if you put in =sum(B11:F11) in the PR Stats page, then you’d add up all of the stuff in cell B11, C11, D11, E11 and F11. The other thing used is =COUNTUNIQUE(X), which does the same thing as =COUNTIF except it counts how many times a unique text is shown in the range, which is X. So if the range was B:C, and there were 3 filled in cells in those two columns that say “lol”, “lel” and “lol”, then it would return as 2.

Well, I guess you know that * is multiplication, + is addition, / is division and - is subtraction. But that’s straightforward.

Hope this helped, TRG prereader! Enjoy your use of Google Sheets’ formula functions!

~Yipyapper