Excel solver macro help.

November 1, 2009 11:24 AM

I have a column of around 100 equations that can only be solved with solver in Excel. Is there any way to create a macro for this purpose. The layout of the spreadsheet given below.

........................ |W1|W2|W3| SD
E(Rp1) "|............| 0 | 0 _ |_1|_0

We are seeking to minimize SD by changing W1, W2 and W3, under the constraint E(Rp1)= a certain number: which starts at say 2 and increases by .1 till 20. The question is whether macros can be written for add-ins?

All that weird formatting was to make sure the cells would line up.

posted by Lucubrator to computers & internet (3 answers total)

The question is whether macros can be written for add-ins?

A macro to run the solver, to set D7 to value of 0 by changing cell D3:

Sub Macro1()
    SolverOk SetCell:="$D$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$D$3"
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
End Sub

I've tested this in Excel 97 and it works after I add a 'reference' to solver.xla, from the 'tools' menu in the VB editor.

If you're using a newer version of Excel, I suggest you look at this documentation of the SolverOK function.

If I've misread your question and this isn't what you wanted to know about, you might want to clarify your question :)
posted by Mike1024 at 2:03 PM on November 1, 2009

There may be another way, using a math trick. If you want to minimize f1(a, b, c) and minimize f2(x, y, z) where a,b,c and x,y,z are completely separate variables, this is exactly the same as minimizing f1(a,b,c) + f2(x,y,z)


So ... you can instantiate the 200 instances of your constrained solution in excel and just tell the optimizer to optimize the sum of the SDs over all the dependent variables and just run this as one optimization.

........................ |W1|W2|W3| SD|Constraint
E(Rp1) "|............| 0 | 0 _ |_1|_0 | E(Rp1)  - 1
E(Rp1) "|............| 0 | 0 _ |_1|_0 | E(Rp1)  - 2
E(Rp1) "|............| 0 | 0 _ |_1|_0 | E(Rp1)  - 3
......................
Total...|............| 0 | 0 _ |_1|Sum of the SD Column |0

And tell the optimzer to optimize the 'sum of the sd column' variable in the 'total' row while keeping all of the 'constraint' boxes at 0 and allowing it to tweak all of the W1/W2/W3s.
posted by bsdfish at 10:38 PM on November 1, 2009

Thank you for the answers!

Mike, I added a loop to your code and it went ahead and worked beautifully.

bsdfish: The basic problem was that I'm trying to maximize F(a,b,c) and minimize F(x,y,z) and that each row was a datapoint in resulting graph.
Thanks for that unique insight though.
posted by Lucubrator at 9:28 AM on November 5, 2009