Computation - Operations Research Models and Methods

Computation

Functions

Download

	Functions

A powerful feature of Excel is that the contents of a worksheet cell can be made to depend on variables in other cells. The relation between the dependent cell and the independent variable cells is called a function. In mathematical terms, the value of y depends of the values of the variables in the vector x according to the function f.

y = f(x)

We restrict attention to cases in which x is a vector of one or more independent variables. Independent variables are continuous and bounded with simple lower and upper bounds. The function f(x) is a single value, a scalar, that depends on the values of the variables. The only immediate restriction on f(x) is that it be defined and finite for all values of x in a specified range. An example is the function:

Although this is a very simple function, Excel allows an almost infinite variety using its built-in functions combined in various arrangements by formulas provided by the user. We require that the value of the dependent variable be placed in a single cell on the worksheet, but that cell can depend on any number of other cells on the worksheet or on cells anywhere in the Excel workbook. Although all the examples in this section involve functions that have closed form expressions and are relatively easy to evaluate, the more useful situation is where the function is the result of a series of computations in an Excel workbook. That function may take considerable time to evaluate for a given selection of x.

Operations

There are many operations that one might perform on a function. The operations provided by the Functions add-in include differentiation, integration, moment calculation and optimization. All operations are performed numerically, rather than analytically. The various features are described on the following pages.

The Functions add-in provides optimization features that are useful for many applications. Although the Solver add-in is a very powerful optimizer for a wide variety of problems, the Functions add-in provides additional information such as Gradient vector and Hessian matrix. In some ways the Functions add-in is similar to the Optimize add-in that optimizes over discrete variables.

Calculations

For most of the operations implemented by the add-in, it is necessary to numerically evaluate a function many times. To compute the value of a function, the add-in places the values of the variables into cells on the worksheet. The function is then evaluated by Excel. Each of the operations has three methods for the evaluation. With the Automatic method when a worksheet cell is changed, all the cells that depend on that cell are automatically recomputed. This is fine when the decision vector has only one component. For multidimensional problems, the cells are recomputed as each variable component is changed. For a complicated function, this could be time consuming. It might be better to wait for the calculation until the entire vector is entered. The other two methods do this. The Worksheet option is best when all the cells affecting the function are on the same worksheet as the function form. The Workbook option is necessary when cells on different worksheets are involved. For problems with only a few variables and simple functions, the Automatic method is probably the best. This method is used for all the example problems.

When using this add-in it is important to set the Excel Calculation option set in the Preference dialog to Automatic.