Saturday, April 9, 2016

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.


  1. This is a pretty easy-to-use implementation of Dijkstra's algorithm! You've made me curious to investigate whether there exists possibly faster or more efficient ways of performing these calculations, but the bottleneck may of course be Excel's weaknesses.

  2. Hm. That's pretty wonderful. Re-invented from scratch, as I can't ever recall having seen this content before.

  3. Anyone using LibreOffice Calc for this? I am currently and I found the iterations option and have it set to 1, but it's still giving me an error.

  4. I don't know LibreOffice Calc, but there may also be a button somewhere else for enabling iterations.

    I found this, but I don't understand it:

  5. I have a bunch of questions about your particular methods for measuring days of travel between two places. Thanks in advance if you have time to answer them.

    When you add additional days of travel time based on elevation difference, do you calculate that looking at one pair of hexes at a time, or based on the overall difference in elevation between start and end points?

    How do you calculate days of travel when two places are connected by a river? I know you take current into account, is that based on the river size numbers that you put on your maps?

    Similarly, how many hexes per day do you use for sea routes? And how do you decide what the limits are in terms of not connecting every port in the world to every other port?

  6. Just found this
    which answers how you decide which ports link to each other.

  7. Samuel,

    Thank you for the donation. Much appreciated, believe me. I'll try to make it worth your while; I've sent you an email that should let you view the trade content on my google drive.

    On that, you'll find one small file called "Distances 5oct14" ~ this is the calculation I have excel do for me for "days" between one hex and another. You'll see three columns in yellow. All three are the same, they're just set up that way so I can compare one route to another to see which is shorter.

    By putting a string of elevations in excel, the file quickly calculates the distance. Each 40 change in elevation between hexes increases the number of days by 0.1.

    On the right, you'll see two groups of columns in blue. One calculates "down" river and the other "up" river. The numbers are put in the same order for both directions (there's some gobbledygook in the M column, it is just old work that I didn't delete. If you know excel, you'll see quickly why it doesn't matter.

    The river will then give two different numbers of days; it isn't based at all on the size of the river, though small rivers are very rarely navigable (unless the land is very flat). So if goods are hauled up river, use the larger number, if they are hauled down river, the smaller number.

    On top of the calculation, I then add small penalties for rivers crossed (0.01 per point of river) and borders (.1 for a state border, .2 for a national border). The number is added each time the route crosses a river or a border, even if it is the same river or border.

    You can see, then, that I calculate it out one hex at a time. This should pretty much answer your first and second questions.

    And you found the post for the sea routes.

  8. As regards the limits of not connective every port in the world to every other port . . .

    I did have one port that did not fit the system. It is a place called Anauroch, in the Andaman Islands east of India. There were places that could import from there, but Anauroch could not import from anywhere (it has 1 market point and is about 40 hexes from another port).

    Most of the time, when this happens, the port is part of the mainland, so it can be attached to the market system by land, if not by sea. But Anauroch is on an island. But it has to be attached, so . . . I simply designated the nearest port as its one connection and moved on. Sometimes, we have to break a "rule."

    I expect to start having more trouble with my system when I start mapping the New World (many European ports won't reach across the Atlantic) or Oceania (where the ports, all non-human, will be too small to import anything from anyone). In such a case, I'll make the same Anauroch fix or, in the case of some European colonies, I'll connect them to the nearest Portuguese, French, English or Dutch port.

    Anything else?

  9. Thank you so much! You have answered all my questions, though I am sure I will find more as I dig through the files over the next while. It feels like world-discovery Christmas. The work you have put in to the trade system is evident, and I appreciate all you have done to make it accessible to others.

  10. Managed to recreate the sheet presented here but I’m afraid I don’t understand what I’m looking at. Marazibol -Adeese connection is 16? But it was clearly 8 when we started. So what does the 16 represent? I’m sure I’m missing something simple here but the logic of this step is just evading me and I’m tired of sitting her like a dolt so I’ll just ASK!

  11. The only number that counts is the one in the D-column. Once you've set your maximum iterations to 1, each time you manually calculate all the numbers will change ... until all the circular (and inconsistent) calculations sort themselves out. The numbers will look wonky until this happens.


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.