# Trouble with Solver

#### Shamrock4656

##### New Member
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?

 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>

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### shg

##### MrExcel MVP
If you put a workbook on box.net or dropbox and post a link, I'll take a look.

#### djo5003

##### New Member
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.

#### shg

##### MrExcel MVP
Explain the drive cost calculation.

#### Shamrock4656

##### New Member
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).

#### shg

##### MrExcel MVP
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:

#### djo5003

##### New Member
Yea I agree shg this problem does not necessarily need solver. For sake of demonstration though...

https://db.tt/j40zV45j

#### Shamrock4656

##### New Member
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!

You're welcome.

Replies
0
Views
474
Replies
1
Views
1K

1,191,075
Messages
5,984,490
Members
439,892
Latest member
yeevy

### 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.

### Which adblocker are you using?

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

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