Handling Some of the Problems Encountered When Using Excel Solver for Microsoft Excel 2000

1. Handling Some of the Problems Encountered When Using Excel Solverfor Microsoft Excel 2000

2. Some Common Sources of Problems • it is possible that the… • time or iteration limit was reached; • precision is too large ; • convergence to the solution is too slow; • model is poorly scaled; • choice of initial solution is inadequate; • solution was affected by limitations on finite precision computer arithmetic; • solution is right and your expectation is wrong…

3. Modifying Solver Options Recall:Solver Options dialog box with default values

4. Increase the value in the Max Time: box and/or Iterations: box Modifying Solver Options • if time or iteration limit was reached NOTE: The message in the Solver Results dialog box will indicate that either the time or iteration limits were reached.

5. Decrease the value in the Precision: box Modifying Solver Options • if the precision is too large NOTE: Constraints are checked within the precision specified in the Precision: box (smaller value means higher precision).

6. Decrease the value in the Convergence: box Modifying Solver Options • if the convergence is too slow NOTE: The message in the Solver Results dialog box will indicate that Solver converged to the current solution. This means that the last five iterations showed relative changes smaller than the value in the Convergence: box.

7. Check the Use Automatic Scaling: box to rescale values to a common magnitude Modifying Solver Options • if the model is poorly scaled NOTE: A model is poorly scaled if it deals with values which have large differences in magnitude. This is the most common source of problems when usingSolver.

8. For more about other Options, click on Help Modifying Solver Options • other Options WARNING: Additional mathematical background might be required to understand some of the remaining options.

9. Selecting Initial Values • if the initial values are inadequate Recall: Variable values in the cells shown in the By Changing Cells: box of the Solver Parameters dialog box NOTE: In optimization problems Solver usually finds a local optimum solution that is close to the initial variable values. Run Solver with initial variable values (on the spreadsheet) that may be closer to the global optimum solution.

10. Finite Precision Computer Arithmetic • not Solver’s fault… • Computers store numbers in binary format (as sequences of 0’s and 1’s); • Some numbers in binary format cannot be represented in a finite amount of space and need to be rounded when stored; • If several arithmetic operations are performed on the rounded representations, rounding errors may accumulate.