Help with solver as solver gives wrong answer

Welcome to the Forum!

I'm not clear on where the data for your trial-and-error case would go in the worksheet to provide that answer. Where would your answer in I21:M24 go to produce the desired result in B39? Have you actually plugged those numbers in and seen what result is calculated?

Solver is not perfect, but it's pretty darn good. Before we look at what Solver might be doing wrong, we should look at your test case and validate whether it is really a legitimate solution. It's difficult to reverse engineer what you've done without a little more explanation of how this all works.

My older version does not have the evolutionary algorithm, so I can't test that.

One observation, in the "respective number of trucks sent from factories to distributors" section, you are using a ROUNDUP() function. Rounding functions tend to create stepwise objective functions, which create problems for algorithms like these that look at how incremental changes in the "by changing cells" effect the target cell. As an example, I need to change the 1000 in b24 by almost 100 either direction to get any change in the target cell. My first suggestion might be to eliminate the roundup function and optimize using "raw" numbers. You can add a roundup() function after you've found the optimum.

As many variables as you have for this problem, it is not surprising to realize that there are many possible "minima" in the target function cell. Finding the smallest minimum is dependent on what you give the problem as your "initial guesses" (the values in the by changing cells before you call Solver). A lot of people do not appreciate how important it can be to give Solver's algorithms good initial guesses in order to get the correct answer. I don't know of any "magic formulas" for picking good initial guesses. You may need to resort to some trial and error like you are already doing until you can convince yourself that you have found the absolute minimum. You might also look over the model and see if there is a better way of formulating the objective function to make it easier for Solver to find the overall minimum. (If it helps, if I put 0 in for all of the by changing cells, I can converge on your manually discovered solution consistently).

In summary, I don't see any easy answers to this.

MrShorty seems to have a better understanding of how Solver algorithms work and probably a better answer than what I can provide--I'm just posting to let you know I continued to work on your file but didn't gain any flash of insight.

Thank you for reporting back here. I find it interesting that the answer key contained the "quick and easy but not quite minimum" answer. Might I inquire: what kind of class is this (Excel, math, computer programming, etc)? As common as these kind of questions are ("how do I determine if the minimum Solver has found is one of many local minima or the minimum?"), especially in problems with this many input parameters, I think it would be educational to talk to the professor and see what explanations he/she might have about these kinds of problems. Both in terms of understanding how much the professor expects of the student in these kinds of problems and also his thoughts on the limitations and other nuances of working with Solver/numerical algorithms.