Excel Solver - Linear Functions (2024)

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:

a₁x₁ + a₂x₂ + ... + a_nx_n

where the a_is, which are called the coefficients, stand for constant values and the x_is stand for the decision variables. A common example is =SUM(C1:C5), where C1:C5 are decision variables and the a_is 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 2x₁ +1x₂. As noted above, a linear function is always convex.

Remember that the a_is 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 a₁ = B1/B2 and a₂ = (D1*2+E1) are the coefficients, and x₁ = C1 and x₂ = 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:

=SUMPRODUCT(A1:A2,C1:C2)

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.