I was going to start the New Selected table, as I said, but I have to write about the Market Cities table a bit first. You will note that it says "New Start" ... that is because I'm keeping all the old data for a little while until I'm sure this new system works. It seems to, but one can never tell. When I feel confident, I'll change the title to Market Distances, a name that at the moment is being used by the old file.
Before going into it, you will probably have found that you are getting a 'circular reference' warning when you open the file. There's nothing wrong here - the file is fine. But you will have to go into your computer and tell it to stop resolving circular issues. I admit, I'm not sure this can be done on everyone's excel; I use Vista, which I am not sorry to say I love, however others feel about it. I can tell you how to fix the problem in Vista. In other formats you're on your own.
The way you do it is to open the Office Button and select Excel Options at the bottom of the pop-up. Once there, click on 'Formulas,' second from the top on the left. Then, on the top right of the pop-up for changing formula options, you'll see a button for 'Enable iterative calculation.' Click it. Then change the 100 in the box below to 1, and change the 0.001 to 0. There, you're all set. No more circular arguments.
However, be warned about randomly messing with this table. The formulas are interactively sensitive, so if you type the wrong thing on the page you'll get error messages which will cascade everywhere and ruin the data. At the moment, testing it, I can't seem to think of a way to make it do that (I removed formulas to make it more streamlined, so it might be immune now).
You'll note that if you hit the function key F9, the numbers will change. This is because there ARE circular functions, but they can't cascade back and forth and crash the files because the iteration has been set to 1. Each time you refresh, the numbers calculate. There's a lot of calculation here, so it can take a long time for them to stabilize. I made some changes last week so they haven't stabilized since that change ... but they will eventually. It usually takes about five or six minutes of hitting the button. This is much less time than the manual calculations I used to have to do. But recently my life got a whole lot easier, and I'm very happy about that.
This table is still in the process of being reformatted; that is why there are only about one fifth of the total cities included. I'll be getting the rest up in the next few weeks.
The lines in orange are the accurate distance between the cities, selecting the shortest route. The others are calculations, as the post I linked talks about. These numbers are used for the Selected table, so I needed to talk about them here.
If you want to mess with this and get the numbers to stabilize, do. Then change one distance, making it lower, and watch them restabilize. It's fun.
Someone who's a programmer could probably do this easier than on excel, but I asked for that two and a half years ago and got no comments back. There are times I hate programmers.
But the guys who invented excel deserve deification.
No comments:
Post a Comment