# 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/help-forums/excel-formulas/1199818-problem-with-nested-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

• 35.3 KB Views: 15
Last edited:

#### NARAYANK991

##### Excel Ninja
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

#### chirayu

##### Well-Known Member
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
Edited the main post

#### NARAYANK991

##### Excel Ninja
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

#### chirayu

##### Well-Known Member
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:

#### NARAYANK991

##### Excel Ninja
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

#### chirayu

##### Well-Known Member
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

##### Excel Ninja
Hi @chirayu

Can you check whether this is correct ?

Narayan

#### Attachments

• 40 KB Views: 6

#### chirayu

##### Well-Known Member
@NARAYANK991 Its doesn't seem to be correct. I've manually created attached possible solutions file for the same

#### Attachments

• 54.8 KB Views: 7

#### NARAYANK991

##### Excel Ninja
Hi ,

Can you indicate which rule is being violated ?

That will help me understand.

Narayan

#### Peter Bartholomew

##### Well-Known Member
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

• 57.7 KB Views: 6

#### chirayu

##### Well-Known Member
@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

##### Excel Ninja
@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.

Narayan

#### Attachments

• 42 KB Views: 5

#### chirayu

##### Well-Known Member
@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.

#### Peter Bartholomew

##### Well-Known Member
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

• 58.8 KB Views: 16