Want to get more from Excel, Solver and Analytics?
Attend a LIVE WEBINAR about Analytic Solver with a real expert, Dr. Sima Maleki.

In many common cases, the objective and/or constraints in an optimization model are linear functions of the variables.  This means that the function 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:

a1x1 + a2x2 + ... + anxn

where the ais, which are called the coefficients, stand for constant values and the xis stand for the decision variables.  A common example is =SUM(C1:C5), where C1:C5 are decision variables and the ais are all 1.  Note that a linear function does not have to be written in exactly the form shown above on the spreadsheet.  For example, if cells C1 and C2 are decision variables, B1 = C1+C2, and B2 = A1*B1 where A1 is constant in the problem, then B2 is a linear function (=A1*C1+ A1*C2).

Geometrically, a linear function is always a straight line, in n-dimensional space where n is the number of decision variables.  Below is a perspective plot of 2x1 +1x2.  As noted above, a linear function is always convex.

Remember that the ais need only be constant in the optimization problem, i.e. not dependent on any of the decision variables.  For example, suppose that the 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 the 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.

The SUMPRODUCT function in Excel 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 function above as:


When using SUMPRODUCT to compute a value for a linear objective or a constraint, ensure that one argument range consists only of decision variable cells, and the other argument range consists only of cells that aren’t (and don’t depend on) decision variable cells.

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 the Simplex LP Solving method.