19 October 2010

Helpful Tips for Using Microsoft Excel

Throughout this course, we will be using Microsoft Excel extensively. The purpose of this note is to provide some troubleshooting tips for the often-quirky Solver function and to introduce some built-in functions that you may or may not be familiar with.
First, familiarize yourself with Excel Solver. For a tutorial in Excel Solver see the tutorial for Excel Solver at a site developed from Frontline Systems at www.solver.com.

Errors may arise from either incorrect formulations or from “quirks” within the Solver function. When a problem occurs, check the following possible sources of the error (or errors).

  1. You should always start by developing the model on paper first. If you can write down a clear, easily understood model in symbolic form on paper first, then transferring this model to Excel becomes a much simpler task and errors are less likely to arise.
  2.  Check to make sure you have correctly entered the constraint set. In particular, make sure that the signs on the constraints are consistent with the formulation or symbolic model that you have constructed on paper.
  3. Sometimes, errors will crop up with Solver when there is a function or a cell reference to a function entered in the right hand side of a constraint. In other words, when you add a constraint and you specify the right hand side of that constraint, this should be a cell reference to an Excel cell that contains a numerical value and not a function. Even if solver generates a solution, problems may arise in the sensitivity reports due to this practice.
  4.  For linear models, which we will be primarily dealing with, make sure that you remember to check the “Assume Linear Model” box in the options dialog box and that you specify non-negativity constraints on the changing cells as well.
  5.  Remember that if you use any Excel functions such as “IF” or “MIN”, you have just created a nonlinear model. Moreover, if you divide one cell reference by another cell reference and both of these cells have functions, which contain the changing cell variables in them, then most likely, you have created a non-linear function. This can sometimes be avoided by simply multiplying the denominator of the division by the right hand side value. However, now you have likely committed the error discussed in problem 3 above. Once again, you can simply correct for this by moving the non-numerical terms that contain references to changing cells from the right hand side of the equation to the left hand side. For instance, say you would like to enter the condition that (5A+3B) / (A+B) <= 2 to solver where A and B are the variables whose reference will be entered in the changing cells dialog box. If you enter the formula as is, you will have entered a non-linear constraint. By rearranging the expression as 5A + 3B <= 2(A + B), you have removed the non-linearity problem, but now you have a right hand side value that contains a function, which is the problem discussed in 3 above. So, you need to move the terms that contain variables to the left hand side, i.e., 5A + 3B – 2(A +B) <= 0, which is equivalent to 3A + B <= 0. This is how this condition should be entered into Solver. The error message “The conditions for Assume Linear Model are not satisfied,” may result if any of these mistakes are made. Notice how this is handled for the Portfolio Selection Excel Spreadsheet from Bradley, Hax, and Magnanti.
  6. Note that you may only reference at most 16 discontiguous array ranges for decision variables within the Solver’s changing cells dialog box.
  7.  Sometimes the Solver will report that "the conditions for Assume Linear Model are not satisfied" even when they are satisfied. Solver is doing a numerical check for linearity, and may erroneously find that a model is non-linear if the precision of the check is too high. To adjust the precision of the numerical calculations, click on the Options button on the Solver dialog box and look for the field labeled "Precision." The default value is 0.000001. Changing the precision to 0.0001 should resolve the numerical difficulties. If this does not rectify the problem, then uncheck the box that says "assume linear model", and run the Excel Solver using non-linear programming. This is not as desirable as running a linear program, but it is better than spending too much of your time debugging an error that is due to Excel Solver.
  8. Sensitivity reports cannot handle merged cells on the worksheet you are optimizing. If you run a sensitivity report and it shows up with blank shadow prices, the culprit may be merged cells on your worksheet. The merged cells interfere with Solver's ability to assign labels in its reports. To remove all merged cells from your worksheet, follow these steps. Select all the cells on the worksheet, from the Format menu select Cells…, click the Alignment tab, and make sure the Merge cells check box is clear.
  9. In the Answer report, sometimes equality constraints are reported as non-binding. Equality constraints are binding in any feasible solution, including the optimal feasible solution.

Artikel ini membantu? Silahkan klik tombol like dan share
Jangan lewatkan artikel terbaru Mr Gaptek! Silahkan berlanggan dengan klik tombol like
Previous Post
Next Post


Silahkan tinggalkan pesan jika ada pertanyaan atau hal lainnya.