Im trying to choose the highest number from a list and Solver keeps posting the same number. It might have to do with the indirect function that Im using.
Book2.xlsx
Im trying to choose the highest number from a list and Solver keeps posting the same number. It might have to do with the indirect function that Im using.
Book2.xlsx
Last edited by Domux; 03-24-2015 at 10:51 PM.
there is some work to do in g2![]()
Please Login or Register to view this content.
You see, Im going to use the price one later on to constraint the choices and add choosing not only one product, so using the maximum wont work. I have updated the OP file to reflect that.
Between the Solver and the INDIRECT() function, you are correct, this is a very poor setup for a GRG-nonlinear/Newton-Raphson (assuming you are using the default Solver algorithms) type algorithm. The chosen objective function has so many problems with it, I'm not even sure where to begin. The ROUND() function makes it "discontiinuous", and the INDIRECT() function makes it very random. The default algorithms just will not know how to handle this kind of function.
1st suggestion, you might try an Evolutionary algorithm (available in 2010 and later). An evolutionary algorithm may do better, I don't know, and I can't test it on my older version of Excel.
If that fails, I would probably suggest a complete re-think of the spreadsheet and the optimization. Perhaps you will want to completely reformulate your "objective function" (the objective function the function, such as the one in H3, that you are trying to optimize).
Or perhaps you completely take Solver out of the picture. I do not know how representative this simple spreadsheet is of your actual problem. In your sample spreadsheet, there are really only 3 possible values for G2: 0, 1, and 2. It might be easier and faster to arrange the spreadsheet so that it computes the result at all three possible inputs, then a MAX() (or equivalent) function can extract the maximum values from the three possibilities. Even if the sample spreadsheet is an oversimplification, it might still be easier to compute your desired result at 10 or 100 possible inputs and extract the maximum than it will be to re-think the objective function.
One way or another, I would suggest the the approach you are taking is not very suitable for the real problem at hand. If Solver's evolutionary algorithm cannot solve the problem as is, I would suggest that you will need to come up with a completely different approach to the problem.
Originally Posted by shg
Change solver model from "GRG NonLinear" to "Evolutionary" and run Solver.
Alf
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks