Help Manual


Sigma Magic Help Version 15



The optimization tool can be used to optimize a set of inputs to achieve certain 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.
Define Variables: 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. They should not contain spaces or special characters.
Variable Type: Specify the type of variable. You can specify the type of variable as Real (it can take any real number subject to constraints), Integer (the variable can only take integer values), Fixed (the variable cannot be changed from the initial value, Binary (the variable can only take values 0 and 1). The default is set to a real number.
Variable Range: 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. If you do not have a minimum limit, just pick a large negative number. Specify an initial value for your variable. It is recommended that the initial value you specify should 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. 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. If you don't have a maximum value, pick a large positive number.
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 on the Objectives tab in the dialog box. A sample screenshot of the objectives is shown below. inputs
Num Objectives: Specify the number of objective functions you want optimize. You can optimize up to 10 objective functions at the same time. If you have multiple objective functions, the software will automatically combine the different objective function into a master objective function depending on the relative weights specified for the objective functions.
Objective 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.
Objective Type: Specify the type of objective function. Here, you have the options of Minimizing the objective function, achieving a specific target value, or maximizing the objective function. Note that if you specify None as the objective type, this objective function is not used in the optimization. If you specify that the objective is to achieve a specific target, you should also specify a target value.
Weight: Specify the weight for the objective functions. These weights are used if you have multiple objective functions to combine them together into a single objective function. Note that the weights can take any real number. Larger the weight, more important is that specific objective function.
Equation: Define the equation for computing the objectives. This should use a valid equation using the variables defined in the variables tab. An example of an equation is x1 * x2 ^ 3 + (x4 - 5)^2. You can use any standard mathematical functions to define the objective function. You can also copy and paste the transfer function from other analysis such as DOE, Regression etc.
Target Value: Specify the target value for achieving a specific target. This value cannot be entered if you are trying to minimize or maximize the objective function. Specify the worst case values for the objective function under Lower Limit and Upper Limit. These values are used to compute the desirability function for this objective.
Once all the objectives have been defined, the next step is to define 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.
LHS: Specify the Left Hand Side (LHS) of the constraint. 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 with respect to 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. You should use the same variable names as specified earlier to define the constraint equation or the RHS value could be a constant.
Finally, specify any options for the optimization.


The optional setting can be opened by clicking on the Options button on the dialog box. A sample screenshot of the options for the optimization problem is shown below. inputs The following options can be set.
MethodSpecify 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 determine that it has reached the optimum solution when the partial derivatives equal to zero. This method is usually fast, but the limitation could be that it may not reach the global minimum. To use GRG nonlinear optimization, the objective functions should be relatively smooth. If all the objective functions and constraints are linear, you can choose the Simplex LP optimization. This method will always result in a globally optimal solution. However, this can only be used in limited cases when all the objectives and constraints are linear functions of the variables. The Evolutionary algorithm is more robust compared to GRG Nonlinear and may reach the global optimum, but it is very slow. It uses the theory of natural selection and uses random starts and mutations to perturb the solution looking for a global minimum.
PrecisionSpecify the degree of precision you want for the optimization. This value is used to check if your constraint is satisfied with this amount. The smaller the number, the higher the precision.
ScalingIf 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.
ConvergenceSpecify 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.
DerivativesSpecify 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 RateThe mutation rate is a value between 0 and 1, it is the relative frequency with which some members of the population 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 SizeIn 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 SeedRandom seed is a positive integer number to be used as the (fixed) seed for the random number generator used for a variety of 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.
Multi StartSelect 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 BoundsSelect 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 that you can specify, the better the multi-start method is likely to perform.
Max TimeSpecify the number of seconds the optimization algorithm is allowed to run in order to find a solution. The larger the max time, the more likely it is to find an optimal solution.
Max IterSpecify the maximum number of iterations that the optimization algorithm is allowed to 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 perform the optimization.


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 show a summary of the input variables with variable name and settings (min, initial value, and max). It will show the objective functions and the type of optimization and the list of constraints. The method used to solve the optimization is listed as well. The algorithm will run some basic checks to make sure the inputs are okay, but it is your responsibility to ensure that the objective functions are defined properly. Verify the equation in the Inputs section of the worksheet. The analysis results will show the values for the variables (initial value as well as the final optimized values). The conclusion section will briefly state if the optimization was successful. The different status 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 Solver.dll is being used by another program.
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 will show each of the optimization objective functions for each variable and the optimal point will be shown in red. If the optimal points are not correct, make sure to check your inputs to see if you have properly defined them.


Following examples can be found 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.