Hello,
I have an excel solver question hopefully someone can help me with. I’m using the Simplex LP solving method to solve something that is not linear which I’m trying to make linear. I’m using the countIF function in my solver which is the issue.
I have attached a pic of my problem below. Overall it’s a transportation solver where the main objective is to minimize the overall transportation cost. I included some constraints (I.E. Max/Min shipments, maximum # of shipments by carrier).
The constraint I have an issue with is the top table far right column (Min # of Transportation Carriers). For example, there are five transportation carriers available and for the lane between San Fran – LA, I would like to have a minimum of three transportation carriers to provide service. Now, I can go to the constraint table (Maximum #of shipments/carrier) and set a maximum of 33% of the total shipments for each carrier, but that’s not the ideal solution.
Is there a way this can be achieved? I prefer not to use the other solving methods (I.E. GRG Nonlinear, Evolutionary) if it’s not necessary
Any help is greatly appreciated.
<tbody>
</tbody>
I have an excel solver question hopefully someone can help me with. I’m using the Simplex LP solving method to solve something that is not linear which I’m trying to make linear. I’m using the countIF function in my solver which is the issue.
I have attached a pic of my problem below. Overall it’s a transportation solver where the main objective is to minimize the overall transportation cost. I included some constraints (I.E. Max/Min shipments, maximum # of shipments by carrier).
The constraint I have an issue with is the top table far right column (Min # of Transportation Carriers). For example, there are five transportation carriers available and for the lane between San Fran – LA, I would like to have a minimum of three transportation carriers to provide service. Now, I can go to the constraint table (Maximum #of shipments/carrier) and set a maximum of 33% of the total shipments for each carrier, but that’s not the ideal solution.
Is there a way this can be achieved? I prefer not to use the other solving methods (I.E. GRG Nonlinear, Evolutionary) if it’s not necessary
Any help is greatly appreciated.
| Transportation Solver | ||||||||||
| Origin | Destination | Transportation Carrier A | Transportation Carrier B | Transportation Carrier C | Transportation Carrier D | Transportation Carrier E | Total Shipments | Forecasted Shipments | Total # of Transportation Carriers | Min # of Transportation Carriers |
| San Fran | LA | 7,500 | 2,500 | - | - | - | 10,000 | 10,000 | 2 | 3 |
| Portland | Phoenix | - | 2,000 | - | 1,000 | 2,000 | 5,000 | 5,000 | 3 | 3 |
| Texas | Las Vegas | 1,125 | - | 1,375 | - | - | 2,500 | 2,500 | 2 | 2 |
| New York | Boston | 1,125 | - | 625 | 1,750 | - | 3,500 | 3,500 | 3 | 2 |
| Total Shipments | 9,750 | 4,500 | 2,000 | 2,750 | 2,000 | 21,000 | 21,000 | |||
| Max Shipments | 15,000 | 15,000 | 15,000 | 15,000 | 15,000 | |||||
| Min Shipments | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | |||||
| CONSTRAINT: (Maximum # of shipments/carrier) | ||||||||||
| Origin | Destination | % Max of Total | Transportation Carrier A | Transportation Carrier B | Transportation Carrier C | Transportation Carrier D | Transportation Carrier E | |||
| San Fran | LA | 75% | 7,500 | 7,500 | 7,500 | 7,500 | 7,500 | |||
| Portland | Phoenix | 40% | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | |||
| Texas | Las Vegas | 55% | 1,375 | 1,375 | 1,375 | 1,375 | 1,375 | |||
| New York | Boston | 50% | 1,750 | 1,750 | 1,750 | 1,750 | 1,750 | |||
| Total Transportation Cost (Min) | $ 24,486,875 | |||||||||
<tbody>
</tbody>
