AC. A simulation tool (2/23/26)

AC1. Predecesors

Microsoft Excel already has a "goal seek" option under its "Tools" pulldown menu. Excel will try different values in one specific cell, closer and closer to the best answer, until the value in a second specific cell equals a target value.

I'm going to explain goal seeking as a game where one person says “you're getting warmer” if a second person is moving toward a hidden goal, and “you're getting colder” if the second person is moving away. The second person is going to start at the number 8, facing the wrong way, with an initial increment of 2. The guessing target will be the number 4. By rule, if you're going the wrong way, make the increment negative and multiply it by 0.5 If you're going the right way, multiply the increment by 2 to get there exponentially faster. What if the answer was 1 million and you were plodding ahead with an increment of only +1?

Current guess Increment discrepancy result Next guess Next increment

Before going further, a caution. Some equations with wavy or curved lines when graphed may have multiple solutions. I came up with the curve y=cos(x)+x^2/30 as an example. As you can see on the graph, this line has not one but two absolute minimums, plus a number of other dips lurking like golf course sand traps to ensnare a goal-seeking program. Thankfully wavy curves are rare. For many problems the goal seeking system works tolerably well.

AC2. Simulations and goal seeking

I anticipate a good number of simulations where we want to optimize multiple input variables to achieve best profits. This process of simulation optimization could be a handy generalized tool for an engineer. Can costs/benefits for the current simulation be improved? Can less lifetime energy be used? Can environmental side effects be reduced without significantly affecting profits? What are the various variables that we can adjust?

I'll compare finding a practical solution to a complex problem to the use of a slide rule in the 1960x. Mechanical engineering students would carry their metal slipsticks around on their belts. These hand-held metal or plastic devices could multiply or divide two numbers to 2 or 3 digits of precision with little time wasted. In many cases nobody cared about finding a result with more than 2 or 3 digits of precision, as long as a usable, practical answer could be turned out quickly.

I often want to minimize the sum of the squares of numbers of imperfections so that no one imperfection stands out too much. In a traffic simulation for example, I want to minimize everyone's waiting time. It won't suffice to put one passenger in a nearly infinitely long waiting queue to get through, frustrating that one passenger, even if everybody else gets through fairly quickly.

AC3: Examples of problems to solve

Elsewhere I have proposed the use of heliostats, solar tracking mirrors, to reflect perhaps 1% of the sunlight landing on Phoenix, Arizona and its suburbs onto a nearby mountain for stored heat, in order to cool each of the city's parking lots and neighborhoods in summer, to generate more electricity in winter by using that stored solar heat and perhaps for other industrial heating uses in winter. Some parking lot owners may demand too much money for the city to mount its heliostats above their nice hot parking lots. Some parking lots might be more shaded by buildings than others during certain key months. We have a good number of cost and size variables to consider. Assuming that we can write a fairly good simulation program, can we then optimize a good number of variables for the project?

Next, Teleport Transit cars need to traverse a certain curved track in the vicinity of ever above-street support pole. The car must traverse a swinging hanger device underneath a support pole. We want to minimize total passenger jerk/acceleration within a half-full Teleport Transit car with a certain steady power applied to its driving wheel as it approaches and then traverses its unique track at a known speed. How do we shape the track to minimize as close to every trace of seasickness from this ride as possible? What is the shape of the optimum track? What will the trackage cost per mile of track?

If we break down the transition into 1/10 or even into 1/100 second parcels we can see the horizontal and vertical accelerations on the car as it climbs its two motorcycle chains, gets its driving wheel into the fixed chain links of the coat hanger device, goes through the rather rapid swing of the coat hanger, then comes off the far end of the coat hanger. The car provides steady acceleration power to its driving wheel but it's likely to slow down as it goes up the grade to the coat hanger device, then accelerate as it drops away from the coat hanger. So, can we reduce the seasickness to an incredibly gentle elevator-like steady acceleration that bothers nobody? How do we optimally handle occasional head wind gusts on bad days?

If we can maintain a rather steady 97% of earth-normal gravity within the car, pulling relatively straight down at the top of the curve, balanced out by perhaps a 101% of earth-normal gravity on the long motorcycle chain loop between support poles, again with the gravity always pulling down roughly toward the bottom of the car, and a smooth rotation of the car at the top, people will say that the acceleration is nothing.

