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 Minimize() and 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.
Optimization Algorithm
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.
Math for Mere Mortals 
This is great, thanks! Would it be possible to add a second constraint? So that one could specify for example 0<x<100?
Thanks for your interest in this code. To answer your question, yes. It is possible to have two inequality constraints. The primary change would be to the line search, and the function syntax would need to allow for two constraints.
A cheesy way to get what you’re looking for is to optimize the problem with the 0<x constraint and separately with the x<100 constraint, then choose whichever answer meets both conditions. This should worke fine if
…should work fine if both optimizations have the same starting point. Can you tell me what kinds of things you’re modeling?
hi! it looks like the link stopped working ! any way to update?
Sorry – what I meant is that the dropbox link is no longer updated/supported – curious if you could re-enact the reference so I can check out this awesome work.
I’m glad you think it’s awesome! Let me take a look at that and update the link…
On Wed, May 28, 2014 at 6:17 PM, Math for Mere Mortals wrote:
>
@ninja
DropBox had some problems (and I have since stopped using the account; I use box.com now and I like it.). The link should work now. I’d appreciate your suggestions for changes, additions, and improvements to the optimization function.
Great program!!! Any chance there is a a>x>y version out there yet? Trying to model power dispatch and I need to limit to values between 0 and 8760 … Thanks!!!
Thanks for your question, Brendan. We all appreciate what you do to keep our lights on and our cellphones at full charge! Your question is a good idea. A lot of optimization problems have several constraints, linear constraints with multiple variables, etc. The difficulty is that those types of constraints are so complicated that they can’t be done without a user interface (like the Excel Solver and Matlab via the “optimtool” command) or support for matrices (like Matlab). In Excel we have to make do with a quick spreadsheet formula and three parameters that are straightforward enough to be remembered by a “mere mortal.” With that said, there are several things you can do. 1. Optimize the parameter with the nonnegativity constraint only. Solve the problem again at the upper limit of 8760. Then choose the best answer with the Excel function =IF(p>8760,8760,p). This is the best way to do it with the tools you have. 2. Optimize the parameter with each constraint separately and choose the best answer. This is not as good because it will take (possibly) twice as long to optimize, and you still need an IF statement to choose the best answer. 3. Choose a starting value that will ensure you end up at or near one of the constraints. Use that one as the constraint and hope/pray that the parameter doesn’t go too far in the opposite direction. This really isn’t such a bad solution if you have a very good handle on the solution space and you know which constraint will be the active constraint. Good luck!
On Thu, Dec 3, 2015 at 5:10 PM, Math for Mere Mortals < comment-reply@wordpress.com > wrote:
>
Thanks – extremely useful!