Operations in DPL ordinarily take scalars (single values) as operands and return scalars as results. For example, the expression x+1 would add the value x (a single value) to the constant 1 (a single value) to produce a result as a single value. Similarly, most DPL functions take arguments that are scalars, and even those whose arguments may be arrays return single values. For example, the function @npv takes an array of cash flows as an argument, but returns a single value for the net present value of that cash flow.
When an operand that is ordinarily required to be a scalar is replaced with an array, the containing formula is said to be an array formula. An array formula must be enclosed in the special symbols {= and } (the notation used by Excel). For example:
array A = {1,2,3;
4,5,6};
array B = {=2+A};
Then B is defined as the array:
{3,4,5;
6,7,8};
Array formulas are very efficient when you need to perform repetitive calculations or calculations that involve large amounts of data.
Array formulas are processed in four steps:
-
The size of the resulting array is determined. The number of rows of the resulting array is equal to the maximum of the number of rows of any operands or arguments that would ordinarily be scalar but have been specified as an array. Arguments that may be arrays or must be arrays are not considered in this calculation (for example, the result of an NPV calculation is a scalar, regardless of the size of the array argument). The number of columns is calculated similarly.
-
The operands are sized up or down, as necessary. All operands or arguments that would ordinarily be scalar are converted (expanded) to arrays of the same size as the resulting array. If an operand or argument consists of a single row or column, that row or column may be used for all rows or columns of the resulting array. Because a scalar is considered to be an array of a single row and a single column, it may be expanded in both dimensions to produce an array in which each element is the value of the scalar.
- If an operand or argument consists of more than one row or column but fewer than the number of rows or columns of the resulting array in the same dimension, the unfilled rows or columns of the expanded array are filled with the error value @na.
-
The operation is performed using corresponding elements of each of the expanded arrays to produce a corresponding element of the resulting array. This is not the same as the matrix operations of mathematics (a result array of n elements will result in execution of the operator or function exactly n times). If any array element involved in an operation is the value @na, the resulting array element is @na.
- The result array is assigned to the target array. This step is performed only when an array formula is used to initialize an array variable.
Result array elements are assigned to the target array beginning with the element in the upper left hand corner (the element in the first row and column). If the result array contains more rows or columns than the target array, excess rows to the bottom and excess columns to the right are discarded. If the result array obtains fewer rows or columns than the target array, the result array is expanded in the manner described in step 2.
For example, consider the following definitions:
array A = {10,20,30};
array B = {1,2,3;
4,5,6};
array C = {=A+B};
Step 1: The resulting array is determined to consist of 2 rows and 3 columns.
Step 2: Array A is scaled up to the same size as the result array.
{10,20,30;
10,20,30};
Step 3: The operation is performed to produce the result array.
{11,22,33;
14,25,36};
Step 4: The result array is assigned to C.
You may specify the size of the resulting array.
array A = {10,20,30};
array B = {1,2,3;
4,5,6};
array C[3][3] = {=A+B};
Because the array that results from adding A and B has only 2 rows, the third row of C is filled with @na.
{11,22,33;
14,25,36;
@na,@na,@na};
The dimensions of the result array can depend on the states of events.
chance Duration.{long, short} = {0.2, 0.8} = 19, 9;
array Cash_Flows_1 = {2,1,3,4,1,3,1,1,3,2,1,2,1,1,4,5,1};
array Cash_Flows_2 = {=Cash_Flows_1 {0 ..Duration} *1.05};
value NPV = @npv(.85, Cash_Flows_2);
Here, Cash_Flows_2 will be an array of one row and either twenty columns or ten columns depending on the state of Duration.
Array formulas may contain array constants. An array constant is specified in the same manner as an array variable initializer; however, each element must be a constant expression and no element can be missing.
For example, in the array formula
{=A+{1,2}}
the variable A is added to the array constant {1,2}.
Versions: DPL Professional, DPL Enterprise, DPL Portfolio