Excel Solver Problem

C

CRX2C

New Member
Joined
Nov 20, 2015
Messages
8
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.

[TABLE="width: 1013"]
<tbody>[TR]
[TD="colspan: 3"]Transportation Solver[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Origin[/TD]
[TD]Destination[/TD]
[TD] Transportation Carrier A[/TD]
[TD] Transportation Carrier B[/TD]
[TD] Transportation Carrier C[/TD]
[TD] Transportation Carrier D[/TD]
[TD] Transportation Carrier E[/TD]
[TD]Total Shipments[/TD]
[TD]Forecasted Shipments[/TD]
[TD]Total # of Transportation Carriers[/TD]
[TD]Min # of Transportation Carriers[/TD]
[/TR]
[TR]
[TD]San Fran[/TD]
[TD]LA[/TD]
[TD] 7,500[/TD]
[TD] 2,500[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 10,000[/TD]
[TD] 10,000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Portland[/TD]
[TD]Phoenix[/TD]
[TD] -[/TD]
[TD] 2,000[/TD]
[TD] -[/TD]
[TD] 1,000[/TD]
[TD] 2,000[/TD]
[TD] 5,000[/TD]
[TD] 5,000[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]Las Vegas[/TD]
[TD] 1,125[/TD]
[TD] -[/TD]
[TD] 1,375[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 2,500[/TD]
[TD] 2,500[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Boston[/TD]
[TD] 1,125[/TD]
[TD] -[/TD]
[TD] 625[/TD]
[TD] 1,750[/TD]
[TD] -[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Total Shipments[/TD]
[TD] 9,750[/TD]
[TD] 4,500[/TD]
[TD] 2,000[/TD]
[TD] 2,750[/TD]
[TD] 2,000[/TD]
[TD] 21,000[/TD]
[TD] 21,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Max Shipments[/TD]
[TD] 15,000[/TD]
[TD] 15,000[/TD]
[TD] 15,000[/TD]
[TD] 15,000[/TD]
[TD] 15,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Min Shipments[/TD]
[TD] 2,000[/TD]
[TD] 2,000[/TD]
[TD] 2,000[/TD]
[TD] 2,000[/TD]
[TD] 2,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]CONSTRAINT: (Maximum # of shipments/carrier)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Origin[/TD]
[TD]Destination[/TD]
[TD]% Max of Total[/TD]
[TD] Transportation Carrier A[/TD]
[TD] Transportation Carrier B[/TD]
[TD] Transportation Carrier C[/TD]
[TD] Transportation Carrier D[/TD]
[TD] Transportation Carrier E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Fran[/TD]
[TD]LA[/TD]
[TD]75%[/TD]
[TD] 7,500[/TD]
[TD] 7,500[/TD]
[TD] 7,500[/TD]
[TD] 7,500[/TD]
[TD] 7,500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portland[/TD]
[TD]Phoenix[/TD]
[TD]40%[/TD]
[TD] 2,000[/TD]
[TD] 2,000[/TD]
[TD] 2,000[/TD]
[TD] 2,000[/TD]
[TD] 2,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]Las Vegas[/TD]
[TD]55%[/TD]
[TD] 1,375[/TD]
[TD] 1,375[/TD]
[TD] 1,375[/TD]
[TD] 1,375[/TD]
[TD] 1,375[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Boston[/TD]
[TD]50%[/TD]
[TD] 1,750[/TD]
[TD] 1,750[/TD]
[TD] 1,750[/TD]
[TD] 1,750[/TD]
[TD] 1,750[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Total Transportation Cost (Min)[/TD]
[TD] $ 24,486,875[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
I take it there's no solution to this problem or my problem is not clear.



CRX2C said:
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.

[TABLE="width: 1013"]
<tbody>[TR]
[TD="colspan: 3"]Transportation Solver[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Origin[/TD]
[TD]Destination[/TD]
[TD]Transportation Carrier A[/TD]
[TD]Transportation Carrier B[/TD]
[TD]Transportation Carrier C[/TD]
[TD]Transportation Carrier D[/TD]
[TD]Transportation Carrier E[/TD]
[TD]Total Shipments[/TD]
[TD]Forecasted Shipments[/TD]
[TD]Total # of Transportation Carriers[/TD]
[TD]Min # of Transportation Carriers[/TD]
[/TR]
[TR]
[TD]San Fran[/TD]
[TD]LA[/TD]
[TD]7,500[/TD]
[TD]2,500[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Portland[/TD]
[TD]Phoenix[/TD]
[TD]-[/TD]
[TD]2,000[/TD]
[TD]-[/TD]
[TD]1,000[/TD]
[TD]2,000[/TD]
[TD]5,000[/TD]
[TD]5,000[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]Las Vegas[/TD]
[TD]1,125[/TD]
[TD]-[/TD]
[TD]1,375[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]2,500[/TD]
[TD]2,500[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Boston[/TD]
[TD]1,125[/TD]
[TD]-[/TD]
[TD]625[/TD]
[TD]1,750[/TD]
[TD]-[/TD]
[TD]3,500[/TD]
[TD]3,500[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Total Shipments[/TD]
[TD]9,750[/TD]
[TD]4,500[/TD]
[TD]2,000[/TD]
[TD]2,750[/TD]
[TD]2,000[/TD]
[TD]21,000[/TD]
[TD]21,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Max Shipments[/TD]
[TD]15,000[/TD]
[TD]15,000[/TD]
[TD]15,000[/TD]
[TD]15,000[/TD]
[TD]15,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Min Shipments[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]CONSTRAINT: (Maximum # of shipments/carrier)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Origin[/TD]
[TD]Destination[/TD]
[TD]% Max of Total[/TD]
[TD]Transportation Carrier A[/TD]
[TD]Transportation Carrier B[/TD]
[TD]Transportation Carrier C[/TD]
[TD]Transportation Carrier D[/TD]
[TD]Transportation Carrier E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Fran[/TD]
[TD]LA[/TD]
[TD]75%[/TD]
[TD]7,500[/TD]
[TD]7,500[/TD]
[TD]7,500[/TD]
[TD]7,500[/TD]
[TD]7,500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portland[/TD]
[TD]Phoenix[/TD]
[TD]40%[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]Las Vegas[/TD]
[TD]55%[/TD]
[TD]1,375[/TD]
[TD]1,375[/TD]
[TD]1,375[/TD]
[TD]1,375[/TD]
[TD]1,375[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Boston[/TD]
[TD]50%[/TD]
[TD]1,750[/TD]
[TD]1,750[/TD]
[TD]1,750[/TD]
[TD]1,750[/TD]
[TD]1,750[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Total Transportation Cost (Min)[/TD]
[TD]$ 24,486,875[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top