Trouble with Solver

Shamrock4656

New Member
Joined
Sep 18, 2014
Messages
4
Hello,

I'm using Solver but have run into an issue. Scenario: I have X number of assets that need to be transported Y miles. There are two transportation modes available, drive and fly, each with their own cost metrics. Attempting to determine the most cost effective transportation method/combination.

The situation is mocked up as below, with the formula's input where apply. Best Cost (C8) is the target cell, Changing cells are A5:B5, with the X and Y variables being input in A2 and B2 as the users needs. Drive cost is depicted by a nested if calculation, and fly cost by a linear expression; both costs are based on the decision criteria of the solver for Drive/Fly assets. Constraint: 'assets moved total' must equal # of assets (X).

Problem experienced: the solver always defaults to drive method. Example: 405 assets and 550 miles: it is $6,500 cheaper to fly 5 assets and drive 400, than it is to drive all 405; however, Solver does not see this. Does anyone know where the mistake is?

ABCDE
1# of AssetsMileage
2405550
3
4Drive AssetsFly AssetsAssets Moved Total
500=SUM(Drive_Assets,Fly_Assets)
6
7Drive CostFly CostBest Cost
8=IF(Drive_Assets=0,0,(IF(Mileage<=499,3000,(IF(Mileage>=500,8000,FALSE))))*(IF(Drive_Assets<=200,1,(IF((Drive_Assets>=201)*(Drive_Assets<=400),2,(IF(Drive_Assets>=401,3,"error")))))))=Fly_Assets*300=SUM(Drive_Cost,Fly_Cost)
9
10

<tbody>
</tbody>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
If you put a workbook on box.net or dropbox and post a link, I'll take a look.
 
Upvote 0
Using an IF function for drive costs makes this a non-linear equation. Excel can not differentiate between global vs local mins and maxes on the cost curve.

Also, as a side note, in your IF function what would happen at mileage 499.5? You should really only use one = sign.
 
Upvote 0
Explain the drive cost calculation.
 
Upvote 0
djo5003 - I've read something on this before, but don't have enough experience; is there a non-linear option for Solver? On the aside, integers are an assumption, but you have a valid point - there should be only one = sign.

shg - The drive cost calculation is handling two variables in a single string, dependent on the users input for assets and mileage. 1) Number of trucks needed (200 assets/truck). 2) Cost of the truck based on mileage (>499 miles = $3000, <=500 miles = $8000).
 
Upvote 0
Do you need Solver for this?

P​
Q​
R​
S​
T​
U​
V​
W​
1​
Assets/Truck​
Mileage​
# Assets​
Cost/Asset​
2​
200​
550​
405​
$ 300​
3​
4​
Trucks​
Trucked​
Truck Cost​
Flown​
Fly Cost​
Total Cost​
Formulas​
5​
0​
0
$ -
405
$ 121,500
$ 121,500
Q5 and down: =P5*Q$2
6​
1​
200
$ 8,000
205
$ 61,500
$ 69,500
R5 and down: =P5*IF(R$2<500, 3000, 8000)
7​
2​
400
$ 16,000
5
$ 1,500
$ 17,500
S5 and down: =MAX(0, S$2 - Q5)
8​
3​
600
$ 24,000
0
$ -
$ 24,000
T5 and down: =T$2 * S5
9​
U5 and down: =R5+T5

Truck 400, fly 5.
 
Last edited:
Upvote 0
Thank you both for the feedback. shg's solution isn't what I was hoping for, but it is easier and gets the job done correctly. djo5003 - I'm using shq's solution, but wanted to point out that the solution you suggested doesn't satisfy other scenario's - unless I'm using it incorrectly. Ex: 10 assets @ 600 miles. That said, I can't follow half of what you set up in that scenario ;)

Looks like I have more learning to do. Thanks again for the time and help!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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