Minimize and MinimizeNonnegative Spreadsheet Optimization v6

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?  Minimize() and 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, Minimize() and MinimizeNonnegative() can optimize some things that the Excel Solver cannot.

Animated Demonstration of Minimize

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.

Minimize Excel Function Syntax

Function Syntax Example in Excel

Calculation Modes

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 Minimize() and 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_AfterSave and Workbook_BeforeSave into the Workbook module.  If you experience crashes, then you need to check that the two events were copied over.

Advertisements

14 comments

  1. Pingback: Constrained Nonlinear Optimization in Excel without the Excel Solver « Math for Mere Mortals

  2. Pingback: Excel Spreadsheet Function to Optimize Cells (without Using the Excel Solver or Goal Seek) « Math for Mere Mortals

  3. Pingback: Excel Macro to Find and Calculate Precedents | RAD Excel

  4. 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?

    • Hi, Sean.
      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.

    • 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:

      >

  5. 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?

    Thanks,

    Doug

    • 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:

      >

  6. 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?

    Thanks,

    Doug

  7. 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:

      >


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s