I want to make a travel plan using Excel Solver to decide which city to visit in order and how long to stay. I'm going to leave from city A and come back to city A.
Below table shows the airfare for moving from one city to another, and the cost of accommodation per day for staying in each city.
Target(Blue cell): To minimize Total expense (N17) Cells to change(Green cells): Staying days in a city ($D$17:$F$19)
For example, 9 in F17 cell means moving from A to C and staying in C for 9 days. 'From count'(Column H) checks the number of departures from each city =COUNTIF(D18:G18,">0")
'To count'(Row 22) checks number of times you arrive in each city =COUNTIF(D18:D21,">0")
'necessary?'(Column K) checks if you have ever arrived in the city (because if you have arrived, you also have to leave) =HLOOKUP(C19,$D$17:$G$22,6,FALSE)
'Total Airfair' (Column M) calculates total airfair =IFERROR(SUMPRODUCT(D5:G5,D18:G18)/SUM(D18:G18), 0)
'Total Accom. Cost' (Column N) calculates total accommodation cost =SUMPRODUCT($D$13:$G$13,D18:G18)
Constraints include following
- We're not moving to the same city
D18 = 0 E19 = 0 F20 = 0 G21 = 0
- Number of staying should be an integer D18:G21 = integer
- travel starts from city A H18 = 1
- come back to city A D22 = 1
- We're not going to stop by a city more than once
H19:H21 <= 1 E22:G22 <= 1
- if you have arrived in a city, you also have to leave
H19:H21 >= K19:K21
- Total staying days (except to come back to A and stay) should be 10
D26 = sum(E18:G21) = 10
- Total visiting cities (except A) should be 2
D27 = sum(E22:G22) = 2
- Number of days to return to A and stay at A is randomly specified as 1
D19:D21 <= 1
I'd like to use this logic in five city examples, but Excel can't find a feasible solution even in this toy example.
What's the reason and how can I make it work?
*I just found that I can't attach the excel file. Is it possible to attach here? I can send you by email if you want.
I think the number shown on the captured image would be the best travel plan. If I enter this number and run solver, I find a solution, but if I clear all the numbers and run it, I don't find a solution.
*I heard that solver does not recognize countif function, so I tried to use iferror, for example, in cell H18 =IFERROR(D18/D18, 0)+IFERROR(E18/E18, 0)+IFERROR(F18/F18, 0)+IFERROR(G18/G18, 0) instead of countif, but I also couldn't find a solution.
Well, came up with this, but you will need to think about what to improve:
I did not use binary as the from count is held to 1.
Note you get 7 days in city C as it is the cheapest... So add a constraint that duration cells D22:F22 must be >=2... for a minumum 2 day stay.