![ms-excel solver function ms-excel solver function](https://i.ytimg.com/vi/hjKwfjgK9_M/hqdefault.jpg)
Also, a one unit increase in a storage location caused a 0.10 increase in costs, but whenĪnother location decreased by 1, the total costs remained the same (its a fixed storage cost)įor week1, we had 3 different clinics that could take 0,1,2,or3 trucks. This was going to require 5 trucks, who's cost was going to remain the same no matter which week you used. If delivery of 101 cost 250, and 201 cost 500, then what is the middle value?Īlso note for example, clinic 3. Solver did not know you used a Ceiling function. etc, the cost of shipping remained that same. One of the reasons your program stopped quickly is that, for example the first line, is that as deliveries changed from 101, 123, 167. I grouped the 3 clinics together, with clinic 3 on top, then clinic 2, then clinic 1. I "think" you should have it.Ī little background.
![ms-excel solver function ms-excel solver function](https://static1.makeuseofimages.com/wordpress/wp-content/uploads/2015/11/excel-functions.jpg)
So, from what littler I see, I think you need to change the logic of the model. The equation for arriving at a used value of 220 might also be another issue. I don't know what you have, but I would remove this constraint, and add:įor week 3, you are taking a random real number 390 (that has a fractional part)Īnd somehow rounding up the fixed cost to 300 (multiples of 150). Most likely using a Max() function on the "Left" column.Īgain, this is generally not a good idea. Once Solver sees that changing some cells without a change in a Max function, the program will usually give up quickly, which seems to be happening. In addition, most likely S18 is using a Max function, which is also a discontinuous function that does not work well in Solver. You have no constraints that limit the changing variables to integers!Īlso, you are most likely using a discontinuous function in the counting of how many trucks you are using per week. However, how your model is set up is very questionable because your solutions are all integers. Solver cannot find global optimization, and hence the solution will really depend on the starting values (using GRG)