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?
<tbody>
</tbody>
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?
A | B | C | D | E | |
1 | # of Assets | Mileage | |||
2 | 405 | 550 | |||
3 | |||||
4 | Drive Assets | Fly Assets | Assets Moved Total | ||
5 | 0 | 0 | =SUM(Drive_Assets,Fly_Assets) | ||
6 | |||||
7 | Drive Cost | Fly Cost | Best 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>