Making a Distance Table

Okay, the next problem.  Determining the distances between our market cities.  I think what I'm going to do going forward is to put up a test run on the blog, then clean it up for the wiki.  I also think I'm going to put a disclaimer on the wiki that I'm not going to explain excel there - but that I'll put a link for occasionally explaining excel here (especially when its requested and it is practical for me to do it).

First of all, let's throw up another copy of the map, this time completed with roads:

When doing this for your world, you're going to be tempted to create roads from every market to every other market.  Resist this!  Two things to remember: it is very expensive to make a road; and not having a road between Groat and Adeese, or between Alzak and the Gathering (sorry, name got cut off, it's in the bottom right), gives meaning to the control of Pon over the area.  The limitation of trade, forcing everything through Marzarbol, creates tension in the area, gives the inhabitants something to fight over and creates adventure potential.  So be careful how easily you slap-dash those roads out.

Note also that the road intentionally bypasses Rosengg and continues on to Alzak.  We need to make up our minds if we want to force trade to go through the mid-point town or have the potential to just continue on.  These things can also create interest in the shape and tenor of the campaign's economics - how important is that crossroads, exactly?

Okay, for this next part, I'm going to need you to change the settings on your excel program.  You're looking for "excel options" - on my computer, it looks like this:

If you're using a different excel, you should be
able to find this in the help menu.

That will take you to this page - where you must choose the "Formulas" option:

You will want to click the box that indicates "enable iterative calculation"  Then it is very important that you set the "Maximum Iterations" to 1.  This will stop your excel program from harassing you about making circular arguments.  We're going to be intentionally making circular arguments and we want to be free to do that.

This little discovery saved me immense amounts of work, I can tell you.

Okay, we're going to go through the looking glass, now.  This may be a bit of a mind-bender, but I'll try to make it as clear as possible.

First, you want to set up an excel table that looks like this:

If you're not familiar with excel, you are probably way out
of your depth at this point.  Please do your best.

Each of the boxes shows the distance between the various markets on the map above to other markets with which they're connected.  At the top, it can be seen that I've made a formula for Heap to Adeese that shows "D15+7."  7 is the distance in hexes between Adeese from Heap.  To this we add 1 to account for the market at Adeese, giving us a total of "8."  It is very important to realize that these two markets - Heap and Marzabol - on Adeese's line indicate markets shipping to Adeese, not from Adeese.  Shipping from Adeese is shown on the lines next to Heap and Marzarbol.  That needs to be very clear.

This above will take a little time to set up - but once it IS set-up, you'll never have to worry about it again, no matter how many additional markets you add to your economy in the future.  Moreover, you can add them one at a time - so that if you didn't want to include Adeese yet, you can just leave it out of all your lines - then add it at a later time.

Next, we want to replace the 1 in the D column with the following:

The function "=MIN(E4:F4)" will find the minimum number between those two results.  Since Heap and Marzarbol both count as 8 hexes (adding +1 to the seven-hex distance of their own markets), the cell at D3/4 will equal to 8+1 (for the Adeese market), or 9.

(If you want to know how to merge cells, look up "how to merge" in your excel help menu).

Note that the number under Heap in the Hills from Adeese has jumped from 8 to 16.  This is because now the formula is measuring the distance from Heap to Adeese and back to Heap again, and still adding both markets (14 hexes +2).  When you are working with multiple routes between cities, this will calculate the shortest distance between all possible routes instantly, no matter how many market cities you add to your system.

Okay, let's add the minimum-choice calculation to every line (in the D column only):

The numbers will adjust every time you hit return, but because

of the iterations they won't adjust more than once.  To adjust them

intentionally, hit F9 at the top of your keyboard.

You will soon notice a problem.  While you're not getting circular calculation warnings, the numbers will keep climbing no matter how often you hit the F9 button (manual calculation).  For the present, don't worry about that.  We will solve that problem later.  At the moment if your numbers are climbing to infinity (and you can see that by repeatedly hitting F9), you're doing this right.  (you'll also notice that there's a lag between the numbers in the EFGHI columns and the "minimum" in the D-column; do not worry about that, it is normal).

Our next thing to remember is that we don't need all our markets at any one time: we only actually need the one where the players are.  To get that, we only need to put a "1" in the cell of whatever market we want to calculate.  Then, once we hit F9 a sufficient number of times, the lowest number will ultimate self-generate into all the cells and it will stop changing.  With only nine markets, I only had to hit calculate (F9) four times for Marzarbol to completely calculate:

These are then the distances between Marzarbol and all the other markets in our system.  Marzarbol itself is divided by 1, while Heap's references are divided by 6, Groat's by 8, the Gathering by 11, Crow's Nest by 6 and so on.

If you prefer a purer distance in hexes, merely eliminate the "+1" from your calculations and all your distances will be shown in the exact hex difference between the markets.

This is exactly the system I presently use to determine distances.  Here's a screen shot of of my Distance Table for part of France:

Land distances are shown in brown; sea distances in blue.
The example is shown at the point where there is no market chosen,
so that is part of the reason for the high numbers.

When I finally do add England to my trading system, many of these markets will have additional connections across the English channel; which means I will have to individually update them.  It is typically a 2 to 7 day job to upgrade all the relative markets to a new map region, depending on how details the region is.  With England, it will be my last really heavy change - but for the moment, I haven't done Spain yet (so I have two big additions left to do).  Africa and the remainder of Asia shouldn't be too bad - though China might be something to consider, far in the future.  From this, however, the reader can probably see why I've been holding off adding Spain.

ith my next post, we can talk about the next stage in applying the distance table to calculating the references, to make them ready for the pricing table.