I'm trying to find the maximum possible value of a cell which contains a formula. The result of the formula is dependent upon some binary values. I've created an example. It's a much simpler workbook, but it has all the same problems.
In the ScreenShot:
- Column A contains values of x which are either 10 or 1
- Column B contains values of y which are either 10 or 1
- Column C contains values of Binary which are either 0 or 1
- Column D contains values of Selection which are based on a simple formula which returns the value of x (column A) if Binary (column C) has a value of 0, and returns the value of y (column B) if Binary (column C) has a value of 1. eg. the formula in cell D2 is: =IF(C2=0,A2,IF(C2=1,B2,0))
- Cell E2 is the sum of the selections (column D).
I've written three macros to find the maximum value in cell E2 by:
- Varying all 10 values of Binary (eg. cells C2:C11)
- Varying the first two values of Binary (eg. cells C2:C3)
- Varying the first value of Binary (eg. cell C2 only)
Each time I run a macro, I reset the binary values to original (eg. as shown).
Macro "Maximise10" should return a value of 100:
Sub Maximise10()
Dim CellToChange, CellToSolve As String
Sheets("Example").Select
CellToChange = "C2:C11"
CellToSolve = "E2"
SolverReset
SolverOptions Precision:=0.1, Convergence:=0.5
SolverOK SetCell:=Range(CellToSolve), MaxMinVal:=1, ByChange:=Range(CellToChange), Engine:=3
SolverAdd CellRef:=Range(CellToChange), Relation:=5
SolverSolve UserFinish:=True
End Sub
Macro "Maximise2" should return a value of 28:
Sub Maximise2()
Dim CellToChange, CellToSolve As String
Sheets("Example").Select
CellToChange = "C2:C3"
CellToSolve = "E2"
SolverReset
SolverOptions Precision:=0.1, Convergence:=0.5
SolverOK SetCell:=Range(CellToSolve), MaxMinVal:=1, ByChange:=Range(CellToChange), Engine:=3
SolverAdd CellRef:=Range(CellToChange), Relation:=5
SolverSolve UserFinish:=True
End Sub
Macro "Maximise1" should return a value of 19:
Sub Maximise1()
Dim CellToChange, CellToSolve As String
Sheets("Example").Select
CellToChange = "C2"
CellToSolve = "E2"
SolverReset
SolverOptions Precision:=0.1, Convergence:=0.5
SolverOK SetCell:=Range(CellToSolve), MaxMinVal:=1, ByChange:=Range(CellToChange), Engine:=3
SolverAdd CellRef:=Range(CellToChange), Relation:=5
SolverSolve UserFinish:=True
End Sub
I only get results with the "evolutionary" method. In the solver options, I've tried adjusting:
- Iterations
- Precision
- Scaling
- AssumeNonNeg
- PopulationSize
- MutationRate
- MaxSubproblems
The code above has provided the best results yet, but the outcome is less than satisfactory.
- The "Maximise1" sub should be the easiest problem to solve because there should only be two options. Excel regularly locks up (eg. not responding) when I run this macro with different options. If it doesn't lock up, Solver continues to run without finding a solution.
- The "Maximise2" sub should be relatively easy because there are only four options to be considered.
- Despite having binary constraints, solver consistently considers non-integer values in column C.
I would very much appreciate some assistance to determine how to best configure the solver add-in to ensure it only considers integer values to determine a maximum value in the fastest, most efficient manner possible.
