Forum Discussion

Copper Contributor

Jul 13, 2020

How to find best combinations in Excel

Hi There, I have a complex problem that can be solved by nested loops, which means programming skills which I am not equipped with at this point, but I thought you might have a suggestion on how to ...

excel

TheAntony

Iron Contributor

Jul 13, 2020

FarshidKaviani , As a variation to Riny_van_Eekelen 's solution, you can use the Sover add-in to solve this problem. Here I'm setting the problem up to have a solution that the length is less than 20 with all 3 lengths to be used and be integers. You can adjust the constraints and play around with the type of solution you need. Hope this helps.

Elements	3	4.2	5
Count	2	2	1
Span	19.4

Linear Program Solver.xlsx10 KB

FarshidKaviani
Copper Contributor
Jul 14, 2020
TheAntony Thank you so much for your help. I managed to solve the problem using your method but since the standard span of elements covers a wide range so I do appreciate any other suggestions that can take a range of numbers as standard element sizes.

I appreciate your time and help!

Regards,
Farshid
- TheAntony
  Iron Contributor
  Jul 14, 2020
  FarshidKaviani , You can set the constraints as ranges instead of individual cells. See example. Hope that helps.
  Linear Program Solver.xlsx10 KB
  - FarshidKaviani
    Copper Contributor
    Jul 15, 2020
    TheAntony Thank you so much. That works perfectly!!!