Solver speed up help

[leigh740]

Hi

I am trying to use Solver to put together orders to make a load percentage of 85%. I have the below;

Date	Customer	Order No	Percentage
25/06/2023​	Customer A	1	2
26/06/2023​	Customer A	2	6
26/06/2023​	Customer A	3	35
27/06/2023​	Customer A	4	18
27/06/2023​	Customer A	5	5
27/06/2023​	Customer A	6	4
27/06/2023​	Customer A	7	8
27/06/2023​	Customer A	8	15
27/06/2023​	Customer A	9	19
27/06/2023​	Customer A	10	6
27/06/2023​	Customer A	11	6
27/06/2023​	Customer A	12	11
27/06/2023​	Customer A	13	23
27/06/2023​	Customer A	14	31
27/06/2023​	Customer A	15	6
27/06/2023​	Customer A	16	38
27/06/2023​	Customer A	17	36
27/06/2023​	Customer A	18	32
27/06/2023​	Customer A	19	19
27/06/2023​	Customer A	20	5
27/06/2023​	Customer A	21	36
27/06/2023​	Customer A	22	14
27/06/2023​	Customer A	23	58

The sheet also has a sumproduct based on the percentage column (d2:d30) then j2:j30. Solver tries to make the sumproduct equal 85 by changing the characters in j2:j30 as binary characters. Initially solver used to take about a minute but now takes into the tens of minutes. Is there any advice on how to speed up the solver please?

Thanks in advance

 

[Alfb]

Looking at your problem I assume you wish maximize the number of loads that adds up to 85%. The total weight of the 23 orders are 433. This number divided by 85 is 5.094. So you could then distribute the total load in 6 boxes. The range F27:I27 set the constraint for the percentage of the 4 first boxes i.e. equal to 85, the range J27:K27 sets the percentage constraints for box 5 and 6 i.e. equal to or less than 85. This in order to give solver certain degree of freedom as we know that theoretically that solver may fill 5 boxes to max with a small balance in the 6th.

This problem seems to complex for the Excel solver but there is a freebie developed by the University of Auckland NZ called OpenSolver that integrates nicely with Excel.
OpenSolver for Excel – The Open Source Optimization Solver for Excel

The image Solver_model shows the result Open Solver found after 0.46 seconds. The solver_stup shows the solver setting (build by using the Excel solver) but run by OpenSolver..