Help Manual


Sigma Magic Help Version 17



The optimization tool can optimize a set of inputs to achieve specific objectives in the presence of constraints.

This tool can be added to your active workbook by clicking on Stats and then selecting Optimize.


Click on Analysis Setup to open the menu options for this tool.


A sample screenshot of the variables menu is shown below.
Num Variables: Specify how many variables you have in your design that you would like to optimize. Currently, you can optimize up to 20 variables using this tool.
Initial Values: The optimization algorithm needs to start the optimization at some initial values. You will need to specify the initial or starting values. The following options are available:
AutoThe software will automatically determine the initial values (shown in #6). For real variable types, the initial values will be at the center point of minimum and maximum values. For binary or integer data types, the initial value will be the average of the minimum and maximum values but rounded to the nearest integer. If the input data type is fixed, you must manually specify the initial value(s).
ManualYou will have to enter the initial values for this study manually.
Name: Specify the name for each variable. The default names are x1, x2, x3, etc. You can give any name you wish, subject to the following constraints. The names for each variable have to be unique across all variables. They should not contain spaces or special characters. Note that names may not be case-sensitive.
Type: Specify the type of variable. The following options are available:
2LevelThe variable can only take values -1 or 1. This is typically used for DOE models.
BinaryThe variable can only take values 0 or 1.
FixedThe variable value is fixed to the initial value and cannot change.
RealThe variable can take any values between the minimum and maximum specified.
IntegerThe variable can take any integer value between the minimum and maximum specified.
Minimum: Specify a minimum value for the variable. The algorithm will only look for values above this number. Hence, this is also sort of like a constraint on your variable. Pick a large negative number if you do not have a minimum limit.
Initial: Specify an initial value for your variable. It is recommended that the initial value you specify be a feasible data point - that is, it should satisfy all the constraints. You could either randomly pick this initial value or maybe pick it right in between the minimum and maximum values.
Maximum: Specify a maximum value for the variable. The algorithm will only look for values less than this number. Hence, the maximum value also acts as a constraint for your optimization problem. Pick a large positive number if you don't have a maximum value.
Help Button: Click on this button to open the help file for this topic.
Cancel Button: Click on this button to cancel all changes to the settings and exit this dialog box.
OK Button: Click on this button to save all changes and compute the outputs for this analysis.


Next, let's set the objectives for the optimization. You can do this by clicking the Objectives tab in the dialog box. A sample screenshot of the objectives is shown below. inputs Desirability Values
Num Objectives: Specify the number of objective functions you want to optimize. You can optimize up to 10 objective functions at the same time.
Method: If you have multiple objective functions, the software will automatically combine the different objective functions into a master objective function depending on the relative weights specified for the objective functions. The following options are available:
AdditiveThe master objective function is obtained as a linear summation of the objective functions. For example, if we have a variable x and three objectives - one is to minimize f(x), the second is to maximize g(x), and the third is to set the h(x) to a target T. The additive model builds the following master objective function = w1 * f(x) - w2 * g(x) + w3 * abs(h(x) - T). This master objective function is now minimized to determine the optimized value of x. Note that if you select this method, you must specify a target value (T) for the Target (#6a).
DesirabilityThe master objective function is obtained using desirability values. For each objective function, a desirability value is obtained. The desirability values range from 0 to 1. A desirability value of 1 implies maximum desirability and a desirability value of 0 implies minimum desirability. A composite desirability value is obtained by combining the desirability of all objective functions. The composite desirability (dc) is a weighted geometric mean of the desirability values. If the objective is depreciation, the desirability value is 1 for all values less than the target. If the objective is maximized, the desirability value is 1 for all values greater than the target. If the objective is to achieve a target, the desirability value is one at the target and goes to zero as you approach the upper or lower values.
Name: Specify a name for the objective function. Make sure that the specified name is unique across all objective function names. The default names are y1, y2, y3 etc.
Type: Specify the type of objective function. The available options are:
MinimizeYou would like to minimize the value of the objective function.
MaximizeYou would like to maximize the value of the objective function.
TargetYou would like to set the value of the objective function to a target value.
Weight: Specify the weight for the objective functions. These weights are used if you have multiple objective functions to combine them into a single objective function. Note that the weights can take any real number. The larger the weight, the more important that specific objective function is. The default value of the weights is 1, which means all objectives have the same importance.
Lower: Specify the lower value for a given objective function. You do not need to enter this value if your method is Additive. This value can only be entered for the desirability method to maximize or achieve a specific target value.
Target: Specify the target value for a given objective function. This value is required if you specify either the desirability method or the additive method, and the object is to achieve a target value.
Upper: Specify the upper value for a given objective function. You do not need to enter this value if your method is Additive. This value can only be entered for the desirability method for either minimization or achieving a specific target value.
Equation: Define the equation for computing the objectives. This should use a valid equation using the variables in the variables tab. An example of an equation is y1 = x1 * x2 ^ 3 + (x4 - 5)^2. You can use any standard mathematical function to define the objective function. You can copy and paste the transfer function from other analyses such as DOE, Regression, etc. Note that you can also import the models.
Once all the objectives have been defined, the next step is determining any constraints for your optimization.


Click on the Constraints button to open the Constraints dialog box. A sample screenshot of the constraints menu is shown below.
Num Constraints: Specify the number of constraints for your optimization problem. You can optimize up to 20 constraints for your optimization problem. If a solution is feasible, it should satisfy all these constraints at the same time.
Method: Specify the number of methods for defining the constraints. Currently, the only option available is to use inequalities (<=, =, >=) to define the constraints.
LHS: Specify the constraint's left-hand side (LHS). You should use the same variable names as specified earlier to define the constraint equation. Note that you do not need to re-specify the constraints for the minimum and maximum values defined for each variable in the first tab.
Relationship: Specify the type of relationship for the constraint. Is it less than, equal to or greater than type of relationship?
RHS: Specify the Right Hand Side (RHS) of the constraint. It would be best to use the same variable names as specified earlier to define the constraint equation, or the RHS value could be a constant.
Finally, specify any optimization options.


The optional setting can be opened by clicking the Options button in the dialog box. Below is a sample screenshot of the options for the optimization problem. inputs The following options can be set.
Method: Specify the method for solving the optimization. The default option is Generalized Reduced Gradient (GRG) Nonlinear, which can be used to solve non-linear optimization problems. The solver looks at the gradient of the objective function as the input variables change and determines that it has reached the optimum solution when the partial derivatives are equal to zero. This method is usually fast, but the limitation could be that it may not reach the global minimum. The objective functions should be relatively smooth when using GRG nonlinear optimization. You can choose the Simplex LP optimization if all the objective functions and constraints are linear. This method will always result in a globally optimal solution. However, this can only be used in limited cases when all the variables' objectives and constraints are linear functions. The evolutionary algorithm is more robust than GRG nonlinear and may reach the global optimum, but it is prolonged. It uses the theory of natural selection, random starts, and mutations to perturb the solution, looking for a global minimum.
Precision: Specify the degree of precision you want for the optimization. This value checks if your constraint is satisfied with this amount. The smaller the number, the higher the precision.
Scaling: If you specify autoscaling, then the solver will internally rescale the values of the variables, constraints, and the objective to similar magnitudes to reduce the impact of extremely large or small values on the accuracy of the solution process.
Convergence: Specify the convergence tolerance for the nonlinear algorithms. If the solution does not vary more than this amount when the inputs are changed, the solution is assumed to have converged.
Derivatives: Specify the algorithm you would like to use to calculate the derivates. Use either the forward difference method or the central difference method. The central difference method requires more computations and should only be used if you are having trouble converging to a solution using the default forward difference method.
Mutation Rate: The mutation rate is between 0 and 1; it is the relative frequency with which some population members will be altered to create a new trial solution. A higher mutation rate increases the diversity of the population and the chance that a new or better solution will be found, but this may increase the total solution time.
Population Size: In the Population Size box, type the number of different starting points (values for the decision variables) you want the multi-start method to consider. The minimum population size is 10; if you supply a value less than 10 in this box or leave it blank, the multi-start method uses a population size of 10 times the number of decision variables but no more than 200.
Random Seed: The random seed is a positive integer number to be used as the (fixed) seed for the random number generator used for various random choices in the Evolutionary method. If you enter a number here, the Evolutionary method will use the same choices each time you click Solve. If you leave this box blank, the random number generator will use a different seed each time you click Solve, which may yield a different (better or worse) final solution.
Muti Start: Select the multi-start option to use the multi-start method for global optimization. If this box is selected when you click Solve, the GRG Nonlinear method will be run repeatedly, starting from different (automatically chosen) starting values for the decision variables. This process may find a better solution, but it will take more computing time than a single run of the GRG Nonlinear method.
Require Bounds: Select the Require Bounds to specify that the multi-start method should run only if you have defined lower and upper bounds on all decision variables in the Constraints list box. The multi-start method is far more effective if you define bounds on all variables; the tighter the bounds on the variables you can specify, the better the multi-start method is likely to perform.
Max Time: Specify the number of seconds the optimization algorithm can run to find a solution. The larger the maximum time, the more likely it is to find an optimal solution.
Max Iterations: Specify the maximum number of iterations the optimization algorithm can run to look for a solution. The larger the number of iterations, the more likely it is to find a solution, but the longer it will take to optimize.


If you click the Verify button, the software will perform some checks on the data you entered. A sample screenshot of the dialog box is shown in the figure below. Verify The software checks if you have correctly specified the input options and entered the required data on the worksheet. The results of the analysis checks are listed on the right. If the checks are passed, they are shown as green-colored checkmarks. If the verification checks fail, they are shown as a red-colored cross. If the verification checks result in a warning, they are shown in the orange exclamation mark, and finally, any checks that are required to be performed by the user are shown as blue info icons.
Item: The left-hand side shows the major tabs and the items checked within each section
Status: The right-hand side shows the status of the checks.
Overall Status: The overall status of all the checks for the given analysis is shown here. The overall status check shows a green thumps-up sign if everything is okay and a red thumps-down sign if any checks have not passed. Note that you cannot proceed with generating analysis results for some analyses if the overall status is not okay.

Optimize Menu Bar

For Optimize worksheets, an additional menu bar is displayed on the top main menu bar, as shown in the following screenshot: Optimize Menu Bar If you don't see this menu bar when you are on an Optimize worksheet, you can display it by clicking on the refresh button (#1 shown in the screenshot). There is one additional button on this menu bar: importing models (#2).

Import Models

This is a helper function that helps you import models that were developed in the current workbook. This function can help you import the designed Regression and DOE models and use those models for optimization. Note that this function can only import models that have been developed already. Once you import a model, it will copy the model equation into the optimization worksheet. However, there is no linkage between the models. If you change the model, it will not be imported automatically into the optimization worksheet. You will have to import the models again for any changes manually. A sample screenshot of the Optimize menu is shown in the figure below. Import Models
Available Models: This list box lists all the models that can be imported for optimization. If no valid models are found, the model notes section will indicate this, and you will not be able to use this function to import models. You can use the search functionality to limit the display of available models, especially if you have a large number of models in your current workbook.
Selected Models: Drag and drop the models you want to import into the list box on the right. When you click the OK button, all the models you select will be imported into the Optimize worksheet.
Model Notes: When you click the OK button, any messages received are displayed in the Model Notes section.
Cancel Button: Click on this button to close this dialog box and cancel the process of importing models from other worksheets in the current workbook. You can manually create the variables and model equations directly through the Analysis Setup dialog box.
OK Button: Click this button to import the selected models into the current Optimize worksheet. The software will try to read the existing variables and models in these worksheets and try to import them into the current Optimize worksheet. Note that any existing data in your current workbook will be overwritten during the import process. Once the import process is complete, the software will display the Analysis Setup dialog box so that you can further refine your imported models.


When all inputs have been entered, click on OK to build the optimization model in the Inputs area of the worksheet and then use the Excel Solver to solve the optimization problem. The notes section will summarize the input variables with variable names and settings (min, initial value, and max). It will show the objective functions, the optimization type, and the constraints list. The method used to solve the optimization is listed as well. The algorithm will run some basic checks to ensure the inputs are okay, but it is your responsibility to ensure that the objective functions are adequately defined. Verify the equation in the Inputs section of the worksheet. The analysis results will show the variables' values (initial value and final optimized values). The conclusion section will briefly state if the optimization was successful. The different statuses that you could get from the optimization are listed in the table below:
0Solver found a solution. All constraints and optimality conditions are satisfied.
1Solver has converged to the current solution (may not be optimal). All constraints are satisfied.
2Solver cannot improve the current solution. All constraints are satisfied.
3Stop chosen when the maximum iteration limit was reached.
4The Objective Cell values do not converge.
5Solver could not find a feasible solution.
6Solver stopped at user's request.
7The linearity conditions required by this LP Solver are not satisfied.
8The problem is too large for Solver to handle.
9Solver encountered an error value in a target or constraint cell.
10Stop chosen when the maximum time limit was reached.
11There is not enough memory available to solve the problem.
12Cannot solve the model since another program is using Solver.dll.
13Error in model. Please verify that all cells and constraints are valid.
14Solver found an integer solution within tolerance. All constraints are satisfied.
15Stop chosen when the maximum number of feasible [integer] solutions was reached.
16Stop chosen when the maximum number of feasible [integer] subproblems was reached.
17Solver converged in probability to a global solution.
18All variables must have both upper and lower bounds.
19Variable bounds conflict in binary or all different constraint.
20Lower and upper bounds on variables allow no feasible solution.
A sample screenshot of the worksheet is shown below. Optimize Outputs The graphs section shows each optimization objective function for each variable, and the optimal point is shown in red. If the optimal points are incorrect, check your inputs to see if you have correctly defined them.


The following examples are in the Examples folder.
  • Find the optimal value of an input variable (x) for which we want to optimize the function (x^2 - 3*x + 4). We would like to minimize this function, and the range of values for x should be between 0 and 10.

© Rapid Sigma Solutions LLP. All rights reserved.