Forum Discussion
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 use Excel to solve that.
Here is the problem:
There is a span of 20 m which has to be filled with the prefabricated elements with standard spans of 3m, 4.2m, and 5m. I need to find out about the best combination of the standard elements which gives the least leftover.
I do appreciate your help and time in advance.
Best Regards,
Farshid Kaviani
7 Replies
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 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
FarshidKaviani , You can set the constraints as ranges instead of individual cells. See example. Hope that helps.
FarshidKaviani Perhaps the attached schedule can help you resolve your problem, or at least give you an idea re how to approach its solution. Enter the span and maximum tolerated waste at the top and in the matrix below, you'll see green numbers for exact matches and red ones for those that fall below the accepted waste. If it's not at all useful, kindly disregard.
Riny_van_Eekelen Thank you so much for your effort. I was looking for an automatic solution that does not involve so many manual data entries since the standard spans cover quite a wide range, but I found your solution very useful for optimizing the waste optimization which is equally important in my project.
I do appreciate your help!
Regards,
Farshid
