Excel Spreadsheet Function to Optimize Cells (without Using the Excel Solver) Version 6
The Excel Solver is a powerful tool for solving complicated problems. But what if you want something smaller and more compact like… an Excel spreadsheet function?
MinimizeNonnegative() combine the power of a nonlinear optimizer with the convenience of a spreadsheet function. Unlike the Excel Solver, these functions automatically respond to changes in the spreadsheet and recalculate like any other formula. As an added bonus,
MinimizeNonnegative() can optimize some things that the Excel Solver cannot.
Download the spreadsheet for Minimize() with examples and full VBA source code.
Updates in Version 6
Version 6 includes several calculation options and a bug fix. This version is backward-compatible; the usage and syntax are exactly the same.
In the VBA code the constant
CalculationMode controls how cells are recalculated during optimization. One way to recalculate is with
Application.Calculate(), which causes Excel to recalculate everything that is open. Another way is to find the target cell’s precedent cells and to recalculate them only. (See Colin Legg’s RAD Excel blog for more details about finding and recalculating precedent cells in Excel.) In spreadsheets where there are few precedent cells or the dependency trees are very small, the precedents method is faster. On spreadsheets with complex cell dependencies, the “ByApplication” method is faster (because cataloging cell dependencies might take longer than the calculations themselves; Excel does not give us direct access to the dependency tree, and it takes time to recreate it). There is yet another calculation method called “ByNaivePrecedents”. This finds and recalculates precedents using the Precedents property of the target cell. This method is “naive” because it only works if all of the precedents are on the same worksheet. If two precedents happen to be on two different worksheets, one of them will not be recalculated! The default is “ByApplication”, but you can change this if you want to experiment with the other methods. If you find that
MinimizeNonnegative() are running too slow, the solution is to change this constant.
Bug Fixed – Crash!
When Excel’s calculation method is set to manual, Excel tries to recalculate the workbook when you save it. So if you hit the save button, Excel is trying to save spreadsheet data at the same time as
Minimize() is making changes to spreadsheet data. Excel does not protect against that, but this version of the macro won’t try to change the spreadsheet data during a save operation. The fix involves adding some lines of code to the Workbook module. If you import the macro into your own workbooks, make sure to also copy the two events
Workbook_BeforeSave into the Workbook module. If you experience crashes, then you need to check that the two events were copied over.
Pingback: Constrained Nonlinear Optimization in Excel without the Excel Solver « Math for Mere Mortals
Pingback: Excel Spreadsheet Function to Optimize Cells (without Using the Excel Solver or Goal Seek) « Math for Mere Mortals
Pingback: Excel Macro to Find and Calculate Precedents | RAD Excel
I am having problems with Excel 2013 Solver VBA and found your site in Google search.
I am trying out MinimizeNonnegative BUT it is returning negative values for some items?
Why would this be?
There may be a bug, but I haven’t been able to recreate the problem. This kind of thing might happen if the formula isn’t entered correctly. For example, one person was entering =+MinimizeNonnegative and the plus sign prevented the function from optimizing when the spreadsheet changed. Can you share an example that produces a negative result despite the constraint? If so, I’ll send you an e-mail address to send it to.
I have managed to get the file down from 30MB to 3MB so it should be OK to email.
Drop me your email address.
Tried email but got no reply. Did you find out why the file I sent gave a negative result?
I have Excel 2013. I don’t see the minimize function anywhere.
Hi, Pam. This is not built into Excel; it is a custom VBA function. You need to copy the modules into your own workbook and update the workbook module. After you do that, the functions will be available in that workbook. The example available for download already has everything copied in.
On Monday, October 24, 2016, Math for Mere Mortals wrote:
I have copied the Modules into my own workbook. This has seemed to enable them to work, but I get the following error:
Run-time error ‘9’:
Subscript out of range
Any thoughts what might be causing this?
Hi, Doug. Does the example workbook open and calculate without errors? Do you see the error message when you copy the modules or do you have to enter a formula first? Can you see which line is causing the problem? One person had a similar issue when he entered the formula as =+Minimize… (with an errant plus sign in front of the function).
On Mon, Oct 31, 2016 at 5:39 PM, Math for Mere Mortals wrote:
Hi Kintaar, Thanks for your response. The example workbook opens and calculates without error. I don’t see the error until I enter a formula. I did not see the error when I just copied the modules. I can not see which line is causing the problem. Unfortunately, I do not have a “+” sign or other errant character causing the error.
Do the modules need to be used with a certain version of Excel? Does the Solver Add-In affect the code? Do I need to turn on or anything particular within the specific workbook that I am trying to use the code in?
I get the 64 bit error saying I need to modify declare statements before the code will work in my 64 bit excel. I can just get out my old laptop that has 32 bit until I can figure out what they are taking about. From what I’ve learned so far that doesn’t look like it is going to be easy.
Hi, Mike. It should work in 64-bit Office Excel. The compiler directives are in place in the SuccessiveQuadraticOptim6 module. Look for this line: #If CBool(Win64) Then I am using Excel 64, and it opens and runs on my computer. Does it give a specific error message or reference a certain line number? That information would help track down the issue, which could be related to macro security or some other thing.
On Tue, May 2, 2017 at 11:25 PM Math for Mere Mortals wrote:
I see this is a bit of an old post so hopefully somebody is still monitoring! I found your site looking for a method of optimization because Solver doesn’t seem to like anything I do on excel – firstly, I want to say this function is fantastic! It does exactly what I’m looking for and the results I’m getting are in line with what I’d expect using a more ham-fisted (i.e. trial and error) approach.
What I’m looking to do is set up the optimize function in such a manner that it doesn’t run if a precedent input is 0 (I currently have 4 optimize functions for 4 variables all linking to set a single cell to a minimum – however, in some cases I want to set the variable to 0 regardless of what the “optimal” range of results might be – at first I set a range of cells (say A1:D1) as the variables for optimize to change, but with a range (A2:D2) that sets each of the above cells to 0 if a specific pre-requisite cell is also set to 0 – this sometimes works fine, but occasionally optimize will run through and produce an exponential result trying to find the best solution (which doesn’t exist since the result is always 0) and stop running the rest of the optimize functions.
I initially tried to set the optimize function such that “IF(A3=0,0,optimize(…)), however this throws up an error which says ‘”IF(A3=0,” is not a valid input’, which led me to the solution above.
Is there a way around this? I’m trying to make the spreadsheet as efficient as possible, otherwise I may need to save multiple versions, which is not ideal!
Graeme, I’m glad you like it. What you describe is an interesting approach, which should work just fine as long as there is no interaction between the input variables that you are optimizing. It sounds like you are trying to prevent optimization from taking place whenever the input variable starts at a value of zero. I believe I have found a solution to this that doesn’t involve changing the code. In the example spreadsheet there is a formula for a merit function in Cell F12. Change it to: =IF(ABS(D12)>0.00001,(E12-D12^2)^2,5) This has the effect of suppressing optimization when D12 has a starting value at or near zero. The optimizer uses a central difference technique to calculate the gradient at a point, and this formula forces the gradient to be zero when D12 is near zero. When the optimizer can’t see a gradient, it just defaults to the central point of the central difference (which in this case is precisely zero). The last parameter in the formula is the number 5, but it can be anything, as long as it’s a constant. Bear in mind that this workaround can potentially interfere with the optimization if and when the parameter passes through zero (e.g. if it starts at -1 and passes through 0 on its way to an optimum of, say, 3). For that reason, the best choice for the constant is the merit function value at that point. In this example it should have been 10,000; not 5. Here is another way to formulate that without manually plugging in a number. This is the better approach: =IF(ABS(D12)>0.00001,(E12-D12^2)^2, (E12-0^2)^2 ) I left in the 0^2 to make it clear that I’m plugging in a zero for D12.
You are creating a singularity (technically two singularities) in the gradient, and you can put it wherever you want. For example, ABS(D12-1.5) would put the singularity near 1.5 if you find that you need something like that.
On Fri, Mar 16, 2018 at 7:24 AM, Math for Mere Mortals wrote:
Thanks for posting this.
Is there a way to set a minimum and maximum for MinimizeNonnegative?
Essentially, I’m trying to use the function to calculate optimal input results for a cell that would minimize the value of another cell (output cell). For example, let’s say I run the MinimizeNonnegative function and it returns a value of 0. Is there a way to run the function so that the answer must be between 1000 and 5000? The reason why I need this is because I have a column of inputs that I’m using MinimizeNonnegative to calculate each input that affects one output I’m trying to optimize.
So I have:
I run MinimizeNonnegative on each B cell (one of 5 inputs that affect G2), and each B cell affects the other B cells as well as G2 (output cell that I’m trying to minimize). Let’s say I get the following results when I run MinimizeNonnegative:
B2 = 660
B3 = 2800
B4 = 3000
B5 = 1880
B6 = 0
Is there a way to run MinimizeNonnegative to make sure all the cells are within 1000 to 5000? This would change cells B2 and B6 which would consequently affect the other B cells to minimize the G2 output cell.
Let me know if this makes sense. Happy to elaborate. Thanks!!
Pingback: Regularizing 3D data