website design software
Calculation

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; if neither of these works for you and the calculation diverges, put reasonable “reset” values back in the cells and then “undo” to get your formulas back. Or, write a macro that does this for you... it’ll happen often!

Tools/Goal Seek

Access this from the Tools menu. A dialog box appears 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.

Solver

To use Solver, the Solver 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 first, 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.

The other gotcha about Solver is that if you’re using it in a macro, Visual Basic needs to have the Solver box checked under Tools/References, or VB won’t be able to find the Solver.