PsiBoxFunction(table_range, Arg1, Arg2, …)

PsiBoxFunction() invokes a custom Box function within the Excel workbook.  This function may be defined within one worksheet and invoked on another.

table_range – The cell address of the Box function. In this example, the cell address is K23:M28.  Notice that the cell address does not include the cell containing the Function Name.

Arg1 – The cell address of the 1st argument.

Arg2 – The cell address of the 2nd argument.

ArgN – The cell address of the Nth argument.

  • In this example, Arg1 will be the value associated with PrevClose and Arg2 will be the value associated with NormDist. 
  • The number of Args must equal the number of Input Parameters in the Box Function. 

Box Function Components

  1. Function name  - (Cell K22) Appears at the top left of the function, in this example, DailyPrice. (Required)
  2. Formula Language – (Cell K23) Appears in the cell immediately below the Function name.  This entry can either be EXCEL or FEEL.  This setting determines what syntax is used in the function. (Required)
  3. Input Parameters – (Cells (L23:M23) Appear in the columns to the right of the Formula Language.  This example uses two input parameters, PrevClose and NormDist . Arguments must be passed to PsiBoxFunction() in this order.  (Required)
  4. Result and Input Parameter Types – (Cells K24:M24) contains the result type for both the Result and Input Parameters, in this example a number.  Although, the types are optional since Formula Language = Excel, this row must exist, even if empty.  (Required if Formula Language = FEEL, otherwise optional)
  5. Function Body – (Cells K25:M27) The third row is the start of the Function body. The body is where intermediary formulas may be calculated.  The first column of the body contains an incrementing number 1,2,3. etc., the second column of the body contains the formula name and the third, merged column contains the actual formula. In this example, the 1st formula calculated is Formula1, followed by Formula 2.   (Optional)
  6. Function Result – (Cells K28:M28) The last merged row contains the Function result. 

Invoking a Box Function

The formula below invokes the Box function shown and described above.

=PsiBoxFunction($K$23:$M$28,FirstClose,D13)

where

  • K23:M28 is the location of the Box function, as shown in the illustration above.
  • FirstClose is the defined name of a cell.  This cell can contain either a constant value, formula, decision variable, recourse variable, uncertain variable or uncertain function. 
  • D13 is a cell address.  This can cell can contain either a constant value, formula, decision variable, recourse variable, uncertain variable or uncertain function.   

Note that the number of arguments must equal the number of input parameters in the box function.

For more information on Box functions, see the Using Custom Functions chapter within the Analytic Solver User Guide.