The Power! The Absolute Power!

O gentle reader, turn aside and find what else there is to read - this will be of little interest altogether.  I shall speak today of the Great Insane Distance Table.  Almost three years ago, it looked like this.

Today, after some redesigning of the table and adding another two hundred cities to it, the table looks like this:

For future reference, Pinerolo is on the far left, and is pink on the edge of the table, and Croftshelm is on the far right, in dark green.

Italy added 117 cities all by itself.

Two years ago, I began a spreadsheet for calculating the distances between these cities, one that included features that would check my work.  The features were largely necessary due to a problem I was having with excel.  In order to find the shortest distance betweeen cities, the calculations ran bang into problems with 'circular references' ... which ignorantly I believed was an unchangeable condition of excel, and which I wrote off to my being unable to program and being forced to use this archaic, annoying system in order to handle my issues.

How wrong I was.

Yesterday, I stumbled across an interesting feature of Vista excel.  For those who might have excel, you click on the Office Button (the one in the upper left corner), on Excel Options, and then on Formulas.  In the upper right corner you will find a box for 'Enable iterative calculation' ... which the reader will take note does no make direct reference to circular references, and for that I'm obviously an idiot for not knowing intuitively that they relate to one another.  Yes, you can find the relationship in the Help menu.  Have you ever used a Help menu?

If you click this box, and you set the maximum iterations to 1, with no change at all, circular references just go away.  They cease to matter.  And at this point you can calculate everything, even putting in box A1 "=B1" and putting in B1 "=A1".  Try it.  Try it without the iteration change, and then try it with.

As a result, yesterday two years of work went 'poof!' as I redid it all in about seven hours.  And as I redid it, stunned as I was to find it was really this easy, and that I'd been unaccountably stupid for two years, I found all my concerns about the other 700 cities (estimated) that I had planned to add going up in smoke as well.  Knowledge is a wonderful thing.  Knowledge is the game changer.

So I am a very happy camper today.  A little abashed, but that goes with the territory.

If I can just put up a very small bit of the spreadsheet, the former problem might be a little more evident:

Believe me, this table just goes on and on.  Every market has a column along the top, and every market has its own row, plus a row that describes its distance from the top listed city through each of the cities through which that market is connected.

Thus, if we look at the table, the first column lists Pinerolo, which is a city in northwest Italy near the modern border between France and Italy; in 1650 it was under the jurisdiction of the French King (shown here as 'FRA/').  The first row lists ALT/Croft - Croftshelm ... which is a city in extreme east modern Kazakhstan.  In 1650 virtually no one lived here, and the actual city - modern Oskemen - was not founded until 1720.  Thus, in my world, I have named the nonexistent city 'Croftshelm' in the region of 'Croft' and designated it Dwarven ... but that's not important now.

The table shows the distance FROM Pinerolo TO Croft (it's important to remember that the top line is the origin and the side is the destination) as it is through Semey or through Verny.  Verny is south of Croft, and the shorter road from Pinerolo through Verny actually requires you to go through Semey (to get to Verny from the south requires going through Babylon and the Persian Gulf, and is just too far) so the road from Semey is the shorter route.  To get to Semey you travel from Pinerolo down the Po Valley to the Adriatic, to Constantinople, then through the port of Cherzeti in Crimea to Tsaritsyn (modern Volgograd), up the Volga, then up the Kama, through the Ural mountains, down rivers to the Irtysh and then up the Irtysh river until you reach Semey itself, which is the highest navigable point on that river ... and thence overland to Croftshelm.

The table shows this will equals 129.8, which is the number of days travelled, so while the route is slightly more than four months.  This includes time for loading, unloading, climbing hills and through passes, etcetera, etcetera.  It is an estimate, and on foot without a wagon the distance isn't quite that far.

How do I know these are the distances?  The grey number beside 'Croftshelm from Semey' on the table is a calculation.  It takes the distance from Pinerolo to Semey and adds 4.5.  Previously it has taken the distance from Pinerolo to Sibir (which is further down the Irtysh River) and added 11.6 (the distance from Semey to Sibir).  Sibir is 9.3 days from Boloine Ob, which is 1.8 days from Yobat, which is 5.8 days from Eykhoth (modern Sverdlovsk/Ekaterinburg), which is 16 days through the Ural Mountains from Great Bolgar (modern Perm), which is 9.6 days from Sirk on the Volga, which is 2 days above Samara, which is 3.6 days above Volki, which is 1.7 days above Saratov, which is 4.1 days above Tsaritsyn, which is 17.1 days from Cherzeti overland and through the disembarkation port of Azov.  Cherzeti is 10.7 days over the sea from Constantinople, which is 2 days from Kallipolis on the Dardanelles Strait, which is 5 days from Corinth, which is 1.7 days from Patras on the north Peloponnese, which is 4.7 days from Valona in Albania, which is 1.3 days from Brindisi, which is 17.3 days from Commachio, which is 1.1 days from Ferrara on the Po River, which is 2.6 days from Pavia, which is 0.7 days from Alessandria, which is 0.5 days from Asti (note the thickness of trade cities in northern Italy), which is 1.8 days from Turin and which is, at last, 1.3 days from Pinerolo.

Each of these distances is a calculation, and each calculation is added together to create the shortest distance between A to B to C and so on ... from Pinerolo to every other market in my world.  And THEN there are calculations for every other market to every market ... I estimate more than 700 total markets in areas that have been mapped, including the Netherlands and India which have not been added to the above graph posted.  And each new city takes longer than the last one.

Most of the work done to date has required manual tweaks, since if I calculate from Pinerolo to Turin and from Turin to Pinerolo, I get a circular reference and I'm stopped.  But no more!  I'm free, I tell you.  I'm free!

As of yesterday, every calculation is automatically done, and I am master of the world again.

Incidentally, if you will remember this post, all these calculations are simply to determine what the availability of a particular item is in a particular market, depending on how far that market is from where your character is standing.  So in Croftshelm, a reference to a product produced in Pinerolo is divided by 129.8.  So if it is one reference to, say, perfume, that reference is worth 0.007704 in Croftshelm.  This is not very much.  If the only perfume in the world were made in Pinerolo, you'd have only one chance in 129.4 of even finding it ... and what with the other things that are done to perfume before it is made into perfume, it would probably be more than a thousand times more expensive.

Just food for thought.