SOLVER problem with a lot of restrictions (BEST POSSIBLES VALUES)

Originally Posted by dontafy

All projects that are Status = 1, are already done. And Status 0, need to be done.
[...]
Projects that are already done (1) can't change for undone (0).

Do you mean that only projects with a 0 are variables for your problem? This is important because it will reduce them to 122.

Originally Posted by dontafy

Each project has a different percentage when it is done. It's important to know that some projects are only punctuating when other 2 projects are also finished. For example, the project "j" only punctuating if project "h" and "i" are already finished.

I need to find the project that I can do in a week to see whose give me the best Result "I1"
but I have a limitation, that is described in cell "I2". For this example, I need to search what the best 5 projects that I need to focus to give me the best possible result.

First, if projects with 1 are already finished and can't be set to 0, we can look for the best result among the remaining ones. Is this correct?

Second, I don't understand how to deal with multiple projects.
Two sets of them will give you 3*2=6 single projects, but you are looking for the best 5, so your solution should at most have 2 singles and a set. Can you clarify this point?

I hope someone else can help you with the automation through VBA (maybe Alf if he sees this thread), I think it is an interesting but time consuming project.

However, I have found a solution for your problem.

- For each single project there will be a binary variable.
- In a set of projects, if only one is still unfinished than we add a simple binary variable.
- If only one is finished, we have two variables and a constraint imposing A1=A2 (A1 being the status of project 1).
- With 3 unfinished projects there are two constraints, A1=A2 and A1=A3.
- The sum of variables is set equal to a given number (5), the sum of percentages is the objective function to maximize.

In the objective function we must take care of projects within a set that are already finished. I have translated the logic condition in a set of two constraints and a binary variable.
If A1, A2 A3 are the projects, and A1 is finished, we want to set a variable B that will be 1 if and only if both A2 and A3 are equal to 1 (this binary variable will then multiply the percentage to be added to the total).

A2+A3-2*B<=0
2*B-A2-A3<=0

These equations are changed when only 1 project is still unfinished: A3-B<=0 and B-A3<=0.

With this setup the solution (using Simplex LP and 0% as integer optimality in the options) with 5 projects will include a set and 4 singles for a total of 16.896%.
The selected projects are highlighted with a conditional formatting.

I have tested the model changing some percentage and number of projects and it seems to be able to choose the best ones.

Since there is a limit in the number of characters that Solver can accept in the variable cell reference, I have reordered your data to keep variables as close to each other as possible. You may wish to find an alternate way for ordering your worksheet.

HTH

Well I had a go at it and the result was some good and some bad.

First of all I used "OpenSolver" to optimize the result as the excel solver did not manage to handle the number of cells.

There are two macros in in the uploaded file I would suggest you start by running the macro "ColorMarkere"

This macro traces all the dependencies of a cell in the D column if if that cell needs a 1 in the 3 cells in the B column. These dependent cells colored yellow.
If B cell value is 1 the in the last dependent cell B value and the corresponding D cell value start with the formula "=(If(SUM ..." than that D cell is colored red. See cell D30.

The OpenSolver will add a binary value in range E2:E208 but there is a constraint stating that the value in the F column should be equal to or less than 1 and since
the F value is equal to the value of the B cell and the corresponding E cell value this stops OpenSolver from setting a binary value in the matching E cell if the value of the B cell is already set to 1.

Scrolling down the values in the E columns checking the results the first problem is the OpenSolver has put a 1 in E11 but as D11 is dependent on h and i this is
not a good idea. Lets remember that.

Cell D30 is marked red but the values in the two dependent cells are small and OpenSolver has rightly ignored them.

Cell E32 is marked as a cell to select and that looks ok.

Cell D47 is marked red but the values in the two dependent cells are small and OpenSolver has rightly ignored them.

Cell E76 is marked as a cell to select and that looks ok.

Cell D98 is marked red but not selected by OpenSolver because B98 = 1, but if cell B97 is set to 1 then the dependency is fulfilled and full points for D98 is achieved.

Cell D124 is marked red but the values in the two dependent cells are small even if one is missing on value in B123 to get the result.

Cell E178 is marked as a cell to select and that looks ok.

So this is the bad part you have to scroll down the results and make a manual adjustment.

There is also a macro named "RunOpenSolver" this macro will run OpenSolver after you have

1) downloaded this freebie from the University of Auckland NZ and installed it on you PC.

https://opensolver.org/

2) You also need to set a reference to both the excel solver and OpenSolver "Visual Basic" icon -> Tools -> References and tick box "solver" and "OpenSolver"

Alf

Alf is absolutely right, the problem can be solved entirely with formulas.

I have changed as little as possible the original layout:

- there is a manual color formatting for the sets of projects; of these rows we use only the last one with three formulas in column D, E, F

=SUM(C9:C11)
=COUNTIF(B9:B11,0)
=SE(E11<>0;D11/E11;0)

that give us the total percentage, the number of unfinished projects in the set and the average percentage.

- for regular projects there are again three formulas

=IF(B2=0,C2,0)
=IF(B2=0,1,0)
=IF(E2<>0,D2/E2,0)

giving us the % (taking into account the status), the weight (always zero or 1) and again the percentage.

Using this setup, the best projects are ordered with

=LARGE(($F$2:$F$208*($E$2:$E$208<=$J$2-H6+1)),H6)

When we consider a set with more than 1 unfinished projects, a conditional formatting will check for the current total of projects already found and gray out rows that are outside the given number in J2.

The name of the project in the table is correct for single projects, but for sets it will refer to the last element (for instance, cs with the original data).

HTH

With time on my hands I made a macro driven setup that don't uses solver still a manual evaluation is needed to determine what values to choose.

To test run macro "FindMaxVal" this macro will ask how many high values it should find so enter 5 for instance and click OK.

There are now 3 new columns the first one is labeled "Missing" and the value shown in E11 tells that are 3 jobs that must be done (H9, I10 and J11) before one gets the full
the full points that are shown in cell F11. The green color in G11 shows that this is one of the 5 highest values found in range C2:C208.

So the macro reacts on the C11 value of 2,580% and not on the F11 value of 3,696%. But again to get this one has to do 3 jobs better wait a bit.

The next suggestion is C32 with a value of 2,662%, seems ok as well as C76 with 4,845%. CF92 with 2,309% is ok and C98 only need a 1 in B97 to get the full score for all the three cells 5,223%.

That was 4 values taken care of, what about the fifth value? Starting filling the B9:B11 range with a 1?

Another option is to run the "ClearForNew" macro and do a rerun but this time enter a 6 when macro asks for how many high numbers it should find. Five of these found values are of cource the same as before but ther is a 6th value now cell C168 that is 1,856%.

So ether one chooses C32, C76, C92, C97 and C9 or does C32, C76, C92, C97 and finally C168 and wait a bit after more jobs are done and the other remaining values are lower?

Alf