Javascript required
Skip to content Skip to sidebar Skip to footer
Home / Excel Solver Could Not Find a Feasible Solution

Excel Solver Could Not Find a Feasible Solution

Header Logo

Optimization

Overview

Optimization template can be used to optimize a set of inputs in order to achieve certain objectives in the presence of constraints.

Using software

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

Inputs

Click on Update Inputs to open the menu options for this template.

Variables A sample screenshot of the input design variables that should be optimized is shown below. inputs Enter the following information for the input variables:

  • Number of 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 template.
  • 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. They should not contain spaces or special characters.
  • 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 for each variable is that it is a real number.
  • 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. If you do not have a minimum limit, just pick a large negative number.
  • Initial Value: 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.
  • Maximum: Specify a maximum value for the variable. The algorithm will only look for values less that this number. Hence, the maximum value also acts like a constraint for your optimization problem. If you don't have a maximum value, pick a large positive number.

Objectives A sample screenshot of the objectives for the optimization is shown below. inputs Enter the following information for the objectives:

  • Number of 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.
  • 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. 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.
  • Target: 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.
  • 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.

Constraints A sample screenshot of the constraints for the optimization is shown below. inputs Enter the following information for the constraints:

  • Number of 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.
  • Equality 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.

Options A sample screenshot of the options for the optimization problem is shown below. inputs Enter the following information for the options:

  • 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 solve 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 equal zero. This method is usually fast, but the limitation could be that it may not reach the global minima. 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 global optimal solution. However, this can only be used in the 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 minima.
  • Precision: Specify the degree of precision you want for the optimization. This value is used to check if your constraint is satisfied within this amount. The smaller the number, 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 a value between 0 and 1, it is the relative frequency with which some member 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 Size: In the Population Size box, type the number of different starting points (values for the decision variables) you want the multistart 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 multistart method uses a population size of 10 times the number of decision variables, but no more than 200.
  • Random Seed: Random 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 Start: Select the multistart option to use the multistart 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 multistart method should run only if you have defined lower and upper bounds on all decision variables in the Constraints list box. The multistart 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 multistart method is likely to perform.
  • Max Time: Specify the number of seconds the optimization algorithm is allowed to run in order to find a solution. Larger the max time, more likely it is to find an optimal solution.
  • Max Iter: Specify the maximum number of iterations that the optimization algorithm is allowed to run to look for a solution. Larger the number of iterations, more likely it is to find a solution but longer it will take to perform the optimization.

Outputs

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:

Status Description
0 Solver found a solution. All constraints and optimality conditions are satisfied.
1 Solver has converged to the current solution (may not be optimal). All constraints are satisfied.
2 Solver cannot improve the current solution. All constraints are satisfied.
3 Stop chosen when the maximum iteration limit was reached.
4 The Objective Cell values do not converge.
5 Solver could not find a feasible solution.
6 Solver stopped at user's request.
7 The linearity conditions required by this LP Solver are not satisfied.
8 The problem is too large for Solver to handle.
9 Solver encountered an error value in a target or constraint cell.
10 Stop chosen when the maximum time limit was reached.
11 There is not enough memory available to solve the problem.
12 Cannot solve the model since Solver.dll is being used by another program.
13 Error in model. Please verify that all cells and constraints are valid.
14 Solver found an integer solution within tolerance. All constraints are satisfied.
15 Stop chosen when the maximum number of feasible [integer] solutions was reached.
16 Stop chosen when the maximum number of feasible [integer] subproblems was reached.
17 Solver converged in probability to a global solution.
18 All variables must have both upper and lower bounds.
19 Variable bounds conflict in binary or all different constraint.
20 Lower and upper bounds on variables allow no feasible solution.

A sample screenshot of the worksheet is shown below. 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, do make sure to check your inputs to see if you have properly defined them.

Examples

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.