I want a goal seeking program that nudges each section of the Teleport Transit hanger's fixed track up or down on the hanger's surface until minimum total acceleration/jerk through the entire trip is found. I want to know how long the coat hanger device's ends should be. I want to know if the track has a wavy curve shape that I currently imagine it might have. What optimal fixed track shape on the hanger can I use on the first prototype? For purposes of selling the project to investors, what will be the total vertical jerk, horizontal jerk and sudden rotation of the passenger cabin within each tenth of a second unit of time? We're not building a roller coaster or a mechanical rodeo bull today, not that the same simulation tool wouldn't work for building a thrill ride. If we were in the roller coaster business, we might want to set a safety limit for jerk on passengers' bodies and then reach but not exceed that limit early and often.

For most such problems we seek a mathematical total minimum value, usually not an absolute zero. We could hire a human engineer to feed in all sorts of variables to a first simulation, look at the results, come up with a second, better set of inputs, look at the results, and so on for 100 or more iterations. Or, we could replace the human with a second computer program that keeps nudging the input variables and running the simulation repeatedly until we find the best combination of input variables.

AC4: A goal seeking program for multiple variables.

I need a goal seeking program that, given N input variables and a simulation software program that comes out with one calculated output number that is the sum of the squares of several imperfections in any particular simulation run, will try many different values in all of these N variables in order to minimize that simulation's output number. My current system is coded as a Visual Basic for Applications (VBA) program, where a second VBA program contains the code for the simulation run, and all variables and other data is displayed on a Microsoft Excel spreadsheet visible in the foreground for high visibility, easy manipulation and the ability to see if the simulation is drifting ever closer toward a final answer.

I start with an initial value to every input variable, plus an initial increment for that variable. I start with a best run value of nearly positive infinity, so that the very first simulation's minimum is always lower than infinity.

As a rule my goal seeker makes one incremental adjustment to the first of the N variables, runs the simulation and sees whether the adjustment has made progress over the simulation's previous best value. If so, it keeps the new adjustment and changes that variable's increment to adjust the variable even further forward in this same direction next time. If not, it throws the new adjustment away and changes the variable's increment to a smaller amount, in the opposite direction. Then the goal seeker moves on to try an incremental adjustment to the second of N variables, then the third variable, until it has tried one adjustment to every variable. Then it repeats through all N variables again and again, until it has reduced the output variable to an approximately optimum minimum value.

For each variable my current linear adjustment algorithm is as follows:

If the new point is worse than the old point, set the increment to –0.5 times the old increment. In other words, we've passed the optimum point so search a small distance in the other direction.

If the new point is better than the old point, set the increment to +sqrt(2) or 1.4142 times the old increment. In other words, we haven't passed the optimum so keep going forward for the optimum point but slowly grow exponentially.

I picked those increments because half of those increments will appear (during tests) as rational numbers. If I start out with an increment of 1, I'll see increments of ½, ¼, 1/8 on my Excel spreadsheet. With the square root of 2 I'll often see irrational increments of 1.4142 and .707 but half of the increments will still be rational numbers such as .5, .25 or .125 . At some point, perhaps with 5 digits of precision, the program stops. I have in the past run sets of perhaps 1000 complex simulations to get to 5 digits of precision. Under such conditions the simulation tool takes my microcomputer about one minute to run. I find this a quite reasonable time to wait for a good answer.

AC5. Future simulations

One minute per answer is painful enough, but what if the simulation takes an hour or a day? Can we speed up the process?

Each simulation for moving around each variable can take a lot of computing time. Therefore we want to get to the minimum as fast as possible. Perhaps we can use a quadratic estimation of the best increment. For each variable, the quadratic estimation will start with the best three values for that variable so far, assuming somewhat blithely that the current variable is the only variable out of many that is out of whack for now. Assuming only the one variable needs changing, we can create a quadratic equation for the three points and find an estimated minimum at the bottom of the quadratic curve.

I would set a maximum limit of making the new increment no more than + or – 4 times a current increment. This avoids massive jumps that might strand the variable and increment too far from its final resting spot. I would also set a minimum limit of no smaller than + or – ¼ to prevent jumping into too small an increment.

Also we have to worry about coming in for a safe landing with roundoff errors. When all of the improvement in the world isn't getting very far, the total positive change will start getting smaller and smaller. Stop when the total positive change values hit the zero wall, when they are so small that the system isn't going forward any more..