Return to Index
Operations Research Models and Methods
Computation Section
Subunit Functions
 - Enumeration
This page describes the Function Form used to represent the analysis on the worksheet. The form constructed here is for the Enumeration method. The actual analysis is on the next page.


The Function Dialog

To create a form that holds information describing the input values and the output functions, choose Add Function from the Random Variables menu. The dialog shown below is presented.

The top field holds the cell designation of the upper-left corner of the form. Initially this field holds the address of the active worksheet cell when the dialog was called. The address cannot be changed on the dialog, so if it is not correct, simply Cancel the dialog and place the cursor at the desired cell.

The next field holds the name of the function form. This name should reflect the purpose of the analysis and should be a single word starting with a letter and with no punctuation. It cannot represent a cell address such as F1. The default name uses the underline symbol to create usable names such as F_1. Once a form is defined a number of cell ranges are named using the text entered here and changing the name is difficult.


The fields at the the upper left hold information about the random variables. The first holds the number of input variables, 4 for the example. The Min, Max and Interval fields are used by the enumeration method, as described below, but are not used by the simulation method. The Include Address checkbox is described later.

To the right of the random variable information is the probability distribution description. The form describes probability distributions in three ways as determined by the buttons in the Distribution frame. With the On Form option, the random variable distributions are placed directly on the function form. This is often the most convenient way and is illustrated here. If random variables are already defined elsewhere on the worksheet, the form can reference these by name. This is the Reference by Name option. Alternatively, the random variables may be determined a collection of scenarios. This is the Scenario option. The field directly to the right is enabled with the scenario option and holds the number of scenarios.

The function information is at the lower left. The number of functions is in the first field. The Example Function box, uses built-in functions to illustrate the analysis procedures. We describe the Include Feasibility and Include Address checkboxes later.

Different forms are created depending on the analysis method chosen in the Method frame at the lower right. This page describes the enumeration form while a later page will describe the simulation form. The Both button creates a form that can be used by both enumeration and simulation and is useful for comparing the two methods. The analysis methods may call other algorithms as part of the solution evaluation. Place the Algorithm name in the field below the frame.


Set Distribution


With the On Form option, the probability distribution is initially determined by clicking the Set Distribution button. The dialog is the same as that used to define random variables. The cell, name and replication fields are not used for this application. Select the desired distribution by clicking one of the buttons at the left and set the parameters on the right. The distribution type and parameters may be changed on the worksheet.



The Function Form


The form constructed by the add-in is shown below. The first row of the form shows the name and analysis method. The random variables are defined next in rows 2 through 5. The Binomial distribution describes a discrete random variable and has two parameters, the number of trials, n, and the probability of a success, p. The random variable is the number of successes in 5 trials and can assume the values of 0 through 5. The parameters may be changed on the worksheet.


Rows 6 through 13 hold information used in the enumeration process. During the enumeration, all possible integer values of the four variables in the range 0 through 5 are generated. The analyst should enter the names of the variables in row 6. The program places the values of the variable in row 7 during the enumeration process. Rows 8 through 10 hold the minimum, maximum and interval values for each variable. Row 12 holds a formula that uses the RV_Prob function to compute the probability the random variables take the values given in row 7. Cell B12 computes the joint probability as the product of the numbers in the range B11:E11. This joint probability assumes the random variables are independent. Cell B13 will hold the total probability enumerated after the enumeration process is complete.

Rows 14 through 18 hold information regarding the functions and results of the analysis. The names of the output variables should be placed in row 14. The maroon cells in row 15 are for the analyst to place formulas that specify the functions to be analyzed. Rows 17 and 18 will hold the results of the analysis.

In general white cells indicate ranges that are controlled by the analyst. Numerical values can be simply numbers or references to other cells that compute the entries. Yellow cells hold formulas created by the add-in and should not be changed. Maroon cells hold formulas that are to be entered by the analyst and green cells hold results that will be inserted by the add-in. It is possible to enter numbers in the green cells, especially the value cells in row 7, to debug the formulas in row 15. Any numbers entered in the green cells will ultimately be replaced by the add-in.

The example functions shown below are implemented using Excel formulas in row 15. For simplicity the analysis takes place entirely on the form for this example. This is not a restriction, however, because the input variables can be linked to any cells on the worksheet and the output values on the form can be simple pointers to other cells on the worksheet.

The figure below shows the form with explicit Excel functions in row 15. We have placed nonzero values in row 7 to illustrate the functions. Note that the formulas in row 11 automatically compute the probability of the values in row 7, and cell B12 computes their joint probability as the product of the probabilities in row 11.

Return to Top

tree roots

Operations Research Models and Methods
by Paul A. Jensen
Copyright 2004 - All rights reserved