The PsiOptValue() function has been extended to support Dimensional Modeling. When used with Dimensional Modeling, PsiOptValue() returns the specific values for a cube in an optimization model. A user can display an optimization result by not only specifying the desired optimization, but also by specifying elements of the parametric dimensions through param_slice. When PsiOptValue() points to a constraint evaluated as a structural cube, the argument struc_format can be used to select elements on participating structural dimensions.
To insert PsiOptValue, click Formulas – Insert Function on the Excel Ribbon, select Psi Optimization from the Or select a category dropdown menu, select PsiOptValue.
The following dialog appears.
Cell_or_name: Enter an existing cube.
Optimization: This optional argument specifies the simulation number to which the function will be applied. If omitted, the optimization selected in the Ribbon will be used.
Model: This argument is not supported in Dimensional Modeling. Leave this argument blank.
Struc_format: A string argument specifying the desired element selected along the structural dimension to be monitored. If omitted, the element selected for the Dimension’s Current Value in the Solver task pane will be used.
To use this argument to return the value of a specific element in a structural cube, use the form: “[StructuralDimisension1].[Element1]”.
To use this argument to return the value of a 2-dimensional cube (containing structural dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element]”.
To use this argument to return the value of a N-dimensional cube (containing structural dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element],…,[StructuralDimensionN].[Element]”.
Param_slice: The param_slice argument is an optional string argument specifying the desired element “slice” for the parametric cube to be monitored. If omitted the element selected for the Dimension’s Current Value in the Solver Model task pane will be used.
To use this argument to return the value of a specific element in a 1-dimension cube containing a parametric dimension use the form: “[StructuralDimisension1].[Element1]”.
To use this argument to return the value of a 2-dimensional cube (containing parametric dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element]”.
To use this argument to return the value of a N-dimensional cube (containing parametric dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element],…,[StructuralDimensionN].[Element]”.
Function Signatures
One signature is provided for this function.
=PsiOptValue(output_cell, [optimization],[model], [struc_format], [param_slice])
Output_cell: (required) An output cell that evaluates to a cube, e.g. a constraint, objective, or intermediate cell
optimization: (optional) Specifies the current optimization related to the PsiOptParam() function. If omitted, the optimization selected in the Ribbon will be used.
model : (optional) This argument is not supported in Dimensional Modeling. Leave this argument blank.
struc_format: (optional) A string argument specifying the desired element selected along the structural dimension to be monitored. If omitted, the element selected for the Dimension’s Current Value in the Solver task pane will be used.
To use this argument to return the value of a specific element in a 1-dimensional cube containing a structural dimension, use the form: “[StructuralDimisension1].[Element1]”.
To use this argument to return the value of a 2-dimensional cube (containing structural dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element]”.
To use this argument to return the value of a N-dimensional cube (containing structural dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element],…,[StructuralDimensionN].[Element]”.
param_slice: (optional) An optional string argument specifying the desired element “slice” along the parametric dimensions to be monitored. Detailed usage of this argument is the same as the identical one in the PsiOptData().
To use this argument to return the value of a specific element in a 1-dimension cube containing a parametric dimension use the form: “[StructuralDimisension1].[Element1]”.
To use this argument to return the value of a 2-dimensional cube (containing parametric dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element]”.
To use this argument to return the value of a N-dimensional cube (containing parametric dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element],…,[StructuralDimensionN].[Element]”.
Examples
=PsiOptValue(K21, 2, ””, "[Parts].[Speaker]")– Results in the value of K21 (a cube comprised of 1 structural dimension) in the 2nd optimization, when Speaker is selected for the Parts dimension.
=PsiOptValue(L17, 3, ””, "[Products].[TVs], [Parts].[Electronics]") – Results in the value of L17 (a cube comprised of 2 structural dimensions) in the 3rd optimization, where the element TVs is selected for the Products dimension and the element Electronics is selected for the Parts dimension.
Cells containing PsiOptValue functions will appear under Results in the Model tab of the Solver task pane. Expand the range K31:L31 to display the following.
Address: Displays the cell address range where the PsiOptValue function is located (read – only).
Formula: Displays the cell formula located in the Address range (read – only).
Monitor Value: Displays “True” since PsiOptValue by definition creates a monitored value (read – only).
PsiDimActive() Signatures
The PsiDimActive() function is a property function passed to PsiParamDim().
=PsiParamDim(name, range_or_array, [PsiDimActive(opt, sim, sen])
=PsiParamDim(name, from_num, to_num, [PsiDimActive(opt, sim, sen])
=PsiParamDim(name, length, [PsiDimActive(opt, sim, sen])
The opt argument can be set to True or False. If False, dimension will not appear in the next optimization
The sim argument can be set to True or False. If False, dimension will not appear in the next simulation.
The sen argument can be set to True or False. If False, dimension will not appear in the next sensitivity analysis.
Example
=PsiParamDim(“cities”, A1:A3, PsiDimActive(true, false, false)) - Results in a Parametric dimension named “cities” containing three elements: NY, LA, and SF. This dimension will only be available for optimization models.
PsiDimLock() Signature
The PsiDimLock() function is a property function passed to PsiParamDim().
=PsiParamDim(name, range_or_array, [PsiDimLock(elem_or_index)])
The elem_or_index argument should be either the name of the locked element or its 1-based index.
Example
=PsiParamDim(“cities”, A1:A3, PsiDimLock(“LA”)) - Results in a Parametric dimension named “cities” containing three elements: NY, LA, and SF. This dimension will be locked to the “LA” dimension element.