• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to sort data based on conditions

J

Jayapresad

New Member
Dear Friends,
Can anyone suggest a solution with formulas in excel.
How to pack items in the range with minimum number of carton subject to
1. Total weight in a carton should between 20 kgs to 30 Kgs.
2. In a caton max 5 items can be inluded

Please see the attached file.
Thanks
JP
 

Attachments

  • Jp sample.xlsx
    9.6 KB · Views: 5
Dear Mr. Peter,

Thanks your solutions.
I will try it now.
Could you please suggest any solutions by using formulas, so that the same can be copy & pasted used for a bigger range and we will get solutions automatically.

Thanks
Jayapresad.B
jayapresad@gmail.com
 
Sorry, I am not sure why the objective did not show on the image. In practice, I did not even attempt to minimise the number of cartons. 2 would not be feasible because they would be overweight. Conversely 4 would be underweight. All I was seeking was a 3 carton solution, any solution that met the constraints. I chose to minimise the deviation of the count of carton items from the average of 4.
= SUM(ABS(items-4))

I would not expect the method to scale well for large problems; I think Solver has limits on problem size when used within Excel. Then again, I wouldn't expect a formula-based approach to work because spreadsheets are not well-suited to iterative calculation.
I am using Office 365 and have used FILTER to display the result but, if you do not have the function then the tables to the right can be deleted.
 

Attachments

  • Solver Evolutionary.xlsx
    12.7 KB · Views: 2
You must log in or register to reply here.
Back
Top