Saturday, April 9, 2016

Building an Index Table

For those who are familiar with excel, this is fairly straight forward stuff.  As I go forward, however, it gets harder to explain what I'm doing.  I'll try to make this clear enough so that if the reader finds they're wallowing a bit, someone close should be able to help them walk through it.

At this point, we're going to make what I call the "index table."  Basically, we're just going to divide the distances we've generated into the references we've generated.

I suggest making a table that looks like this:

Table A
It has a lot of empty space, but this is just a set up.  The reader will note that there's only 1 iron reference in our whole system, all coming from Alzak.  That's interesting; all those mountains in Pon and I didn't roll a single iron reference.  Well, scarcity, right?  It would certainly make a campaign heavy on wooden weapons, particularly if we wanted to make the only iron forging located in Alzak as well (which would give the Dwarves a considerable advantage in the world); but I'm digressing.

All we want to do now is insert our distance calculations from the previous post into the above table.

We'll have to make a reduced version of the distance post's last table, by keeping only the part highlighted in orange, below.  Note below how the cell K3 equals the cell C3 - but that the reduced version has no merged cells:

Table B
The cells K3 to K11 can now be attached to Table A, above: either by copy and pasting them as values (when you go to paste, use the drop down menu by right clicking and then select "paste special" and click the "Values" button), or - if you're really clever - incorporating the right-hand side of Table B right into Table A, as shown below:

Table C
I would normally be doing this on several worksheets in one document, to save space, but here I'm arranging the format just to make it clearer to the reader.  Honest, it is best to set up one worksheet per level of table in your document - even several documents, as I keep my distance calculations completely separate from my index calculations, just for my sanity.

Very well, I'll remove the little table on the left of Table C and we'll move onto the next part, which is slightly tricky.  Each number, from cell D4 to cell AI12 needs to be divided by the distance numbers.  The way we do this is to divide D4 by C4, D5 by C4, D6 by C4 and so on, the same way for every line.

We do this by creating another complete table, a near duplicate of Table C:

Table D
We'll call these the "top half" and the "bottom half."  D16 in the bottom half equals D4 in the top half divided by C4.  By putting "$" signs in front of the C and the 4, we ensure that when we copy left from D16, all the cells above will be divided by the same distances we've inserted.

I hope that is clear.  The benefit here is that if we want to put in the distance numbers for a different market (other than Marzarbol, which is shown here), then the "TOTALS:" will change automatically without our needing to build this table again.

These totals are the "Index" for the pricing table.  I can show the reader how to build further content from the Index, but obviously I'm interested in getting readers to donate $10 to my Patreon, to have a look at my own pricing table from start to finish.  Please excuse the pitch, but I'm not quite earning enough money to see me through to managing my bills before the first of May and I can use all the help I can get (either through Patreon or through direct donations).  We had a good donation yesterday and it lifted everyone's spirits, given that we're spending a lot of time counting every penny at the moment.


  1. I just want to check this line,

    "The way we do this is to divide D4 by C4, D5 by C4, D6 by C4 and so on"

    did you mean

    'Divide D4 by C4, D5 by C5, D6 by C6'

    so that each row is divided by the distance number in the corresponding row so that all references are divided by the distance that corresponds to the settlement of the row.

  2. Yes. Another way to put it would be that everything on Row 4, from D4 to AI4, is divided by the distance to Adeese. This is then recorded on row 16. For example, "fish" at Adeese is 2 references, divided by 9 = 0.22.


If you wish to leave a comment on this blog, contact with a direct message. Comments, agreed upon by reader and author, are published every Saturday.

Note: Only a member of this blog may post a comment.