• 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

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

You must log in or register to reply here.
Back
Top