|
![](comp1.gif) |
Functions
|
|
|
![](ibranch.gif) |
Functions |
![](compbk.gif) |
|
![](compbk.gif) |
|
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.
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. |
|
The commands shown
under the Functions menu item are discussed on the
following pages.
- Add Function: This
command places a form on a worksheet that holds the variables,
bounds and function. A function form must be present on the
active page before any other operations may be performed.
The function may depend on any number of independent variables.
Problems with a large number of variables may exceed the limitations
imposed by Excel and will probably take a long time to solve.
- Differentiate:
This command computes the Gradient (vector of first partial
derivatives) and/or the Hessian (matrix of second partial
derivatives). The Hessian is further analyzed to obtain an
equivalent diagonal matrix with the associated linear transformation
of the variables. In many cases, the diagonal matrix can be
analyzed to determine if the function is convex, concave or
neither at the evaluation point.
- Integrate: This command
integrates a function over the range specified by the variable
bounds. When there are several independent variables, some
of the variables may be fixed at specific values, while the
function is integrated with respect to the others.
- Moments: This command
provides the first four moments of a function together with
the second through fourth central moments: variance, skewness
and kurtosis. When the function represents a probability density
function, the first moment is the mean and the second central
moment is the variance of the associated random variable.
- Optimize: This command
finds the variable values that either maximize or minimize
the function within the ranges of the independent variables.
A simple gradient method is used for optimization.
We cannot hope to provide a complete coverage of any of the
topics on this pages. They all have been the subject of a great
deal of research, most of which is far beyond the capabilities
of this author and the scope of this site. The pages describe
the computations performed by the add-in with a very cursory
and introductory discussion of the underlying mathematics. When
possible, a textbook references is provided. |
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.
|
|