To define a function, choose Add Function from the menu. The dialog below is presented. The Function Location holds a cell designation from the worksheet. The function form is placed below and to the right of this cell. The initial value is the location of the active cell when the dialog is presented. The location may be manually changed. The $ signs need not be included in the cell address. The Function and Variable names are automatically generated by the program. They can be changed, but the revised names must have no blanks or punctuation. A function or variable name can be used only once in a workbook. The number of variables is entered in the Variable Dimension box. For the example the function has only one variable.

We explain the remainder of the checkboxes on the Add Function page.

Clicking OK places the form on the worksheet as below. This is the simplest form with a single variable. The green area in cell B3 holds the Value of the variable. The program will vary this cell, but the user may experiment with the value. Cells C3 and D3, outlined in maroon hold the Lower and Upper limits of the variable and are to be set by the user. The pink cell B4 is to hold the function to be analyzed. The pink color indicates that the user is to provide an Excel function that depends on the value entered in cell B3.

The figure below shows the form after the function has been entered. Cell B4 how holds the function to be analyzed. All Excel functions must begin with an "=" sign. Excel has computed the value for x = 0.5. Note that the function uses the term X_Value. This is an Excel name that identifies cell B3. The name is automatically created by the program.

In general, the computation of the function may use many cells outside the form and be very complex. Only the result of the final computation must be placed in the function cell of the form.

To differentiate the function choose Differentiate from the menu. The dialog below determines the location of the analysis and the function to be analyzed. The analysis results must be on the same worksheet as the form containing the function. Its location should be chosen so that the output will not overwrite important data on the worksheet.

The checkboxes at the left indicate what derivatives are to be computed. Diagonalization is possible only if the Hessian is also checked. Derivatives are estimated numerically with the step size indicated in the third field from the top.

The results of the analysis are shown below. The derivatives are estimated for the value of the variable set in the function definition, B3. That value is repeated in G3 along with the value of the function in G4. The gradient has a single dimension that is the derivative of the function with respect to x. Analytically, the derivative is:

This result is estimated numerically and is reported in cell H3. To find the derivative, the program changes the value of the variable in B3 and observes the result in B4. With one variable, two evaluations are required to estimate the derivative.

With one variable, the Hessian matrix is simply the second derivative. Three function evaluations estimate this value. Analytically the second derivative is:

The program approximates this result and shows it in cell I3. With only one dimension, the Diagonalized matrix and the Linear Transformation matrix provide no additional information. The analysis starting in column K indicates that the point (0.5) is not a stationary point because the gradient is not zero and the point is not at a boundary. Since the second derivative is positive, the Hessian matrix is positive definite. This indicates that the function is convex at this point.

To integrate the function, choose Integrate from the menu. The dialog selects the function to be integrated and the number of steps in the integration. Three numerical methods are provided.

Simpson's rule is an accurate method for continuous functions and a small number of variables a moderate number of integration steps. It is only available when there are no more than four variables of integration. The other methods are applicable when the integral is over several dimensions.

The results of the integration are shown below. Simpson's Rule is a numerical integration method that is quite accurate for a single variable of integration and the function is continuous. The results are shown below in cell B12. The top line of the form shows the function integrated, the method of integration, and the number of observations used for the integration. The cells in row 11 show the variable of integration and the range of integration. The range of integration is set in the function form.

For the example, the integral can be computed exactly as:

The result of Simpson's rule is quite accurate and the accuracy can be improved by choosing a larger number of integration steps.

The Monte Carlo alternative is invoked by clicking the appropriate button on the dialog. We enter 100 as the number of integration steps, but that number is rounded up to the nearest multiple of 30, 120 in this case.

For the Monte-Carlo integration method, a fixed number of observations of the independent variables are randomly selected, 120 in this case. The average objective function value of the observations multiplied by the range provides an estimate of the integral, presented shown in cell B18 below. Since the result is a random variable, we can estimate the standard deviation of the integral estimate, presented in cell B19. The observations are considered in samples of 30 to obtain an estimate of the error of the estimates. Because of the Law of Large Numbers the mean of the sample means are Normally distributed, so we use the Normal distribution to provide a confidence limit for the integral. The confidence level in B21 can be changed by the user. Formulas in cells C21 and D21 show the range of the confidence limit.

The range of integration is shown in C17 and D17. These were copied from the cells C3 and D3 in the function form. The confidence limits are rather wide with only 120 observations. Estimates with larger sample sizes are shown below. According the Law of Large Numbers, to decrease the standard deviation of the estimated mean by a factor of 10 requires an increase in the sample size by a factor of 100. This is approximately observed in the example where the standard deviation is 0.0126 for a sample size of 120 and 0.0010 for a sample size of 12,000.

This command computes the moments of the function for a selected variable. The Moment dialog sets the location for the display and the function for the analysis. When the number of variables is greater than one, the third field indicates the variable for which the moments are to be determined. All other variables are fixed to the values set on the function form. The current example has only one variable.

The moment calculations require an integration over the range of the variable. The program has three methods for performing the integration. Since the example has only one variable we choose Simpson's Rule. The function will be plotted using the steps entered at the lower right.

The results of the moment analysis are placed on the worksheet at the specified location. The analysis computes the values of the function for the number of steps required and normalizes the results so that the total area for the function is 1. The first result, Constant, is the area under the function. We normalize the function by dividing each of the observations by this constant. Because of the normalization, the function represents a probability density function. The remaining lines on the display show the moments for this density function. The meanings of the values calculated are given on the Moments page of this section.

To optimize the function choose Optimize from the menu. The dialog presents the option of maximizing or minimizing and the choice of starting solution. The Demo option steps through the process with dialog boxes explaining the sequence of steps in the process. The Run option performs the search technique, stopping only when one of the stopping criterion is satisfied. The check boxes select the information to be displayed on the worksheet.

The program uses the simple gradient direct search technique. At each step, the gradient of the function is numerically estimated. For a maximization, the program searches for the maximum along the line defined by the gradient. For this single variable problem, the gradient points toward 0 from any nonzero starting point. The first line search reaches the point providing the maximum function value, x = 0.

For a minimization, the search is in the reverse direction of the gradient and the search seeks a minimum along the line. The minimum is at x = 1.