Number 10, and even in this BREXIT fuelled run-up to the General Election 2019, this isn't even a post actually about politics.
No... This is a post about Excel and my nature of sometimes being excessively obsessed on superfluous details. In this instance, the character sheet for a GURPS role-playing game I'm intending to run among some friends.
My wife has assured me, that this point, this one thing encapsulates and captures so much of who I am as a person that I've decided to memorialise the event of me being able to produce the number 10 in an Excel spreadsheet.
The Number 10;
Not just any number 10, and not even always the number 10. But it was the number 10 which sparked literal weeks of commentary on my mentally obsessive behaviour, although not in the true medical definition of obsessive behaviour, more of a flippant observation made about me.Prepare for preamble reasoning...
Within the GURPS mechanics and within the established core of rules, it is encouraged that players adopt and implement their own concepts. The core rules book (GURPS Basic Set) outright tells players to ignore the published rule books. With this in mind, I use GURPS to do whatever I feel fits my desired game. To that fact, I expand or invent what I want as a character sheet, and what I want as the format in which the numerical details of a character are built and dictate effect within the game.
Thanks, GURPS! I hate having to look up rules, so now I don't!
Excel-lent
It's no secret that one of my geek skills is that of being able to use Microsoft's Excel with some considerable efficiency. Generally, I can make pretty much anything happen in an excel sheet and the things I can't, I can easily adapt, learn or 'borrow' from an online excel forum, with at least some working understanding of what I'm borrowing.
And so, When it came to building out the character sheets I needed a function which did one thing looked at two sets of tables, find some information and do a little maths, and then finally show a number.
When doing this by hand, it's very easy... Look at the tables of numbers (these being the characters 'stats') add one to the other, throw in a little extra maths, adding or subtracting as required for a given stat, a little division and, voila! A character stat is born. Huzzah!
Now, it is important to remember that I've essentially just made up the format of my characters, and so, I cannot use a standard character sheet or even look up a direct reference online. What I have is what I've decided to make, and only I can do anything with it or understand anything of it. And so comes the problem of me not wanting to do each stat for each character by hand.
Each character has 53 calculated details, over 4 characters that's 212 calculations, and while I'm sure I could have done this by hand, each being nothing more than three steps of simple mathematics, Instead I decided to automate the main functions. It was quicker to automate than risk any potential recalculation if I modified anything about how I want the game to work... Which did happen... A lot.
Fair Warning: Most of this blog-post is likely VERY boring.
It's simple really...
I know I'm a dork, but when my better half quizzed me on what I'm doing and pointed at a particularly long formula, asking "well... what does that do?" I replied with: "It makes the number 10", while factually correct, I was avoiding the concept of having to explain several hours of work for no reason other than 'because I could' and leading her through the mass of bracketed formula which I understand that anyone other than me would find dull as mud.
With that in mind, I shall now go through the formula and explain it in detail explaining how I ended up with it being what it is.
If you're still reading, you will need some prior knowledge of the sheet I'm working on. For this, I present the following colour coded screenshot (you can ignore the actual details, this is more as a reference of what is where):
Over on the left, in green are the primary character stats, these dictate how good a character might be at lifting, or thinking etc...
Pretty much everywhere, showing in blue are the characters skills stats these are directly inherited from the primary stats as simple as A+B and usually then with a -1 modifier and divided by 2, this is handled with some pretty boring lookup X/Y stuff that any old Excel jock could do.
In Yellow, however, are the secondary stats. This is where the real magic happens, a second layer of dependency is introduced. These details are derived from a combination of the primary stats and a skill stat. A normal human might have had the skill stats (the blue bits) kept on a secondary sheet as a formula reference, but that just adds more stuff. So, I made the yellow bits simply reference the blue bits they share a table with, as shown by the red containing box.
Now we need full meta third-level, self-referencing, multiple displaced redundancy formula, and Excel doesn't do that natively, so I had to first become the box I that wanted to be outside of.
Normally when you need Excel to look up detail, it has to be in columns of information that can be read with a VLOOKUP or INDEX/MATCH formula. Both of which require 'simple' data columns, and my table (the red box bit) has 4 separate columns. It's BIG BRAIN time.
Here's the offending formula:
=ROUNDDOWN((INDEX($F$3:$U$8,SUMPRODUCT(($F$3:$U$8=VLOOKUP(N10,'PC stat share'!$E$3:$G$61,2,FALSE))*ROW($F$3:$U$8))-ROW($F$3:$U$8)+1,SUMPRODUCT(($F$3:$U$8=VLOOKUP(N10,'PC stat share'!$E$3:$G$61,2,FALSE))*COLUMN($F$3:$U$8))-COLUMN($F$3:$U$8)+1+3)+INDEX($A$4:$D$12,MATCH(VLOOKUP(N10,'PC stat share'!$E$3:$G$61,3,FALSE)&"*",$A$4:$A$12,0),4)-1)/2,0)
And here's what each bit means and why I want it...
At the very start is the simple ROUNDDOWN function ... this just takes the output of the following formula and crunches it into the lowest simple form of the result, so a 10.8234782 becomes: 10. It would be a little crap if my printed character sheets had nonsensical and unimportant trails of long decimal places in a game system that really only needs 'whole' numbers.
Next up, it gets a little complex. The INDEX function is now preparing the formula to look at a set of information, in the first instance here within the cell range F22 and U27 (the red box parts).
Usually INDEX/MATCH relies on a reference of detail it must read, which we have.. And then, it requires a detail of which row number and a column number to read from. The MATCH function does this very well in simple columns of details, but refuses to do anything in a situation in which the details a spread all over the place (trust me, I tried...)
And so, I turn to SUMPRODUCT... Wait, what? You see, SUMPRODUCT can produce a normal number as an output, and by abusing this as form of declared lookup reference at the start of its detail, it must operate a fact that can either be true or false. If it's true, the result declares itself as a the number 1.
With this 'truth' equation, I can determine a row number based on a known detail. Here the SUMPRODUCT looks at all of the details of the stats table (red box) as too does the INDEX function, so I'm looking for details 'in the same place'.
With this 'truth' equation, I can determine a row number based on a known detail. Here the SUMPRODUCT looks at all of the details of the stats table (red box) as too does the INDEX function, so I'm looking for details 'in the same place'.
Within the SUMPRODUCT I declare a comparison of the entire top section of the stats block (the red box) against the results of a VLOOKUP this creates the outcome of true or false, which, numerically the SUMPRODUCT resolves as either 1 or 0. Handy for later...
The VLOOKUP here is doing it's best to resolve one little detail, using the name of the stat it is calculating, determine on which skill it must base its calculation. but because only one word can be 'true' it can only create a result for the true detail.
The VLOOKUP function, from it's newly acquired information, and with the same 'true/false' declaration that it began with, now tells the ROW function in which row the true detail is from the stats table (the red box) by multiplying [true] by the required detail. Sadly, because SUMPRODUCT is not a lookup function it requires tricking into doing the job by correcting its result... Done with the second ROW function negating a sort of overflow error.
At this point the formula has worked out ONE-HALF or ONE detail required for its first INDEX function. Right now, the formula only has 'some' idea of what it is supposed to be doing, It still doesn't even have a number, let alone the number 10!!!
As the INDEX function requires a ROW and a COLUMN declaration, the same calculation from above is done once more, but this time, looking for (by way of calculating a details position as true and then spitting out the number of the column in which the detail exists.
The VLOOKUP here is doing it's best to resolve one little detail, using the name of the stat it is calculating, determine on which skill it must base its calculation. but because only one word can be 'true' it can only create a result for the true detail.
The VLOOKUP function, from it's newly acquired information, and with the same 'true/false' declaration that it began with, now tells the ROW function in which row the true detail is from the stats table (the red box) by multiplying [true] by the required detail. Sadly, because SUMPRODUCT is not a lookup function it requires tricking into doing the job by correcting its result... Done with the second ROW function negating a sort of overflow error.
At this point the formula has worked out ONE-HALF or ONE detail required for its first INDEX function. Right now, the formula only has 'some' idea of what it is supposed to be doing, It still doesn't even have a number, let alone the number 10!!!
This all boils into:
Calculate a reference number for when a known detail is equal to desired detail and multiply by oneTime for a short breather...
I'm nowhere near clever enough to do THIS, but borrowed from a source such as the one linked below:As the INDEX function requires a ROW and a COLUMN declaration, the same calculation from above is done once more, but this time, looking for (by way of calculating a details position as true and then spitting out the number of the column in which the detail exists.
Now that we have both a row and a column, the INDEX can finally do something. It looks up a number, from the stats table, that is called for as one half of a simple addition.
INDEX($F$3:$U$8,SUMPRODUCT(($F$3:$U$8=VLOOKUP(N10,'PC stat share'!$E$3:$G$61,2,FALSE))*ROW($F$3:$U$8))-ROW($F$3:$U$8)+1,SUMPRODUCT(($F$3:$U$8=VLOOKUP(N10,'PC stat share'!$E$3:$G$61,2,FALSE))*COLUMN($F$3:$U$8))-COLUMN($F$22:$U$27)+1+3)
Using the section of formula shown above, Excel can begin to calculate the characters 'Smooth Talk' stat. The reference of N10 is where that stat is shown on the sheet. And from this first and more complex half of the calculation, Excel can now find that 'Haggle' is the required skill, which has a score of 9. Thanks, Excel, you're doing a great job!
The second half of the stat calculation is far easier...
INDEX($A$4:$D$12,MATCH(VLOOKUP(N10,'PC stat share'!$E$3:$G$61,3,FALSE)&"*",$A$4:$A$12,0),4)
An INDEX/MATCH formula looks up which primary stat is required to be added and then gives the appropriate detail of that stat. In this case, it looks for the stat 'Smarts' in which this character has 9 points. Yeah, nice one Excel.. whatever...
And finally-
After much effort, Excel can now lookup (based on the name of a character's stat) what the secondary stat is calculated as, and then include that into the secondary stat calculation.Smooth talk requires the calculation of the characters 'Haggle' stat score, added to the characters 'Smarts' primary stat. Reduced by 1 and then divided by 2.
To be done by hand this is super easy; Haggle is 9, Smarts is 9, that's 18. And then -1 for 17, finally divided by 2 for a score of 8.5 which is rounded down to 8. And while I could very, very simply do this just in my head, I don't fancy doing so more than 200 times if I decide to change the requirements of a stat, or the primary statistics of a character.
Getting this calculation right took me the better part of two nights of work, and all so I could be lazy.
And it didn't even calculate the number 10 this time...
