• 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.

Something I found on Ozgrid

chirayu

Well-Known Member
Hi Guys,

Found this thread on Ozgrid & thought it would be a good challenge. I have already tried it without success. I could only allocate within column but not across row. See if you guys can do it.

https://www.ozgrid.com/forum/forum/...ed-if-and-statements-things-are-getting-messy

Column AI has "Bins" per row, Column AK:AO are the Truck columns which the "Bins" must be distributed to

Column AJ is the "Weight" of the "Bins", column AQ to AU are Tucks columns where the "Weight" must be distributed to - Its just Weight/ Bins

Each Truck column cannot have more than a total of 45 "Bins"

Each truck must have upto 10 "Bins" per row maximum so if total bins for the row is 30 then Truck 1 to Truck 3 will each be 10 each

Each Truck must not weigh over 42000 pounds

So in essence we could get rows where less than 10 "Bins" are applied to Truck 1 for example but 10 are applied to Truck 2 etc. based on the 45 max "Bins" rule per truck

Similarly We can have Truck rows that do not have 10 "Bins" because the Total "Bins" in column AI was less than 10

So its a 2 way formula, down column & across row, to distribute the "Bins" further complicated by the fact that the Weight cannot exceed 42000 pounds for each Truck.
 

Attachments

  • Example 2.xlsm
    35.3 KB · Views: 17
Last edited:
Hi ,

Can you explain what is to be done , and what are the constraints ?

Without a more detailed description , I am at a loss.

Narayan
 
Hi ,

Thank you for the explanation.

Something seems to be wrong in the problem description.

The total weight in the 4 trucks is 1,93,552 ; this clearly exceeds the 42,000 per truck limit , since 4 x 42,000 = 1,68,000.

A fifth truck will be required.

Narayan
 
Narayan there are five truck columns in the file. You may need to ignore the values already in the column because those are from the original thread creator and are not accurate
 
Last edited:
Hi ,

I agree that there are 5 columns for 5 trucks. But the cell AH1 says there are only 4 trucks going out , which is why I made my comment.

Narayan
 
OK got it. Narayan I guess we can make the argument more difficult then. Its not part of the challenge but could technically make it more difficult. So its an optional addition to the challenge if someone wants to try it.

If we take AH1 as possible criteria then it would mean that based on the number of trucks chosen for allocation, our formula/macro should only allocate against those trucks and not for the rest i.e lets say driver is ill or truck is being repaired; under such a circumstance, this criteria would become useful for allocating Bins & Weight.

Note that not all the rows would get allocated because Bins/ Weight may exceed our total allocation for requested trucks
 
@NARAYANK991 Its doesn't seem to be correct. I've manually created attached possible solutions file for the same
 

Attachments

  • TRUCKY.xlsx
    54.8 KB · Views: 8
I had a go at using LP simplex for getting me near to a possible solution.
Since that tends to produce solutions with fractional bins, I added at row to show where extra capacity (bin count or weight) was still available. The objective function chosen was the weight carried by the first four trucks but, once the weight carried by truck 5 has reduced to zero, every feasible solution is as good as every other. The adjustment to give integer bins per truck was achieved by hand, rounding one value up and compensating by rounding another down. Not very scientific!
 

Attachments

  • TRUCKY2.xlsx
    57.7 KB · Views: 7
@NARAYANK991 its violating the max 10 Bins per row per truck rule e.g. look at the row 6 - truck 1 = 30 bins. Actual output should be truck1 = 10, truck2 = 10, truck3 = 10
 
@NARAYANK991 its violating the max 10 Bins per row per truck rule e.g. look at the row 6 - truck 1 = 30 bins. Actual output should be truck1 = 10, truck2 = 10, truck3 = 10
Hi ,

Thanks for pointing it out. I had not considered it at all.

What about this then ?

Narayan
 

Attachments

  • Test.xlsm
    42 KB · Views: 7
@NARAYANK991 fully automated, me likey

Only downside is my manual solution & your automated solution both fall short of the solution provided by @Peter Bartholomew which does the allocation of Bins & Weight really well

However Peter's solution does require manual intervention whereas your does not. So I suppose yours does have the upper hand in that sense even though Peter's is a better allocator of Bins & Weight.
 
After a bit of experimentation, I found that a possible strategy with Solver was to define the integer constraints on the number of bins but then set the option to ignore them. The LP simplex method 'roughs out' a solution very rapidly. I then reset the option to enforce the integer constraints and got a solution.
As an objective function, I maximised the load carried by the first four trucks in order to give the best chance of reducing the fleet of trucks.
 

Attachments

  • TRUCKY2.xlsx
    58.8 KB · Views: 18
Back
Top