Using Excel solver (or other method) with a common objective in multiple cells by changing the same variables, subject to constraints

[irwin259]

Hi,

I am struggling with quite a complex problem in which I think I may be able to solve or at least partially solve using the Excel solver tool.

I am trying to set up a design of experiments analysis in which I have a number of parameters at two levels.
I have one specific objective which I would like to be constant for each experiment, mass, e.g 15 grams.
I also have written up some constraints on what values the parameters can and cannot be.
I would then like to execute some kind of solver to optimise my parameters subject to constraints to achieve a constant mass (or at least as close as possible).

The excel solver is perfectly set up for this problem for a single objective cell but I need this tool to work for about 32 cells which are calculated using the same variables.

High and low level parameters and constraints

Design matrix and total mass

 

[offthelip]

I never use solver to optimise calculations , I always do it by using multiple vba loops. I find it is much more flexible because you can check for multiple conditions and do also sorts of complicated things in the loop which you can't do with solver..
You haven't given any indication of what criteria you are using to optimise or where your results are. So here is a bit of code to give you the general idea of how I would do it.

Sub test()
bestresult = 0
H2 = 16
Range("C2:C32") = H2
For r2 = 16 To 17 Step 0.1
   Range("A2:A32") = r2
    For r4 = 8 To 8 Step -0.1
       Range("B2:B32") = r4
        For h3 = 47 To 45 Step -0.2
           Range("D2:D32") = h3
            ' now check the results, this is a check just to demonstrate that at this point you can any calculation on your output array to determine the optimum result
             inarr = Application.WorksheetFunction.Max(Range("H2:H32"))
              If inarr > bestresult Then
               bestresult = inarr
               indextxt="r2=" & r2 & "r4=" & r4 & "h3=" & h3
              End If
       Next h3
   Next r4
Next r2
txt = "Value=" & bestresult & indextxt
MsgBox (txt)

End Sub

 

[irwin259]

Thanks for this code, although this is not specifically what I am looking for.
I would like the mass i.e Range H2:H32 to be constant.
The mass is dependant on the variables; R2, R4, H2 etc
Thus I would like to find values of of these variables for which the mass can remain constant.
The design matrix is what determines the combinations of parameters that are used (low -1 or high +1).

 

[offthelip]

As I said, you haven't specified your problem sufficiently for me to design a loop to solve your specific problem. To do that you need to specify:
What are the input variables
What ranges are each of them going to vary over
What size step do you want to specify as the initial step value for each variable.
where each input variable is going to be written into the worksheet
any other constraints which must be adhered to
What are the outputs from your equations
Where are they read from on your worksheet
What are the equations which are to be used to assess which solution is best
Where is the final result to be written

( Note that I am saying I am going to do it, it depends on my time. but once you have done this writing the loops is very easy)