I am working on a load plan sheet for new associates in our business. We want to maximize the trucks that are being shipped. We have two different sets:
FCA and CIF
Each one has a min and max weight, our goal is to have as many close to the max, but also looking at the last one keeping it as high as possible. This means that the other trucks may be closer to the mins then the max's to make the last truck as heavy as possible.
I have attached a sheet with comments and examples of what i am trying to do. I am not sure if its best to do formulas, or a macro to get this done. The min and maxs can change from time to time, and the weight of the load that comes in changes sometimes 100,000lbs sometimes 500,000lbs and it has to be broken up. This sheet is going to be used to help the new associate understand the best way to break it up so we maximize our truck weights and have a higher average.
What we want to make sure of is the last truck which is always smaller, is as big as we can make it, with out taking the other trucks below the mins. This keeps the average higher over a period of time.
If anyone has suggestions please help, I know what i want it to do, just can't figure out how to do make it use the logic.