Linear and Nonlinear Functions

The objective function in a Solver problem is some calculated value that depends on the decision variable cells; the job of the Solver is to find some combination of values for the decision variables which maximizes or minimizes the objective function. During the optimization process, only the decision variable cells are changed; all other "input" cells are held constant. If you analyze the chain of formulas which calculates the objective function value, you will find that parts of those formulas (those which refer to non-decision variable cells) are unchanging in value and could be replaced by a numeric constant for the purposes of the optimization.

If you follow the suggestion above and use only constant values on the right hand sides of constraints, then the same observation applies to the left hand sides of the constraints: Parts of the constraint formulas (those which refer to non-decision variable cells) are unchanging in value, and only the parts dependent on the decision variables "count" during the optimization.

The mathematical form of the relationship between the objective function and constraint cells and the decision variables has important implications for the difficulty of the problem, the Solver "engine" that can be used, and the speed of solution. The simplest and most common case is where the objective function is a linear function of the variables. This means that the objective can be written as a sum of terms, where each term consists of one decision variable multiplied by a (positive or negative) constant. Algebraically, we can write:

max (or min) a1x1 + a2x2 + ... + anxn

where the ais, which are called the coefficients, stand for constant values and the xis stand for the decision variables. Remember that the ais need only be constant in the optimization problem, i.e. not dependent on any of the decision variables. As an example, suppose that the objective function is +B1/B2*C1+(D1*2+E1)*C2, where only C1 and C2 are decision variables, and the other cells contain constants (or formulas that don't depend on any of the decision variables). This would still be a linear function, where a1 = B1/B2 and a2 = (D1*2+E1) are the coefficients, and x1 = C1 and x2 = C2 are the variables.

Note that the @SUMPRODUCT function computes exactly the algebraic expression shown above. If we were to place the formula +B1/B2 in cell A1, and the formula +(D1*2+E1) in cell A2, then we could write the example objective function as:

@SUMPRODUCT(A1..A2,C1..C2)

A nonlinear function, as its name implies, is any function of the decision variables which is not linear, i.e. which cannot be written in the algebraic form shown above. Examples would be +1/C1, @LOG(C1), +C1^2 or +C1*C2 where both C1 and C2 are decision variables. If the objective function or any of the constraints are nonlinear functions of the variables, then the problem cannot be solved with an LP Solver.