© 2021 Jasper van Baten, AmsterCHEM

# Using the Excel solver

For some unit operation models, you can write all outputs explicitly as functions of the inputs. This is however not always the case. If you cannot write your unit operation calculations explicitly, or if it is not convenient to do so, you can use the Excel solver to solve the model equations.

## Setting up the solver

First make sure the Solver Add-In is selected and active: Set up your calculations in such a way that you can formulate the model equations so that the Excel solver can solve them. This requires creation of a target cell for which Excel needs to find inputs so that value of the target cell is at a minimum, maximum or specified values. If solving requires more than one target cell to be equal to a value, you can add constraint cells to the solver. Test that the solver works by setting up the solver manually, and running it.

You can specify multiple solver models to be solved during unit operation calculation, so if the problem to be solved can be split up in sequential sub-problems, attempt to do so.

## Saving the solver model

Once you have set up the solver, the unit operation will need to know what to solve. Therefore you will need to save the solver model. Solver models are saved in a column vector. The number of elements in this vector is 3 plus the number of constrains you are using. First move the cursor to a space where the solver model can be saved.

All steps below can be performed manually, or you can click the Solver Scenario button on the Unit Operation ribbon.

Open the solver: In the solver dialog, click on Options: In the options dialog, click on Save model. Your selection will be expanded so that it is large enough to save the model: Hit OK to save the model. Exit the solver.

For the unit operation to be able to find where you have saved the model, you need to give a name to the model. For this, select the region where you have saved the model, and select Name from the Insert menu, and then Define: The last step is to tell the unit operation to use your solver model when calculating the unit operation. This is done by adding the name you have given to the solver model to the list of defined solver models on the control sheet. The defined name of the range that contains the saved solver model should appear in the column marked "model name". The models that appear in this column will be solved sequentially; if any of the models fails solving the unit operation calculation fails.

Note: as the solver requires the target cell to be on the active worksheet, solver models must be saved at the same sheet as the target cell.

## Initial guesses

The cells that are solved for (the degrees of freedom, indicated in the solver dialog as "By Changing Cells") will remain in the worksheet after the unit operation is done calculating. By default, this will be the initial guess for the next time the unit operation is solved.

You may run into situations where you want a different initial guess. For example, you could calculate an initial guess from a simplified model that is easier to solve. If you specify formulas in the cells that are solved for, these formulas will be overwritten by the solver.

Specifying an initial guess is optional. If you want to specify an initial guess, make sure the cells that are solved for are in a single range. Mark this range by giving it a defined name (see above for instructions). This will be the initial guess target range. The values can be taken from an initial guess source range. This range should have the same number of rows and columns as the target range. Mark the source range by giving it a defined name as well. Now specify the formulas (or constant values) for the initial guess in the source range. Then, tell the Excel Unit Operation which initial guess values to use by specifying the names that you have defined for the source and target ranges on the control sheet.

## Note

It is important that the dependencies on the current sheet are local references (this influences the way Excel copies values). So rather than calculations!A1, use A1 in your formulas on the calculations sheet.

INDEX
CONTENT