chirayu
WellKnown 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/helpforums/excelformulas/1199818problemwithnestedifandstatementsthingsaregettingmessy
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.
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/helpforums/excelformulas/1199818problemwithnestedifandstatementsthingsaregettingmessy
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: