Constrained Optimization in Excel
Here we see how to do constrained optimization with an Excel spreadsheet function. The spreadsheet function
MinimizeNonnegative() imposes a nonnegativity constraint on the variable. This is useful when you’re solving for something that can’t be negative, like energy or an interest rate.
Download Excel Optimization Function (Minimize and MinimizeNonnegative) v5
(Note: This spreadsheet has both
MinimizeNonnegative() usage examples.)
Examples of Excel Constrained Optimization
Suppose we need to find the logarithm of 0.5 without using a logarithm function. As before, we use
Minimize() and there are no constraints on x. The answer is -0.693. No surprise there!
Now the same thing, but constrain the result to be nonnegative. This time we’re using
MinimizeNonnegative() instead of Minimize() so that x cannot go below zero. This time the answer is 0, not -0.693 because the spreadsheet function won’t let x go below zero. x con only be between zero and infinity.
Constraints Other than Zero
The constraint doesn’t have to be zero. Here is an example where x is constrained to be at least -0.45.
The minimizer is modifying the value of J, but the answer x is in a different cell. The answer is -0.45.
We do this by adding another cell to the mix. We’ll call that J. Instead of optimizing by changing x, the optimizer is changing J. Then we make x a function of J like x = J – 0.45. The merit function is still a function of x; that much we keep the same.
Where J cannot go below zero, x cannot go below -0.45 because of the way it relates to J. Meanwhile the merit function is based on x. In other words, the Excel optimization function only constrains at zero, but we’re finding a clever way to get around that. We can have any constraint we want; we’re having our cake and eating it too! The spreadsheet shows examples of all combinations of constraints for x: less than, greater than, etc.
This VBA function uses the same algorithm as the previous post Excel Spreadsheet Function to Optimize Cells (without Using the Excel Solver or Goal Seek) with the addition of a nonnegativity constraint applied to the initial line search. The optimization routine still uses the false position method, but its starting condition is constrained and it can detect when the minimum is located at (or below) zero.
When you have constraints (and the constraints are active), the optimization algorithm has to evaluate the merit function more times than in the unconstrained case. This can slow things down, so it is best to use constraints only when you really need them. For example, in this spreadsheet a typical optimization may take 3 milliseconds instead of 1 millisecond. You’ll notice the difference if you are optimizing a thousand cells.