1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Excel Challenges' started by chirayu, Mar 9, 2018.

  1. chirayu

    chirayu Well-Known Member

    Messages:
    896
    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.

    Attached Files:

    Last edited: Mar 9, 2018
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  3. chirayu

    chirayu Well-Known Member

    Messages:
    896
    Edited the main post
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  5. chirayu

    chirayu Well-Known Member

    Messages:
    896
    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: Mar 9, 2018
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  7. chirayu

    chirayu Well-Known Member

    Messages:
    896
    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
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi @chirayu

    Can you check whether this is correct ?

    Narayan

    Attached Files:

  9. chirayu

    chirayu Well-Known Member

    Messages:
    896
    @NARAYANK991 Its doesn't seem to be correct. I've manually created attached possible solutions file for the same

    Attached Files:

  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Can you indicate which rule is being violated ?

    That will help me understand.

    Narayan
  11. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    428
    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!

    Attached Files:

  12. chirayu

    chirayu Well-Known Member

    Messages:
    896
    @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
  13. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

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

    What about this then ?

    Narayan

    Attached Files:

  14. chirayu

    chirayu Well-Known Member

    Messages:
    896
    @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.
  15. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    428
    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.

    Attached Files:

Share This Page