Spreadsheet Tricks for Thermal Engineers : Calculation, continued

• What about circular references? I don't use them if I can avoid it; too many experiences with the calculation diverging into clearly ridiculous territory. If I have to iterate on a calculation, there are two ways that I like to handle it. One way is to use Tools/Goal Seek. This pops open a dialog box that lets you specify a cell, a target for the cell's value, and a "by changing" cell. So you might specify a cell containing the formulas for a die temperature, a target value for it, and the "by changing" cell is one containing a value -- say, for the flow rate. While this is a great tool, it only works on one column at a time, and you have to enter the data each time. If you're going to be performing the same action multiple times, you have two options: record/write a macro to automate it, or use Solver (next bullet), which is the second way to iterate.
• To use Solver, the Add-In has to be installed. If Solver doesn't show up on the Tools menu, go to the Add-Ins menu and install it; it comes with every installation of Excel and you shouldn't need the disk. Once it's installed, selecting Tools/Solver brings up a big dialog box that is more or less self-explanatory. But before you do that, set up the problem to include a cell that you can minimize. I usually set it up to be a sum of the squared error, so that it's always positive. The error could be between the total heat flux and the heat flux in each of two parallel paths (going to separate "grounds", one of which depends on the heat flux in the other leg, makes this an iterative problem). So one cell in each column contains the square of the error, and another cell elsewhere is the sum of all the errors; this is the one you ask Solver to minimize. One big caveat with Solver, though: sometimes the problem is sufficiently complex that the solution it finds is a local optimum.  In other words, the answer it finds depends on your choice of starting point. This is probably true of almost any iteration scheme; just be aware that this could happen. I also often see the Solver give a message that it hasn't found a solution, when in fact all the squared-error cells are nearly zero; not sure what's going on there, but it's easy enough to figure out and ignore.

Ideas on data analysis in the next newsletter...