Help with solver as solver gives wrong answer

[syjytg]

I want to minimize shipping costs (Cell B39) by filling in (Cell B21:F24) subject to certain constraints. The solver's solution are currently in Cell B21:F24 which give B39 answer of 79875.

However, by trial and error I found a test case (answer in Cell I21:M24) that gives Cell B39 answer of 70500, which is less than 79875. It is not possible to try all possible cases by hand, so I am not sure whether 70500 is the best.

How do I make solver give me the correct answer?

I don't know whether to use Simplex LP, GRG Nonlinear or Evolutionary but whatever I use, the best answer it gave was 79875 (Using Evolutionary).

The file is uploaded.

[6StringJazzer]

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.

[syjytg]

I have plugged my test case into B21:F24 to check and it confirmed that my handwritten calculations on paper were correct for this test case.

[syjytg]

Can somebody help please?

[MrShorty]

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.

[syjytg]

Yes, if I remove the ROUNDUP function then add it in later, it gives the same answer as my manually discovered solution. I am really hoping it is the absolute minimum. I don't want to do Lagrange manually by hand with so many variables and constraints unless I have no choice.

[6StringJazzer]

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.

[syjytg]

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.

So I got full marks for the question. But apparently the answer which I initially found with the solver would had been correct as well as that was the professor's answer key. But I still got full marks because using your method I had found the absolute minimum. Maybe he didn't expect us to know that solver doesnt works well with ROUND().

[MrShorty]

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.