|
Solver not working for me
I'm having positing problems to boot, so I don't even know if this will make
it to the newsgroup, but I hope so. If it does, I'll see it. Though I seldom use solver, I recall it to be powerful. I wanted to show some other users how nice it is, but it is not working for me. I concocted an example with only nine integer choices for an input, values of 1 thru 9. I asked it to maximize an output cell. Using a data table, I can quickly see that the output cell's curve is concave down and definitely has a clear maximum in about the middle. Bu, for some reason, Solver will not budge from whatever starting integer value I give it. It says it has found a solution but the solution is always whatever value it had when I started the solver. I am simply saying maximize the output cell subject to constraints that the input cell (that I am changing) being an integer, being greater than or equal to 1, and being less than or equal to 9. I have checked and rechecked and there just doesn't seem to be any reason this is not working. It can find the minimum (which is at one of the two endpoints) just fine. I notice that it takes about 20 seconds (and you see some fleeting calcs at the bottom left) to find that minimum, whereas it thinks it finds the maximum in about 10 seconds, and you don't see much of the fleeting calculation. Can anyone explain how this could happen? I don't think I'm doing something stupid, I tried an example with a much simpler output cell, output cell equals input cell plus 2, and it found that constrained maximum just fine, so the solver seems to be working. I can't fathom what the problem can be. I've used it before and it solved much harder problems. Thanks! Dean |
Solver not working for me
I am not familiar with Solver, since I haven't used it, but I can guess
that maybe the problem is with your forcing the input to be (or round off to) an integer. A lot of mathematical techniques attempt to take a small step in one direction, then effectively compute deltaY/deltaX. In this case, you end up with 0/0, which is indeterminate. I am guessing that maybe you should try removing the integer requirement from the input variable, let Solver find the maximum, then round the input value off to the nearest integer, or evaluate the result at both integral values on both sides of the result, then choose the best value. Solver may not be designed to work with problems where the input variable is a quantized variable in this fashion. What does the documentation say? -- Regards, Bill Renaud |
Solver not working for me
No - one of the canned constraints is that it be an integer, so it's
definitely allowed. Nevertheless, I took it out and it didn't help. I took out all the constraints and that didn't help either. It's really bizarre to me. Solver actually says it has found a solution, but the solution is always the same as whatever value you start it off with. I was thinking that I had too high numbers for some of the options, such as convergence, etc, but I set them all really low, as EXCEL help suggested to do, and it did not help. D "Bill Renaud" wrote in message . .. I am not familiar with Solver, since I haven't used it, but I can guess that maybe the problem is with your forcing the input to be (or round off to) an integer. A lot of mathematical techniques attempt to take a small step in one direction, then effectively compute deltaY/deltaX. In this case, you end up with 0/0, which is indeterminate. I am guessing that maybe you should try removing the integer requirement from the input variable, let Solver find the maximum, then round the input value off to the nearest integer, or evaluate the result at both integral values on both sides of the result, then choose the best value. Solver may not be designed to work with problems where the input variable is a quantized variable in this fashion. What does the documentation say? -- Regards, Bill Renaud |
Solver not working for me
Does the vendor have a tech support group or peer-to-peer self-help message
board (like this one)? You might have to repost this question (with a little more detail about the function you are using) to that forum. -- Regards, Bill Renaud |
Solver not working for me
Are you using the Excel Solver add-in, or the one from Frontline Systems?
If using the Excel Solver, then post your worksheet setup (cell values and formulas) or post to the worksheet functions newsgroup. -- Regards, Bill Renaud |
Solver not working for me
I set up a sample problem similar to your description, and it seems to work
fine (I use Excel 2000). B1: value B2: =-1*((B1-5)^2)+10 This is a parabola, with the maximum at 5 and cupped downward, like you mentioned. Solver Parameters: --------------------- Set Target Cell: $B$2 By Changing Cells: $B$1 Subject to the Constraints: $B$1 <= 10 $B$1 = integer $B$1 = 0 Options: (I left everything at the defaults) -------------------------------------------- Max Time: 100 seconds Iterations: 100 Precision: 0.000001 Tolerance: 5% Convergence: 0.0001 All checkboxes OFF (Assume Linear Model, Assume Non-Negative, Use Automatic Scaling, Show Iteration Results). Estimates: Tangent Derivatives: Forward Search: Newton I could start with cell $B$1 at 0 or 10, and it would converge to 4.9999999824217 in about 1 second. -- Regards, Bill Renaud |
Solver not working for me
Could you give us the function of your Target Cell?
Feel free to send me your workbook, and I'llbe glad to take a look at it. -- Dana DeLouis "Dean" wrote in message ... No - one of the canned constraints is that it be an integer, so it's definitely allowed. Nevertheless, I took it out and it didn't help. I took out all the constraints and that didn't help either. It's really bizarre to me. Solver actually says it has found a solution, but the solution is always the same as whatever value you start it off with. I was thinking that I had too high numbers for some of the options, such as convergence, etc, but I set them all really low, as EXCEL help suggested to do, and it did not help. D "Bill Renaud" wrote in message . .. I am not familiar with Solver, since I haven't used it, but I can guess that maybe the problem is with your forcing the input to be (or round off to) an integer. A lot of mathematical techniques attempt to take a small step in one direction, then effectively compute deltaY/deltaX. In this case, you end up with 0/0, which is indeterminate. I am guessing that maybe you should try removing the integer requirement from the input variable, let Solver find the maximum, then round the input value off to the nearest integer, or evaluate the result at both integral values on both sides of the result, then choose the best value. Solver may not be designed to work with problems where the input variable is a quantized variable in this fashion. What does the documentation say? -- Regards, Bill Renaud |
Solver not working for me
It's an NPV function, which is a polynomial. I'll e-mail it to you.
Thanks much! Dean "Dana DeLouis" wrote in message ... Could you give us the function of your Target Cell? Feel free to send me your workbook, and I'llbe glad to take a look at it. -- Dana DeLouis "Dean" wrote in message ... No - one of the canned constraints is that it be an integer, so it's definitely allowed. Nevertheless, I took it out and it didn't help. I took out all the constraints and that didn't help either. It's really bizarre to me. Solver actually says it has found a solution, but the solution is always the same as whatever value you start it off with. I was thinking that I had too high numbers for some of the options, such as convergence, etc, but I set them all really low, as EXCEL help suggested to do, and it did not help. D "Bill Renaud" wrote in message . .. I am not familiar with Solver, since I haven't used it, but I can guess that maybe the problem is with your forcing the input to be (or round off to) an integer. A lot of mathematical techniques attempt to take a small step in one direction, then effectively compute deltaY/deltaX. In this case, you end up with 0/0, which is indeterminate. I am guessing that maybe you should try removing the integer requirement from the input variable, let Solver find the maximum, then round the input value off to the nearest integer, or evaluate the result at both integral values on both sides of the result, then choose the best value. Solver may not be designed to work with problems where the input variable is a quantized variable in this fashion. What does the documentation say? -- Regards, Bill Renaud |
Solver not working for me
I am using the EXCEL solver. Now that you mention Frontline, it rings a
distant bell in my memory, such as them sending me stuff about some of their more advanced stuff. I am hoping that the EXCEL version is capable of understanding its own NPV function but am, now, starting to wonder if it can't handle that one; and that frontline told me that 10 years ago! Thanks! Dana has offered to help but if he can't, I'll post where you say - I assume you mean that this is another Microsoft newsgroup. D "Bill Renaud" wrote in message . .. Are you using the Excel Solver add-in, or the one from Frontline Systems? If using the Excel Solver, then post your worksheet setup (cell values and formulas) or post to the worksheet functions newsgroup. -- Regards, Bill Renaud |
Solver not working for me
(Excel Solver add-in that won't converge.)
Dean wrote: <<It's an NPV function, which is a polynomial. Is the setup too complex to post here in the newsgroup, for the benefit of other viewers? -- Regards, Bill Renaud |
Solver not working for me
Yes, it would take too long for folks to recreate, plus I doubt it would
provide any insight to the problem. If there is somewhere I could post the spreadsheet, I'd be happy to do so. It is a small one, albeit with some hairy equations. Thanks for asking, Bill Dean "Bill Renaud" wrote in message . .. (Excel Solver add-in that won't converge.) Dean wrote: <<It's an NPV function, which is a polynomial. Is the setup too complex to post here in the newsgroup, for the benefit of other viewers? -- Regards, Bill Renaud |
Solver not working for me
Hi. Got your sheet. I can see what the problem is.
Although your Target Cell does not use any "Discontinuous functions," the Target Cell is dependent on many cells that do use these functions. The Target Cell is dependent on many uses of the IF function. This is the problem !! But, for some reason, Solver will not budge from whatever starting integer value I give it. I'll try to give a quick explanation as to the problem. In a nutshell, Solver can easily get confused, and when it does, will often give up immediately without a warning. This is a known issue. Lets look at one of your dependant cells. =IF(G12$I$26,0,IF(G12<$I$26,0,-$I$7)+$I$7*$E$27*-1) (G12 and I26 represent Integer Years) Even if G12 were "suppose" to equal I26, and tolerances in Excel might show these as unequal. Hence, a problem. Suppose we do start off with an integer guess like you mention. Only 1 of these cells would show a value and in theory would work as planned. However, on the very next guess, the value of your changing cell would most likely "NOT" be a true integer. (Even though the constraint is integer). Excel does not start out with most of the integer constraints set at true integers. Later on, it will try to get them "closer" to integer values. So, on the next guess, "ALL" of the equations like that above come into play. This is enough to confuse Solver, and will quit immediately. That is one reason the use of IF functions will not work in Solver. Your equation above might be written like this: IF G12 = "Integer Changing Cell, then ... This would almost NEVER be true. The whole model would have to be re-written. It appears to me that one solution might be the following. Select 9 Cells as your changing cells. Make the constraint "Binary" (ie 0 or 1) Also, in another cell, Sum these 9 Cells. Add a constraint that the sum of these 9 cells =1. (Actually, I might use =0.9 and <= 1.1 since we want to avoid problems of equality. Now, Solver will put a 1 in only 1 of the 9 Cells. It is up to you to use that 1 value and calculate a value. The cell that ends up with a 1 represents the number of years. Anyway, hope this helps a little. -- Dana DeLouis "Dean" wrote in message ... Yes, it would take too long for folks to recreate, plus I doubt it would provide any insight to the problem. If there is somewhere I could post the spreadsheet, I'd be happy to do so. It is a small one, albeit with some hairy equations. Thanks for asking, Bill Dean "Bill Renaud" wrote in message . .. (Excel Solver add-in that won't converge.) Dean wrote: <<It's an NPV function, which is a polynomial. Is the setup too complex to post here in the newsgroup, for the benefit of other viewers? -- Regards, Bill Renaud |
Solver not working for me
Oh, I missed this. Yes, I also tried the default settings. Believe me,
I've used solver before in the past with no such problems, for generally hairier workbooks than this. Dean "Bill Renaud" wrote in message ... I set up a sample problem similar to your description, and it seems to work fine (I use Excel 2000). B1: value B2: =-1*((B1-5)^2)+10 This is a parabola, with the maximum at 5 and cupped downward, like you mentioned. Solver Parameters: --------------------- Set Target Cell: $B$2 By Changing Cells: $B$1 Subject to the Constraints: $B$1 <= 10 $B$1 = integer $B$1 = 0 Options: (I left everything at the defaults) -------------------------------------------- Max Time: 100 seconds Iterations: 100 Precision: 0.000001 Tolerance: 5% Convergence: 0.0001 All checkboxes OFF (Assume Linear Model, Assume Non-Negative, Use Automatic Scaling, Show Iteration Results). Estimates: Tangent Derivatives: Forward Search: Newton I could start with cell $B$1 at 0 or 10, and it would converge to 4.9999999824217 in about 1 second. -- Regards, Bill Renaud |
Solver not working for me
Interesting that, despite what you refer to as giving up, Solver always says
it has found a solution. I guess that's part of the known issue. The EXCEL imprecision is something that I recall from long ago and, to this day, I will often write if statements that say, if (abs(A-B) <0.00000001, ...) which confuses readers but covers tiny roundoff. That said, slowly over the years, I do this less often since I don't notice the problem being there. Perhaps it was only there, even back then, when using things like solver. It will take me a little while to understand your workaround but, in the meantime, I first tried replacing all the integer cell references "X" with round(X,0) but that did not help. I then replaced all if statement arguments with: abs(x-y)<-0.1, abs (x-y) 0.1, or abs(x-y)< 0.0001, Since x-y will always be infinitesimally close to an integer, this should work. But it didn't. Assuming I didn't miss one (and my plot of the data, table results seems to confirm the 9 values are unchanged - also if I try 3.99999, rather than 4, manually it produces the same answer), shouldn't this have also worked as a workaround? Or, perhaps, I am missing the point. Thanks so much for your analysis. Dean "Dana DeLouis" wrote in message ... Hi. Got your sheet. I can see what the problem is. Although your Target Cell does not use any "Discontinuous functions," the Target Cell is dependent on many cells that do use these functions. The Target Cell is dependent on many uses of the IF function. This is the problem !! But, for some reason, Solver will not budge from whatever starting integer value I give it. I'll try to give a quick explanation as to the problem. In a nutshell, Solver can easily get confused, and when it does, will often give up immediately without a warning. This is a known issue. Lets look at one of your dependant cells. =IF(G12$I$26,0,IF(G12<$I$26,0,-$I$7)+$I$7*$E$27*-1) (G12 and I26 represent Integer Years) Even if G12 were "suppose" to equal I26, and tolerances in Excel might show these as unequal. Hence, a problem. Suppose we do start off with an integer guess like you mention. Only 1 of these cells would show a value and in theory would work as planned. However, on the very next guess, the value of your changing cell would most likely "NOT" be a true integer. (Even though the constraint is integer). Excel does not start out with most of the integer constraints set at true integers. Later on, it will try to get them "closer" to integer values. So, on the next guess, "ALL" of the equations like that above come into play. This is enough to confuse Solver, and will quit immediately. That is one reason the use of IF functions will not work in Solver. Your equation above might be written like this: IF G12 = "Integer Changing Cell, then ... This would almost NEVER be true. The whole model would have to be re-written. It appears to me that one solution might be the following. Select 9 Cells as your changing cells. Make the constraint "Binary" (ie 0 or 1) Also, in another cell, Sum these 9 Cells. Add a constraint that the sum of these 9 cells =1. (Actually, I might use =0.9 and <= 1.1 since we want to avoid problems of equality. Now, Solver will put a 1 in only 1 of the 9 Cells. It is up to you to use that 1 value and calculate a value. The cell that ends up with a 1 represents the number of years. Anyway, hope this helps a little. -- Dana DeLouis "Dean" wrote in message ... Yes, it would take too long for folks to recreate, plus I doubt it would provide any insight to the problem. If there is somewhere I could post the spreadsheet, I'd be happy to do so. It is a small one, albeit with some hairy equations. Thanks for asking, Bill Dean "Bill Renaud" wrote in message . .. (Excel Solver add-in that won't converge.) Dean wrote: <<It's an NPV function, which is a polynomial. Is the setup too complex to post here in the newsgroup, for the benefit of other viewers? -- Regards, Bill Renaud |
Solver not working for me
After experimenting some more with the hypothetical problem that I posted
earlier, it appears that Solver ignores any Integer constraints on a variable, then simply rounds the answer off to the nearest integer after the convergence has finished. For example, if I set my parabola to peak at 4.49, then Solver returns 4 as the answer. If I set the peak at 4.51, then Solver returns 5 as the answer. -- Regards, Bill Renaud |
Solver not working for me
If true, that seems very misleading. It would seem to me that the closest
integer could be much further off from the maximum than some other integer that may not be as close to where the best solution is, depending upon the nature of the curve. Or am I missing something? Dean "Bill Renaud" wrote in message . .. After experimenting some more with the hypothetical problem that I posted earlier, it appears that Solver ignores any Integer constraints on a variable, then simply rounds the answer off to the nearest integer after the convergence has finished. For example, if I set my parabola to peak at 4.49, then Solver returns 4 as the answer. If I set the peak at 4.51, then Solver returns 5 as the answer. -- Regards, Bill Renaud |
Solver not working for me
Hi. Just some thoughts.
if (abs(A-B) <0.00000001 In General, ABS is another function that can not be used with Solver. A variable (say B) will most likely never get that close to return a True value ( ie < 0.00000001. ) Look under Solver's options for (precision and Tolerance) Suppose Solver's variable varies between 4.9 and 5.1 =ABS(5-5.1) =ABS(5-4.9) Both of these solutions return the same answer. In addition, the slope reverses direction. This is enough to confuse Solver, and give up without warning. round(X,0) Note however, that =ROUND(3.6,0) =ROUND(4.3,0) Both of these return the same answer. This is enough to confuse Solver, and give up. Therefore, Round is another function that can not be used with Solver. It now becomes an art to rewrite your model with functions that Solver can use. Here's an small example. Suppose you need to pick 1 of 2 variables (v1 and v2). The beginners method would be to use an IF statement. However, lets use a changing cell whose constraint is "Binary" (0 or 1) The function might be =v1*B + v2*(1-B) If Solver picks B=1, then the function returns V1 (other goes to zero) If Solver picks 0, then the function returns V2 . Solver "CAN" keep track of jumps using this technique. Hope you see the pattern. Anyway, hope this helps. -- Dana DeLouis "Dean" wrote in message ... Interesting that, despite what you refer to as giving up, Solver always says it has found a solution. I guess that's part of the known issue. The EXCEL imprecision is something that I recall from long ago and, to this day, I will often write if statements that say, if (abs(A-B) <0.00000001, ...) which confuses readers but covers tiny roundoff. That said, slowly over the years, I do this less often since I don't notice the problem being there. Perhaps it was only there, even back then, when using things like solver. It will take me a little while to understand your workaround but, in the meantime, I first tried replacing all the integer cell references "X" with round(X,0) but that did not help. I then replaced all if statement arguments with: abs(x-y)<-0.1, abs (x-y) 0.1, or abs(x-y)< 0.0001, Since x-y will always be infinitesimally close to an integer, this should work. But it didn't. Assuming I didn't miss one (and my plot of the data, table results seems to confirm the 9 values are unchanged - also if I try 3.99999, rather than 4, manually it produces the same answer), shouldn't this have also worked as a workaround? Or, perhaps, I am missing the point. Thanks so much for your analysis. Dean "Dana DeLouis" wrote in message ... Hi. Got your sheet. I can see what the problem is. Although your Target Cell does not use any "Discontinuous functions," the Target Cell is dependent on many cells that do use these functions. The Target Cell is dependent on many uses of the IF function. This is the problem !! But, for some reason, Solver will not budge from whatever starting integer value I give it. I'll try to give a quick explanation as to the problem. In a nutshell, Solver can easily get confused, and when it does, will often give up immediately without a warning. This is a known issue. Lets look at one of your dependant cells. =IF(G12$I$26,0,IF(G12<$I$26,0,-$I$7)+$I$7*$E$27*-1) (G12 and I26 represent Integer Years) Even if G12 were "suppose" to equal I26, and tolerances in Excel might show these as unequal. Hence, a problem. Suppose we do start off with an integer guess like you mention. Only 1 of these cells would show a value and in theory would work as planned. However, on the very next guess, the value of your changing cell would most likely "NOT" be a true integer. (Even though the constraint is integer). Excel does not start out with most of the integer constraints set at true integers. Later on, it will try to get them "closer" to integer values. So, on the next guess, "ALL" of the equations like that above come into play. This is enough to confuse Solver, and will quit immediately. That is one reason the use of IF functions will not work in Solver. Your equation above might be written like this: IF G12 = "Integer Changing Cell, then ... This would almost NEVER be true. The whole model would have to be re-written. It appears to me that one solution might be the following. Select 9 Cells as your changing cells. Make the constraint "Binary" (ie 0 or 1) Also, in another cell, Sum these 9 Cells. Add a constraint that the sum of these 9 cells =1. (Actually, I might use =0.9 and <= 1.1 since we want to avoid problems of equality. Now, Solver will put a 1 in only 1 of the 9 Cells. It is up to you to use that 1 value and calculate a value. The cell that ends up with a 1 represents the number of years. Anyway, hope this helps a little. -- Dana DeLouis "Dean" wrote in message ... Yes, it would take too long for folks to recreate, plus I doubt it would provide any insight to the problem. If there is somewhere I could post the spreadsheet, I'd be happy to do so. It is a small one, albeit with some hairy equations. Thanks for asking, Bill Dean "Bill Renaud" wrote in message . .. (Excel Solver add-in that won't converge.) Dean wrote: <<It's an NPV function, which is a polynomial. Is the setup too complex to post here in the newsgroup, for the benefit of other viewers? -- Regards, Bill Renaud |
Solver not working for me
Dean wrote:
<<It would seem to me that the closest integer could be much further off from the maximum than some other integer that may not be as close to where the best solution is, depending upon the nature of the curve. You're point is quite valid. My "parabola" example was probably overly simplistic. Upon further thought, it might be that Solver finds the maximum, then checks each of the variables that are constrained to be an integer, then checks each combination to see which combination produces the maximum. Extending my previous "parabola" example to 2 independent variables, suppose that you have a "hill" made by 2 variables: X and Y. The result is given by Z. Then suppose Solver finds the optimum answer at X=4.5 and Y=4.5, with Z=10 in this case. The iteration table might look like the following: Iteration X Y Z --------- --- --- ---- 1-4 - - - 5 4.5 4.5 10.0 6 4.0 4.0 8.2 7 5.0 4.0 8.1 8 4.0 5.0 8.3 9 5.0 5.0 7.9 I would hope that Solver would then "walk around" the optimum point (X=4.5, Y=4.5) to get the best answer, which would be X=4.0 and Y=5.0 (Z=8.3). Hopefully, the problem remains fairly-well behaved around this area, but then the Sensitivity report should probably be checked to verify this. I haven't spent the time trying to develop some functions that would create a "known hill" like this, so that I could verify this behavior. Hopefully, somebody else will have some input on this. -- Regards, Bill Renaud |
Solver not working for me
The Sensitivity report is not generated for problems with variables
constrained to an integer. -- Regards, Bill Renaud |
Solver not working for me
See "How to Find Resources on Microsoft Excel Solver" at
http://support.microsoft.com/kb/146606 Also, see "Solver Uses Generalized Reduced Gradient Algorithm" at: http://support.microsoft.com/kb/82890/EN-US/ -- Regards, Bill Renaud |
Solver not working for me
The web page http://www.solver.com/academic.htm ("Academic Users of
Optimization Start Here") on the Frontline Systems, Inc. web site contains a link to download the following document: "Design and Use of the Microsoft Excel Solver" by Daniel Fylstra, Leon Lasdon, John Watson and Alan Waren in INFORMS INTERFACES 28: 5 September-October 1998 (pp. 29-55). http://pubsonline.informs.org/main/p...se_article.pdf See page 17 for their discussion of how integer constraints are handled at the end after the optimum point has been found. -- HTH, Bill Renaud |
Solver not working for me
Yes, it's all very interesting, but it's getting a little beyond me. Dana
seems convinced that Solver just won't work with various EXCEL functions (that you have to be really clever to reformulate things so that solver will work with it) and, if so, I am surprised that such is not really noted in the long, seemingly thorough document of your link below (which is 10 years old, so things should be better, certainly no worse, by now). There is some mention of certain functions but it is not clear what they are saying. As I mentioned, even without integer constraints, this EXCEL solver dog 'does not hunt' (would not solve). Just to review, this was not a problem that I needed to solve per se. I was giving a free conference lecture about the wonders of analysis of EXCEL to analysts (I got loud applause with goal seek, sensitivity via data tables, etc, etc) and, at the last minute before the presentation, remembered that for, constrained problems there was solver. So, I prepared a typical example they might face and was going to show them how to solve it with solver. Well, it wouldn't work, but I finessed my way thru the presentation without them knowing - I set the initial point to the value that I already knew would produce the maximum, had solver run, which told us it had found a solution, even though, in reality, I knew it didn't even look! So now, I'm just trying to decide if I should omit solver in my hard copy to be posted on a website of theirs. I think I need to. I have vague recollections of, a decade or more ago, talking to Frontline, perhaps about similar issues, and them telling me they had better versions I could buy. At this point, it is an issue of academic interest to me, but nothing urgent. I would appreciate any research you guys want to do. My conclusion right now is that Solver is pretty useless for anything reasonably complicated, enough such that Dana's reformulation is not worth all the effort. Sad to say. D "Bill Renaud" wrote in message . .. The web page http://www.solver.com/academic.htm ("Academic Users of Optimization Start Here") on the Frontline Systems, Inc. web site contains a link to download the following document: "Design and Use of the Microsoft Excel Solver" by Daniel Fylstra, Leon Lasdon, John Watson and Alan Waren in INFORMS INTERFACES 28: 5 September-October 1998 (pp. 29-55). http://pubsonline.informs.org/main/p...se_article.pdf See page 17 for their discussion of how integer constraints are handled at the end after the optimum point has been found. -- HTH, Bill Renaud |
Solver not working for me
I remember studying mathematical optimization techniques for computers way
back in engineering school, so I remember some of the concepts. It is clear to me that you cannot have step functions or other non-continuous functions in the model, because that causes the search to get lost. Basically, at each point, the technique is to take a small step in the direction of each independent variable, then calculate the "slope" (partial derivative of F with respect to X) of the function. Then the next point is calculated by moving all independent variables in the appropriate direction. If you have any functions in your cells that return the same result (say 0) when this small step is taken, then the slope appears to be 0, so the technique concludes (wrongly) that it is at the "top of the hill" (maximization problem) and then stops. You simply have to take all of the non-continuous functions out of your model (i.e. ROUND, ROUNDDOWN, ROUNDUP, RAND, IF(var1<0,SomeValue,OtherValue), etc.). I believe that Solver really is a powerful tool and can be used in a lot of situations, as long as you understand the setup and usage requirements first. I guess the other lesson (or rule) is: Always double-check your presentation demos before class! (You always get questions that can test your credibility anyway!) -- Regards, Bill Renaud |
Solver not working for me
As Dana mentioned, you had one cell with the following formula:
=IF(G12$I$26,0,IF(G12<$I$26,0,-$I$7)+$I$7*$E$27*-1) Essentially, this is like a large, flat field with a "pin" pointing upward somewhere out in the middle of the field. When Solver plugs values in to this formula, it always gets 0, unless it just happens to land right on top of the "pin", the probability of which will be essentially "never". Therefore, Solver stops iterating, because it thinks that it has reached the top of the "hill". Simply change that particular cell to: -$I$7+$I$7*$E$27*-1 -- Regards, Bill Renaud |
Solver not working for me
I understand what you are saying. I'm just perturbed that EXCEL doesn't
seem to do a better job of telling you what functions you can't use, or must work around. "Bill Renaud" wrote in message . .. I remember studying mathematical optimization techniques for computers way back in engineering school, so I remember some of the concepts. It is clear to me that you cannot have step functions or other non-continuous functions in the model, because that causes the search to get lost. Basically, at each point, the technique is to take a small step in the direction of each independent variable, then calculate the "slope" (partial derivative of F with respect to X) of the function. Then the next point is calculated by moving all independent variables in the appropriate direction. If you have any functions in your cells that return the same result (say 0) when this small step is taken, then the slope appears to be 0, so the technique concludes (wrongly) that it is at the "top of the hill" (maximization problem) and then stops. You simply have to take all of the non-continuous functions out of your model (i.e. ROUND, ROUNDDOWN, ROUNDUP, RAND, IF(var1<0,SomeValue,OtherValue), etc.). I believe that Solver really is a powerful tool and can be used in a lot of situations, as long as you understand the setup and usage requirements first. I guess the other lesson (or rule) is: Always double-check your presentation demos before class! (You always get questions that can test your credibility anyway!) -- Regards, Bill Renaud |
Solver not working for me
Actually, if you look at the 2nd IF carefully, there are two possible
non-zero results. Regardless, I don't understand your recommended change. Even if I assumed that g12<i$26 produces zero also, that result you recommend is only valid for $i$26 = the integer in g26, not just any integer. In fact, in the next cell down, where this formula is copied, that result would be valid only when $i$26 = the integer in g27. Ultimately, the result happens in only one of those 9 cases (where the g column contains the integers between 1 and 9). I don't see how it is this easy. If you're curious yellow and actually want to, I can send you my spreadsheet class example to rework. I don't really need the problem solved, just want to know if it could be reworked easily enough that I could suggest to students that solver is worth using for common EXCEL problems (which, in my mind, have lots of such discontinuities about which one has to work around). I wonder if any of Frontline's more advanced products somehow do a conversion for you? Thanks much, Bill. Dean "Bill Renaud" wrote in message . .. As Dana mentioned, you had one cell with the following formula: =IF(G12$I$26,0,IF(G12<$I$26,0,-$I$7)+$I$7*$E$27*-1) Essentially, this is like a large, flat field with a "pin" pointing upward somewhere out in the middle of the field. When Solver plugs values in to this formula, it always gets 0, unless it just happens to land right on top of the "pin", the probability of which will be essentially "never". Therefore, Solver stops iterating, because it thinks that it has reached the top of the "hill". Simply change that particular cell to: -$I$7+$I$7*$E$27*-1 -- Regards, Bill Renaud |
Solver not working for me
Dean wrote:
<<Actually, if you look at the 2nd IF carefully, there are two possible non-zero results. You are correct. I didn't look at the formula carefully enough! This still causes a discontinuity, though, since the value of the cell will always be zero anytime G12 is greater than $I$26. Apparently, Solver isn't sophisticated enough to work around discontinuities (or work when the formula changes slope near a limit). I guess a user would have to do a grid search first, to get an idea of where the maximum or minimum would be, then narrow down the limits to a smaller area around this. Yes, I would like to have your workbook, if you can also include a brief explanation of what the practical application is. I think your point is quite valid about Solver not being very useful, if a user has to make a large number of changes to a model to remove all of the discontinuities first. -- Regards, Bill Renaud (Remove the ".No.Spam" part of my e-mail address; leave one period in between first and last name.) |
Solver not working for me
...Target cell.. integers between 1 and 9.
Hi. Your Adjustable cell is "Years of Refinance" and is an integer between 1-9. Based on a simple look at the model, I think it is best to re-write your model. This is a fairly common technique in other Solvers as well. This is not complete, but have your years listed 1-9. Then have adjacent "Binary" adjustable cells next to the years. Add the constraint that the sum of the Binary cells =1. NOW, your "Years of Refinance" becomes the Formula =Sumproduct(Year,Bin) (Only 1 number, 1-9 will show up here) Do you see how this works now? You have a "Backstop Payback" column that uses an IF function. As we know, "IF" don't work with Solver. However, if is now easy to fix. The formula now becomes =Bin*CumBackstop. It is this technique that we are able to eliminate IF formulas. Solver won't get confused now because it is keeping track on Integer & Binary changing cells. You are also using a few MAX Formulas in other columns, so they are major problems as well. It really becomes an Art at this point to adjust your model. When dealing with complex financial models, and especially Least-square-fitting, one must be aware that Solver locks on "Local" mim/max, and will not find Global optimization. Let's look at a simple example. ie: Maximize Sin(x)/x. If we start start with x=12, and run Solver, we will get 1 solution. However, it is the "local" solution, and not the best solution. If we start with 5 or 6, and run Solver, we will get another solution. However, if we start with 0, then the function has an Error (Div/0!), and Solver will not even start. However, 0 is the correct solution!! Both Excel, and Solver are not aware of this. Not that there is anything wrong with Solver, you just have to know some of its limitations. In fact, if Solver is searching around 0, and selects 0, Solver will abort with an Error, despite that fact that 0 is the correct solution. We would want to go to Solver's options and make Solver work a little harder. Again, more of an art than a science at this point. -- Dana DeLouis "Dean" wrote in message ... Actually, if you look at the 2nd IF carefully, there are two possible non-zero results. Regardless, I don't understand your recommended change. Even if I assumed that g12<i$26 produces zero also, that result you recommend is only valid for $i$26 = the integer in g26, not just any integer. In fact, in the next cell down, where this formula is copied, that result would be valid only when $i$26 = the integer in g27. Ultimately, the result happens in only one of those 9 cases (where the g column contains the integers between 1 and 9). I don't see how it is this easy. If you're curious yellow and actually want to, I can send you my spreadsheet class example to rework. I don't really need the problem solved, just want to know if it could be reworked easily enough that I could suggest to students that solver is worth using for common EXCEL problems (which, in my mind, have lots of such discontinuities about which one has to work around). I wonder if any of Frontline's more advanced products somehow do a conversion for you? Thanks much, Bill. Dean "Bill Renaud" wrote in message . .. As Dana mentioned, you had one cell with the following formula: =IF(G12$I$26,0,IF(G12<$I$26,0,-$I$7)+$I$7*$E$27*-1) Essentially, this is like a large, flat field with a "pin" pointing upward somewhere out in the middle of the field. When Solver plugs values in to this formula, it always gets 0, unless it just happens to land right on top of the "pin", the probability of which will be essentially "never". Therefore, Solver stops iterating, because it thinks that it has reached the top of the "hill". Simply change that particular cell to: -$I$7+$I$7*$E$27*-1 -- Regards, Bill Renaud |
Solver not working for me
Bill,
I would be happy to send the file and an explanation. However, I want to be clear that it was a concocted example so that students could see how solver might be used. It is not a problem I need the solution to and, now that I realize how hard I would have to work to recast the equations, I really don't have any interest in pursuing it any further. It sounds like Dana has suggested a not too hard way to go, so there is no need to put yourself out over this. Bottom line, I will send it to you if you want it for your own intellectual curiosity, but the practical application is totally concocted and I don't need/want the answer. Actually, with only nine possible solutions, I can use data table to test all nine! Let me know what YOU want. Thanks much! Dean "Bill Renaud" wrote in message . .. Dean wrote: <<Actually, if you look at the 2nd IF carefully, there are two possible non-zero results. You are correct. I didn't look at the formula carefully enough! This still causes a discontinuity, though, since the value of the cell will always be zero anytime G12 is greater than $I$26. Apparently, Solver isn't sophisticated enough to work around discontinuities (or work when the formula changes slope near a limit). I guess a user would have to do a grid search first, to get an idea of where the maximum or minimum would be, then narrow down the limits to a smaller area around this. Yes, I would like to have your workbook, if you can also include a brief explanation of what the practical application is. I think your point is quite valid about Solver not being very useful, if a user has to make a large number of changes to a model to remove all of the discontinuities first. -- Regards, Bill Renaud (Remove the ".No.Spam" part of my e-mail address; leave one period in between first and last name.) |
Solver not working for me
Very informative, Dana. Thanks much!
Dean "Dana DeLouis" wrote in message ... ...Target cell.. integers between 1 and 9. Hi. Your Adjustable cell is "Years of Refinance" and is an integer between 1-9. Based on a simple look at the model, I think it is best to re-write your model. This is a fairly common technique in other Solvers as well. This is not complete, but have your years listed 1-9. Then have adjacent "Binary" adjustable cells next to the years. Add the constraint that the sum of the Binary cells =1. NOW, your "Years of Refinance" becomes the Formula =Sumproduct(Year,Bin) (Only 1 number, 1-9 will show up here) Do you see how this works now? You have a "Backstop Payback" column that uses an IF function. As we know, "IF" don't work with Solver. However, if is now easy to fix. The formula now becomes =Bin*CumBackstop. It is this technique that we are able to eliminate IF formulas. Solver won't get confused now because it is keeping track on Integer & Binary changing cells. You are also using a few MAX Formulas in other columns, so they are major problems as well. It really becomes an Art at this point to adjust your model. When dealing with complex financial models, and especially Least-square-fitting, one must be aware that Solver locks on "Local" mim/max, and will not find Global optimization. Let's look at a simple example. ie: Maximize Sin(x)/x. If we start start with x=12, and run Solver, we will get 1 solution. However, it is the "local" solution, and not the best solution. If we start with 5 or 6, and run Solver, we will get another solution. However, if we start with 0, then the function has an Error (Div/0!), and Solver will not even start. However, 0 is the correct solution!! Both Excel, and Solver are not aware of this. Not that there is anything wrong with Solver, you just have to know some of its limitations. In fact, if Solver is searching around 0, and selects 0, Solver will abort with an Error, despite that fact that 0 is the correct solution. We would want to go to Solver's options and make Solver work a little harder. Again, more of an art than a science at this point. -- Dana DeLouis "Dean" wrote in message ... Actually, if you look at the 2nd IF carefully, there are two possible non-zero results. Regardless, I don't understand your recommended change. Even if I assumed that g12<i$26 produces zero also, that result you recommend is only valid for $i$26 = the integer in g26, not just any integer. In fact, in the next cell down, where this formula is copied, that result would be valid only when $i$26 = the integer in g27. Ultimately, the result happens in only one of those 9 cases (where the g column contains the integers between 1 and 9). I don't see how it is this easy. If you're curious yellow and actually want to, I can send you my spreadsheet class example to rework. I don't really need the problem solved, just want to know if it could be reworked easily enough that I could suggest to students that solver is worth using for common EXCEL problems (which, in my mind, have lots of such discontinuities about which one has to work around). I wonder if any of Frontline's more advanced products somehow do a conversion for you? Thanks much, Bill. Dean "Bill Renaud" wrote in message . .. As Dana mentioned, you had one cell with the following formula: =IF(G12$I$26,0,IF(G12<$I$26,0,-$I$7)+$I$7*$E$27*-1) Essentially, this is like a large, flat field with a "pin" pointing upward somewhere out in the middle of the field. When Solver plugs values in to this formula, it always gets 0, unless it just happens to land right on top of the "pin", the probability of which will be essentially "never". Therefore, Solver stops iterating, because it thinks that it has reached the top of the "hill". Simply change that particular cell to: -$I$7+$I$7*$E$27*-1 -- Regards, Bill Renaud |
Solver not working for me
I would be curious to see the model and try Dana's changes. Can you save it
in Excel 2000 format before sending? -- Regards, Bill Renaud (Remove the ".No.Spam" part of my e-mail address; leave one period in between first and last name.) |
Solver not working for me
Ok, I have sent it to you. Feel free to share it with anyone you wish.
"Bill Renaud" wrote in message . .. I would be curious to see the model and try Dana's changes. Can you save it in Excel 2000 format before sending? -- Regards, Bill Renaud (Remove the ".No.Spam" part of my e-mail address; leave one period in between first and last name.) |
| All times are GMT +1. The time now is 10:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com