|
Using Solver to Minimise the Result of Another Iterative Routine
I've written an iterative Trapezoidal integrator in VBA for integrating an
analytic function f(x), where f(x) is defined in a cell, for example, B1: =3*A1^3 + 2*A1^2 + A2^3 - A3, where the integrating variable x is in cell A1, and cells A2 and A3 contain parameters. In this example, the cells B1, A1, the cells with the lower and upper limits, and the cell to receive the result, are passed as arguments. All this works fine. Now, this is the part I 'm wanting to do, but cannot achieve. After the integrator produces its result, I'm wanting to call the Solver tool to minimise the integral result (target cell), adjust the parameters in A2 and A3 (changing cells), and then for the integrator to be run again, producing its next result. This process is to be repeated, until such time as the parameters have been so adjusted by the Solver, and the integral result has reached its minimum value. ie, 1. Set up initial values. 2. The integrator determines its result. 3. Solver reads the result, then adjusts the two parameters. 4. Repeat steps 2 and 3, until the Solver has minimised the integral via the two changed parameters. I am unable to determine how to combine the iterative integrator with the Solver in the above manner, so they alternately operate until such time that the integral has been minimised. Any guidance greatly appreciated. |
Using Solver to Minimise the Result of Another Iterative Routine
hi, Graeme !
it's not clear (or viewable) "where" (in the code) could it be a integration with solver tool however (and AFAIK) once solver "solves" an equation... there is no way that solver seek for another (possible) solution unless you reset the solver arguments and you will get (probably) the same solution as before (perhaps) if you post the code (and a sample data) -?- hth, hector. __ OP __ I've written an iterative Trapezoidal integrator in VBA for integrating an analytic function f(x) where f(x) is defined in a cell, for example, B1: =3*A1^3 + 2*A1^2 + A2^3 - A3 where the integrating variable x is in cell A1, and cells A2 and A3 contain parameters. In this example, the cells B1, A1, the cells with the lower and upper limits and the cell to receive the result, are passed as arguments. All this works fine. Now, this is the part I 'm wanting to do, but cannot achieve. After the integrator produces its result, I'm wanting to call the Solver tool to minimise the integral result (target cell) adjust the parameters in 2 and A3 (changing cells), and then for the integrator to be run again, producing its next result. This process is to be repeated, until such time as the parameters have been so adjusted by the Solver and the integral result has reached its minimum value. ie, 1. Set up initial values. 2. The integrator determines its result. 3. Solver reads the result, then adjusts the two parameters. 4. Repeat steps 2 and 3, until the Solver has minimised the integral via the two changed parameters. I am unable to determine how to combine the iterative integrator with the Solver in the above manner so they alternately operate until such time that the integral has been minimised. Any guidance greatly appreciated. |
Using Solver to Minimise the Result of Another Iterative Routin
Hi Hector, On thinking this through further: What I'm needing in principle is to have the call to the integrator and the call to the Solver in a loop, to allow them to be repeated in sequence as necessary. The loop exit criteria will take place after the Solver call. This would be a brief outline of the process, and I seek your advice on this: After the integrator call is made, and the integral result provided, Solver is called. It looks at the integral result (target cell), does some thinking, then makes changes to the changing cells. Then at THAT very moment, I need Solver to quit, ie release itself back to the code loop. (I don't want Solver to attempt to run its own iteration process to completion, or at least attempt to do so, because it won't succeed, as it cannot invoke the integration routine after making changes to the change cells). At the time Solver releases itself back to the code loop, Solver would also need to provide a status value, indicating whether the last presented target cell value is the lowest it can find, or not, allowing the code to either reloop or terminate. This (in theory) should do the job, I think. Q. Can the Solver be used in this manner, ie, allowing its internal processes to be interacted with in this way for program control purposes? Thanks for your help. Graeme PS Standing right back, it would seem that my requirement would not be very different in principle to any other situation where an iterative routine in VBA code is needed to calculate some mathematical function for whatever purpose, and then to try to use the Solver in conjunction with it for finding some minimum or maximum value of it. Still, I'm not feeling very confident Hector. Perhaps Solver's specifications don't allow for such interaction. Hoping you can identify a path through this. -------------------------- "Héctor Miguel" wrote: hi, Graeme ! it's not clear (or viewable) "where" (in the code) could it be a integration with solver tool however (and AFAIK) once solver "solves" an equation... there is no way that solver seek for another (possible) solution unless you reset the solver arguments and you will get (probably) the same solution as before (perhaps) if you post the code (and a sample data) -?- hth, hector. __ OP __ I've written an iterative Trapezoidal integrator in VBA for integrating an analytic function f(x) where f(x) is defined in a cell, for example, B1: =3*A1^3 + 2*A1^2 + A2^3 - A3 where the integrating variable x is in cell A1, and cells A2 and A3 contain parameters. In this example, the cells B1, A1, the cells with the lower and upper limits and the cell to receive the result, are passed as arguments. All this works fine. Now, this is the part I 'm wanting to do, but cannot achieve. After the integrator produces its result, I'm wanting to call the Solver tool to minimise the integral result (target cell) adjust the parameters in 2 and A3 (changing cells), and then for the integrator to be run again, producing its next result. This process is to be repeated, until such time as the parameters have been so adjusted by the Solver and the integral result has reached its minimum value. ie, 1. Set up initial values. 2. The integrator determines its result. 3. Solver reads the result, then adjusts the two parameters. 4. Repeat steps 2 and 3, until the Solver has minimised the integral via the two changed parameters. I am unable to determine how to combine the iterative integrator with the Solver in the above manner so they alternately operate until such time that the integral has been minimised. Any guidance greatly appreciated. |
Using Solver to Minimise the Result of Another Iterative Routin
hi, Graeme !
i'm not so sure the solver tool is what you look for to interact with your routines (but perhaps)... you might need some (named ?) TempCells for solver to put its results (i.e. SolvMin & SolvMax) and do some check if solver finds something different versus your integrator procedure (and if so)... for doing a (re)process changing your variables (target/changind cells) to the mim/max solver could found previous error free/checking routines (just in case ?) hth, hector. __ OP __ On thinking this through further: What I'm needing in principle is to have the call to the integrator and the call to the Solver in a loop to allow them to be repeated in sequence as necessary. The loop exit criteria will take place after the Solver call. This would be a brief outline of the process, and I seek your advice on this: After the integrator call is made and the integral result provided, Solver is called. It looks at the integral result (target cell), does some thinking then makes changes to the changing cells. Then at THAT very moment, I need Solver to quit ie release itself back to the code loop. (I don't want Solver to attempt to run its own iteration process to completion or at least attempt to do so, because it won't succeed, as it cannot invoke the integration routine after making changes to the change cells). At the time Solver releases itself back to the code loop, Solver would also need to provide a status value indicating whether the last presented target cell value is the lowest it can find, or not, allowing the code to either reloop or terminate. This (in theory) should do the job, I think. Q. Can the Solver be used in this manner, ie, allowing its internal processes to be interacted with in this way for program control purposes? Thanks for your help. Graeme PS Standing right back, it would seem that my requirement would not be very different in principle to any other situation where an iterative routine in VBA code is needed to calculate some mathematical function for whatever purpose and then to try to use the Solver in conjunction with it for finding some minimum or maximum value of it. Still, I'm not feeling very confident... Perhaps Solver's specifications don't allow for such interaction. Hoping you can identify a path through this. __ previous posts __ it's not clear (or viewable) "where" (in the code) could it be a integration with solver tool however (and AFAIK) once solver "solves" an equation... there is no way that solver seek for another (possible) solution unless you reset the solver arguments and you will get (probably) the same solution as before (perhaps) if you post the code (and a sample data) -?- __ OP __ I've written an iterative Trapezoidal integrator in VBA for integrating an analytic function f(x) where f(x) is defined in a cell, for example, B1: =3*A1^3 + 2*A1^2 + A2^3 - A3 where the integrating variable x is in cell A1, and cells A2 and A3 contain parameters. In this example, the cells B1, A1, the cells with the lower and upper limits and the cell to receive the result, are passed as arguments. All this works fine. Now, this is the part I 'm wanting to do, but cannot achieve. After the integrator produces its result, I'm wanting to call the Solver tool to minimise the integral result (target cell) adjust the parameters in 2 and A3 (changing cells), and then for the integrator to be run again, producing its next result. This process is to be repeated, until such time as the parameters have been so adjusted by the Solver and the integral result has reached its minimum value. ie, 1. Set up initial values. 2. The integrator determines its result. 3. Solver reads the result, then adjusts the two parameters. 4. Repeat steps 2 and 3, until the Solver has minimised the integral via the two changed parameters. I am unable to determine how to combine the iterative integrator with the Solver in the above manner so they alternately operate until such time that the integral has been minimised. Any guidance greatly appreciated. |
Using Solver to Minimise the Result of Another Iterative Routi
Hi Hector, It appears that Solver may not be the tool of choice. (If only it had the option to allow me to intercede in its process, as I described. Oh well...). I note that the publication "Numerical Recipes" 3rd edn ( www.nr.com ) describes several minimisation (and maximisation) algorithms for one or more independent variables in Chapter 10. One of these could act as the starting point, making the necessary changes needed, being that it is not the analytic function I need minimised, but the integral of the function. This way, I am creating my own "Solver" for this task. I'm sure it will be an interesting exercise. Thank you for your good advice. Best regards. ---------------------------- "Héctor Miguel" wrote: hi, Graeme ! i'm not so sure the solver tool is what you look for to interact with your routines (but perhaps)... you might need some (named ?) TempCells for solver to put its results (i.e. SolvMin & SolvMax) and do some check if solver finds something different versus your integrator procedure (and if so)... for doing a (re)process changing your variables (target/changind cells) to the mim/max solver could found previous error free/checking routines (just in case ?) hth, hector. __ OP __ On thinking this through further: What I'm needing in principle is to have the call to the integrator and the call to the Solver in a loop to allow them to be repeated in sequence as necessary. The loop exit criteria will take place after the Solver call. This would be a brief outline of the process, and I seek your advice on this: After the integrator call is made and the integral result provided, Solver is called. It looks at the integral result (target cell), does some thinking then makes changes to the changing cells. Then at THAT very moment, I need Solver to quit ie release itself back to the code loop. (I don't want Solver to attempt to run its own iteration process to completion or at least attempt to do so, because it won't succeed, as it cannot invoke the integration routine after making changes to the change cells). At the time Solver releases itself back to the code loop, Solver would also need to provide a status value indicating whether the last presented target cell value is the lowest it can find, or not, allowing the code to either reloop or terminate. This (in theory) should do the job, I think. Q. Can the Solver be used in this manner, ie, allowing its internal processes to be interacted with in this way for program control purposes? Thanks for your help. Graeme PS Standing right back, it would seem that my requirement would not be very different in principle to any other situation where an iterative routine in VBA code is needed to calculate some mathematical function for whatever purpose and then to try to use the Solver in conjunction with it for finding some minimum or maximum value of it. Still, I'm not feeling very confident... Perhaps Solver's specifications don't allow for such interaction. Hoping you can identify a path through this. __ previous posts __ it's not clear (or viewable) "where" (in the code) could it be a integration with solver tool however (and AFAIK) once solver "solves" an equation... there is no way that solver seek for another (possible) solution unless you reset the solver arguments and you will get (probably) the same solution as before (perhaps) if you post the code (and a sample data) -?- __ OP __ I've written an iterative Trapezoidal integrator in VBA for integrating an analytic function f(x) where f(x) is defined in a cell, for example, B1: =3*A1^3 + 2*A1^2 + A2^3 - A3 where the integrating variable x is in cell A1, and cells A2 and A3 contain parameters. In this example, the cells B1, A1, the cells with the lower and upper limits and the cell to receive the result, are passed as arguments. All this works fine. Now, this is the part I 'm wanting to do, but cannot achieve. After the integrator produces its result, I'm wanting to call the Solver tool to minimise the integral result (target cell) adjust the parameters in 2 and A3 (changing cells), and then for the integrator to be run again, producing its next result. This process is to be repeated, until such time as the parameters have been so adjusted by the Solver and the integral result has reached its minimum value. ie, 1. Set up initial values. 2. The integrator determines its result. 3. Solver reads the result, then adjusts the two parameters. 4. Repeat steps 2 and 3, until the Solver has minimised the integral via the two changed parameters. I am unable to determine how to combine the iterative integrator with the Solver in the above manner so they alternately operate until such time that the integral has been minimised. Any guidance greatly appreciated. |
Using Solver to Minimise the Result of Another Iterative Routi
hi, Graeme !
... I am creating my own "Solver" for this task. I'm sure it will be an interesting exercise. you can count on it (for sure) :)) regards, hector. |
Using Solver to Minimise the Result of Another Iterative Routi
Apologies to all for this long post, especially to Dana.
Hi Dana. I'm going to need to spend some time going over your response, so I fully understand your suggestions. Thank you for your advice. I have a background in programming and mathematics, but little experience with Excel application development. Re posting a simple equation. In my opening message, I basically describe the process I'm trying to achieve. My goal is to minimise the integral of a given analytic function which has (say) two independent variables (parameters). I gave an example in the post. The idea is that a numerical integrator sub is called to calculate the integral. Following that, I then wish to call Solver, so it does some thinking, and then makes changes to the changing cells (parameters). Solver is then to exit, returning control back to the loop. An exit test then occurs. This way, the call to the integrator and Solver are to be repeated in a code loop, until the integral is minimised. However, to achieve this, I need Solver to do one iteration only, ie, after making changes to the changing cells, I want Solver to exit, and the exit test done, after which the integrator is called again, with its (new) result dependent on the changed parameter values. Then Solver does one iteration again and exits, etc, etc. When Solver runs, if it thinks the integral has been minimised, it exits with a particular status value, while if it thinks the integral hasn't (yet) been minimised, it exits with a different status value. Then the exit test (placed after the Solver call) reads the Solver status value, and either loops back to the integrator/Solver pair again, or else exits the loop, and the code terminates with the minimised integral value and the final values of the two parameters in specified cells. Overall, my problem here is to get the Solver to do one iteration only, then exit. ie, look at the target cell (integral result), think, then change the changing cells (independent variables), and then exit - just one single pass only. So, the sequence is: integrate, adjust parameters, integrate, adjust parameters, etc, etc, until the Solver indicates the integral has reached its minimum value. It is the final values of the two parameters which I seek. Another issue which has now come to mind is that for the Solver (or any similar purpose routine) to "know" when a minimum has been reached, it will (?) need to have in its "hands" the integral results and parameter values associated with the last several passes, so it can do its own algorithmic things and tests to enable it to decide if the minimum has been found. In the manner I am proposing above, no such previous results are stored, nor is Solver aware of them, as each time the call to Solver is made in the loop, it is starting afresh.... It holds no prior history, as I'm giving it a single pass only before it exits. Perhaps the last para sums up the greatest problem I face - I want the Solver to tell me when the minimum has been found, but I'm expecting it to do so on a single pass.... Still, I could store the last several integral results and parameter values, but then I need to tell Solver of their existence, as well as what I want Solver to do with them. Whew!!! The only (?) other alternative is that, ok, let Solver iterate in its usual manner, but that demands that Solver has the ability to repeatedly call the integrator sub after it makes changes to the changing cells. I am not aware that Solver has this ability. I understand that it can only read the target cell and change the changing cells - it cannot call a sub and process the result, all internally within itself... My initial thinking is that I cannot do this as I have described it. Therefore, I need to now consider your previois post in much detail. Perhaps the answer is in there. Wouldn't it be nice if the Solver tool functionality was to be revised to address the core issues discussed here? The Subject of this thread could indeed be achieved, making the Solver tool applicable to a brave new range of mathematical problems. Dana, if you have the patience to read the above, I'd be most grateful for your thoughts. Best regards. Graeme ----------------------------------- "Dana DeLouis" wrote: (If only it (Solver) had the option to allow me to intercede in its process. Oh well...) If you post a simple equation that you are trying to solve, perhaps someone could suggest an alternative solution. Solver doesn't have a method to do 1 or 2 loops, and then exit with a status message. However, there are other methods that can come close. The Option for "Max Iterations" is rather unclear because if you set it to 1, it's not really 1 iteration, but something a little more. Here's one idea based on time... SolverOptions MaxTime:=1 'Second Results = SolverSolve(True) If Results = 10 Then 'Stop chosen when the maximum time limit was reached. 'Code Here 'Remember Target Value 'Let's start over SolverFinish 2 'Don't keep results 'Increase time to 2 Seconds SolverOptions MaxTime:=2 'Seconds Results = SolverSolve(True) 'Do something based on the trend of the two solutions so far. The other option is more in line with your question, but it won't abort as quickly as you would like.. When you run a long Solver problem, we can intercept the converging solution with the following option. I've often used it to log the best solution over time Results = SolverSolve(UserFinish, ShowRef) Even when we set the smallest time/iteration interval, what this uses is something I've never been able to figure out. Basically, the smallest interval is something I'm not sure of despite numerous tests. It has to do with Solver having to do a few calculations to calculate a derivative / slope. The "ShowRef" is the name of a macro you can run to track the results of Solver's convergence. The "problem" though is that this is not documented to the best of my knowledge. Your macro has to set up some return codes itself, so it's a little tricky. However, a macro must also test other items in the Workbook, Worksheet, etc for this to work because there are unducumented things that will make Solver Abort / Skip this method. However, it does work. But note that for simple Solver problems, one will often get a solution "before" your macro is called for the first time. This is best used for "Long" solver processes. I've been wanting to do a web site and include this technique. Good luck. -- HTH :) Dana DeLouis "Graeme Dennes" wrote in message ... Hi Hector, It appears that Solver may not be the tool of choice. (If only it had the option to allow me to intercede in its process, as I described. Oh well...). I note that the publication "Numerical Recipes" 3rd edn ( www.nr.com ) describes several minimisation (and maximisation) algorithms for one or more independent variables in Chapter 10. One of these could act as the starting point, making the necessary changes needed, being that it is not the analytic function I need minimised, but the integral of the function. This way, I am creating my own "Solver" for this task. I'm sure it will be an interesting exercise. Thank you for your good advice. Best regards. ---------------------------- "Héctor Miguel" wrote: hi, Graeme ! i'm not so sure the solver tool is what you look for to interact with your routines (but perhaps)... you might need some (named ?) TempCells for solver to put its results (i.e. SolvMin & SolvMax) and do some check if solver finds something different versus your integrator procedure (and if so)... for doing a (re)process changing your variables (target/changind cells) to the mim/max solver could found previous error free/checking routines (just in case ?) hth, hector. __ OP __ On thinking this through further: What I'm needing in principle is to have the call to the integrator and the call to the Solver in a loop to allow them to be repeated in sequence as necessary. The loop exit criteria will take place after the Solver call. This would be a brief outline of the process, and I seek your advice on this: After the integrator call is made and the integral result provided, Solver is called. It looks at the integral result (target cell), does some thinking then makes changes to the changing cells. Then at THAT very moment, I need Solver to quit ie release itself back to the code loop. (I don't want Solver to attempt to run its own iteration process to completion or at least attempt to do so, because it won't succeed, as it cannot invoke the integration routine after making changes to the change cells). At the time Solver releases itself back to the code loop, Solver would also need to provide a status value indicating whether the last presented target cell value is the lowest it can find, or not, allowing the code to either reloop or terminate. This (in theory) should do the job, I think. Q. Can the Solver be used in this manner, ie, allowing its internal processes to be interacted with in this way for program control purposes? Thanks for your help. Graeme PS Standing right back, it would seem that my requirement would not be very different in principle to any other situation where an iterative routine in VBA code is needed to calculate some mathematical function for whatever purpose and then to try to use the Solver in conjunction with it for finding some minimum or maximum value of it. Still, I'm not feeling very confident... Perhaps Solver's specifications don't allow for such interaction. Hoping you can identify a path through this. __ previous posts __ it's not clear (or viewable) "where" (in the code) could it be a integration with solver tool however (and AFAIK) once solver "solves" an equation... there is no way that solver seek for another (possible) solution unless you reset the solver arguments and you will get (probably) the same solution as before (perhaps) if you post the code (and a sample data) -?- __ OP __ I've written an iterative Trapezoidal integrator in VBA for integrating an analytic function f(x) where f(x) is defined in a cell, for example, B1: =3*A1^3 + 2*A1^2 + A2^3 - A3 where the integrating variable x is in cell A1, and cells A2 and A3 contain parameters. In this example, the cells B1, A1, the cells with the lower and upper limits and the cell to receive the result, are passed as arguments. All this works fine. Now, this is the part I 'm wanting to do, but cannot achieve. After the integrator produces its result, I'm wanting to call the Solver tool to minimise the integral result (target cell) adjust the parameters in 2 and A3 (changing cells), and then for the integrator to be run again, producing its next result. This process is to be repeated, until such time as the parameters have been so adjusted by the Solver and the integral result has reached its minimum value. ie, 1. Set up initial values. 2. The integrator determines its result. 3. Solver reads the result, then adjusts the two parameters. 4. Repeat steps 2 and 3, until the Solver has minimised the integral via the two changed parameters. I am unable to determine how to combine the iterative integrator with the Solver in the above manner so they alternately operate until such time that the integral has been minimised. Any guidance greatly appreciated. |
Using Solver to Minimise the Result of Another Iterative Routi
Hi Dana.
Thanks for your advice. One such expression I have is as follows (actually a distortion equation): f(x) = (( ASIN(( x^2 + 500*V1 - V1^2)/(500*x)) - V2) / x )^2 where V1 and V2 are the two independent variables. The function looks something like the letter W sitting on the x-axis. When V1 and/or V2 are varied, one, or two, of the three function peaks will either increase or decrease wrt to the other peak(s), thus changing the integral value. However, there is a unique pair of values for V1 and V2 which will give the minimum value for the integral of the above expression. I'm trying to find those values. With the values of V1 = 16, V2 = 0.38 (radians), lower limit of x = 60, and upper limit = 145, the numerical integrator gives a result of approx. 1.95 E-6. The final values of V1 and V2 to minimise the integral will be within +- 5% of the above values. (I calculate the integral to around 14 or 15 significant digits, but I'm only showing three in the result above). As I summarised in my last message, it appears there are some clear reasons for why I cannot achieve what I'm seeking, in the manner I'm proposing. Short of the Solver being modified to enable the exposure and control of its internal operations, including its ability to call an external sub (for evaluation of the integral), I think I'm out of luck with the Solver. It's specifications simply do not provide for such (obvious?) functionality. Graeme --------------------------- "Dana DeLouis" wrote: Perhaps the last para sums up the greatest problem I face - I want the Solver to tell me when the minimum has been found, but I'm expecting it to do so on a single pass Solver (or anything else that I can think of) can not do it on a single pass. Solver has to calculate it's derivative via finite differences, so it has to make a few small changes to get going. Just to throw it out, are you aware of the following structure?? Results = SolverSolve(True) Select Case Results Case 0, 1, 2 'Solver found a solution 'code Case 3 'Max Iteration reached 'code Case 4 'Solver did not converge 'code etc My goal is to minimize the integral of a given analytic function which has (say) two independent variables I'm still not clear on the process because it sounds confusing to me. Here's an equation. If a & b are equal, it's easy to see it's a circle. To minimize the integral over an area, we let say b tend towards infinity. As x varies, y has to approach zero. The equation is basically flattened into a line. The integral is 0 as b tends towards infinity. (If I understand what you are doing) I really don't see where Solver is needed for "1 pass". a* x^2 + b*y^2 - 4 -- HTH :) Dana DeLouis "Graeme Dennes" wrote in message ... Apologies to all for this long post, especially to Dana. Hi Dana. I'm going to need to spend some time going over your response, so I fully understand your suggestions. Thank you for your advice. I have a background in programming and mathematics, but little experience with Excel application development. Re posting a simple equation. In my opening message, I basically describe the process I'm trying to achieve. My goal is to minimise the integral of a given analytic function which has (say) two independent variables (parameters). I gave an example in the post. The idea is that a numerical integrator sub is called to calculate the integral. Following that, I then wish to call Solver, so it does some thinking, and then makes changes to the changing cells (parameters). Solver is then to exit, returning control back to the loop. An exit test then occurs. This way, the call to the integrator and Solver are to be repeated in a code loop, until the integral is minimised. However, to achieve this, I need Solver to do one iteration only, ie, after making changes to the changing cells, I want Solver to exit, and the exit test done, after which the integrator is called again, with its (new) result dependent on the changed parameter values. Then Solver does one iteration again and exits, etc, etc. When Solver runs, if it thinks the integral has been minimised, it exits with a particular status value, while if it thinks the integral hasn't (yet) been minimised, it exits with a different status value. Then the exit test (placed after the Solver call) reads the Solver status value, and either loops back to the integrator/Solver pair again, or else exits the loop, and the code terminates with the minimised integral value and the final values of the two parameters in specified cells. Overall, my problem here is to get the Solver to do one iteration only, then exit. ie, look at the target cell (integral result), think, then change the changing cells (independent variables), and then exit - just one single pass only. So, the sequence is: integrate, adjust parameters, integrate, adjust parameters, etc, etc, until the Solver indicates the integral has reached its minimum value. It is the final values of the two parameters which I seek. Another issue which has now come to mind is that for the Solver (or any similar purpose routine) to "know" when a minimum has been reached, it will (?) need to have in its "hands" the integral results and parameter values associated with the last several passes, so it can do its own algorithmic things and tests to enable it to decide if the minimum has been found. In the manner I am proposing above, no such previous results are stored, nor is Solver aware of them, as each time the call to Solver is made in the loop, it is starting afresh.... It holds no prior history, as I'm giving it a single pass only before it exits. Perhaps the last para sums up the greatest problem I face - I want the Solver to tell me when the minimum has been found, but I'm expecting it to do so on a single pass.... Still, I could store the last several integral results and parameter values, but then I need to tell Solver of their existence, as well as what I want Solver to do with them. Whew!!! The only (?) other alternative is that, ok, let Solver iterate in its usual manner, but that demands that Solver has the ability to repeatedly call the integrator sub after it makes changes to the changing cells. I am not aware that Solver has this ability. I understand that it can only read the target cell and change the changing cells - it cannot call a sub and process the result, all internally within itself... My initial thinking is that I cannot do this as I have described it. Therefore, I need to now consider your previois post in much detail. Perhaps the answer is in there. Wouldn't it be nice if the Solver tool functionality was to be revised to address the core issues discussed here? The Subject of this thread could indeed be achieved, making the Solver tool applicable to a brave new range of mathematical problems. Dana, if you have the patience to read the above, I'd be most grateful for your thoughts. Best regards. Graeme ----------------------------------- "Dana DeLouis" wrote: (If only it (Solver) had the option to allow me to intercede in its process. Oh well...) If you post a simple equation that you are trying to solve, perhaps someone could suggest an alternative solution. Solver doesn't have a method to do 1 or 2 loops, and then exit with a status message. However, there are other methods that can come close. The Option for "Max Iterations" is rather unclear because if you set it to 1, it's not really 1 iteration, but something a little more. Here's one idea based on time... SolverOptions MaxTime:=1 'Second Results = SolverSolve(True) If Results = 10 Then 'Stop chosen when the maximum time limit was reached. 'Code Here 'Remember Target Value 'Let's start over SolverFinish 2 'Don't keep results 'Increase time to 2 Seconds SolverOptions MaxTime:=2 'Seconds Results = SolverSolve(True) 'Do something based on the trend of the two solutions so far. The other option is more in line with your question, but it won't abort as quickly as you would like.. When you run a long Solver problem, we can intercept the converging solution with the following option. I've often used it to log the best solution over time Results = SolverSolve(UserFinish, ShowRef) Even when we set the smallest time/iteration interval, what this uses is something I've never been able to figure out. Basically, the smallest interval is something I'm not sure of despite numerous tests. It has to do with Solver having to do a few calculations to calculate a derivative / slope. The "ShowRef" is the name of a macro you can run to track the results of Solver's convergence. The "problem" though is that this is not documented to the best of my knowledge. Your macro has to set up some return codes itself, so it's a little tricky. However, a macro must also test other items in the Workbook, Worksheet, etc for this to work because there are unducumented things that will make Solver Abort / Skip this method. However, it does work. But note that for simple Solver problems, one will often get a solution "before" your macro is called for the first time. This is best used for "Long" solver processes. I've been wanting to do a web site and include this technique. Good luck. -- HTH :) Dana DeLouis "Graeme Dennes" wrote in message ... Hi Hector, It appears that Solver may not be the tool of choice. (If only it had the option to allow me to intercede in its process, as I described. Oh well...). I note that the publication "Numerical Recipes" 3rd edn ( www.nr.com ) describes several minimisation (and maximisation) algorithms for one or more independent variables in Chapter 10. One of these could act as the starting point, making the necessary changes needed, being that it is not the analytic function I need minimised, but the integral of the function. This way, I am creating my own "Solver" for this task. I'm sure it will be an interesting exercise. Thank you for your good advice. Best regards. ---------------------------- "Héctor Miguel" wrote: hi, Graeme ! i'm not so sure the solver tool is what you look for to interact with your routines (but perhaps)... you might need some (named ?) TempCells for solver to put its results (i.e. SolvMin & SolvMax) and do some check if solver finds something different versus your integrator procedure (and if so)... for doing a (re)process changing your variables (target/changind cells) to the mim/max solver could found previous error free/checking routines (just in case ?) hth, hector. __ OP __ On thinking this through further: What I'm needing in principle is to have the call to the integrator and the call to the Solver in a loop to allow them to be repeated in sequence as necessary. The loop exit criteria will take place after the Solver call. |
Using Solver to Minimise the Result of Another Iterative Routi
Hi Dana, you've done some great work here, and provide very useful insight. It appears then that Solver cannot help me anyway in this situation, regardless of the previously discussed issues. In that case, other tools will be needed... Your maths program seems to be very useful for such calculations, in so far as its precision is concerned, as well as its abilities to perform numerical integration (various methods) and function minimisation tasks. I'd be interested to know if your program is based on a standard program such as Mathematica? 1. In your experience, could you advise me which of the standard (or other) available programs are more appropriate for this type of work? 2. Are there such programs, similar to the Solver optimiser and your maths program, which can be called from within Excel via VBA, and which can make use of the Excel worksheet interface and the Excel VBA environment? In the meantime, I need to review the situation overall, based on your advice to date. Thank you for taking the time and effort to assist me here. I'm most grateful to you. Graeme ---------------------------------------- "Dana DeLouis" wrote: I think I'm out of luck with the Solver. It's specifications simply do not provide for such (obvious?) functionality f(x) = (( ASIN(( x^2 + 500*V1 - V1^2)/(500*x)) - V2) / x )^2 Wow. That's nasty! Looks like the Integral is hard because with your starting set of data, you have 3 areas to integrate. x) 60 to 63.4919 x) 63.4919 to 121.968 x) 121.968 to 145 Which is why finding a closed form is hard. I don't have an answer, but I could throw out some ideas... If we plot the Derivative of your equation from x: 60 to 145, the "best" we can do is about 5E-9. It's average is very close to zero. Well, this puts Excel's Solver out of the question right here! Solver, at best, is looking at a derivative of zero, and will quit! Done! (When Solver starts, it's finite differences of the input is apr 1e-8, so it's output is definitely zero with your derivative) numerical integrator gives approx. 1.95 E-6. (I calculate the integral to around 14 or 15 significant digits, but I'm only showing three in the result above). Here's a custom math program that didn't adjust any options, except for method. Basically, it agrees with your test results.(display accuracy: 5) Don't want to use another program here, but it may give you confidence in your vba program that you wrote. Automatic,1.95615*10^-6 ClenshawCurtisRule,1.95615*10^-6 GaussBerntsenEspelidRule,1.95615*10^-6 GaussKronrodRule,1.95615*10^-6 LobattoKronrodRule,1.95615*10^-6 LobattoPeanoRule,1.95615*10^-6 MultiPanelRule,1.95615*10^-6 NewtonCotesRule,1.95615*10^-6 TrapezoidalRule,1.95615*10^-6 AdaptiveMonteCarlo,1.94478*10^-6 AdaptiveQuasiMonteCarlo,1.95614*10^-6 DoubleExponential,1.95615*10^-6 DuffyCoordinates,1.95615*10^-6 ExtrapolatingOscillatory,1.95615*10^-6 MonteCarlo,1.94252*10^-6 QuasiMonteCarlo,1.95609*10^-6 Trapezoidal,1.95615*10^-6 EvenOddSubdivision,1.95615*10^-6 SymbolicPiecewiseSubdivision,1.95615*10^-6 OscillatorySelection,1.95615*10^-6 UnitCubeRescaling,1.95615*10^-6 If you would like, here is an attempt to find a solution for you using a math program. I looked at both a 3-Dimension Plot, and a Contour Plot of your data as v1 & v2 varied. Basically, the minimum integral was very flat near the solution, which leads me to be cautious of any solution. This doesn't lend itself to finding a minimum solution with normal accuracy. I tried it with 5 difference methods, but had to bump up some options... They all seem to agree with a value of about: 1.2422239376097 Method - ConjugateGradient 1.242223937609776745471796936814978806751241791062 1*10^-6, v1 - 16.59681987152812969758657295866485314913298904852 9, v2 - 0.378686765992950375586810189450921583710520665259 44 Method - Gradient 1.242223937609744769978038086977178977576841134578 0*10^-6, v1 - 16.59681924220979875329663329192423136140262458371 6, v2 - 0.378686758571369920465929815981736153772213226370 52 Method - Newton {1.24222393760974667580216940915294188130246766377 24*10^-6, v1 - 16.59681924221862631665794567940967845001729596882 4, v2 - 0.378686758571468371377975920758723531204765532812 47 Method - QuasiNewton 1.242223937609744558219801273402094210496215964667 5*10^-6, v1 - 16.59681924209372203360622328272050997293299257177 8, v2 - 0.378686758570205999184427836335752951443229840041 52 Method - InteriorPoint 1.242223937609744769978038086977178977576841134578 0*10^-6, v1 - 16.59681924221469841991958090435721239222211746227 5, v2 - 0.378686758571421922639720863172693913413215568270 09 I won't bore you with the caution messages. I'm not sure how I would do this in Excel at the moment. Good luck. : ~ -- HTH :) Dana DeLouis "Graeme Dennes" wrote in message ... Hi Dana. Thanks for your advice. One such expression I have is as follows (actually a distortion equation): f(x) = (( ASIN(( x^2 + 500*V1 - V1^2)/(500*x)) - V2) / x )^2 where V1 and V2 are the two independent variables. The function looks something like the letter W sitting on the x-axis. When V1 and/or V2 are varied, one, or two, of the three function peaks will either increase or decrease wrt to the other peak(s), thus changing the integral value. However, there is a unique pair of values for V1 and V2 which will give the minimum value for the integral of the above expression. I'm trying to find those values. With the values of V1 = 16, V2 = 0.38 (radians), lower limit of x = 60, and upper limit = 145, the numerical integrator gives a result of approx. 1.95 E-6. The final values of V1 and V2 to minimise the integral will be within +- 5% of the above values. (I calculate the integral to around 14 or 15 significant digits, but I'm only showing three in the result above). As I summarised in my last message, it appears there are some clear reasons for why I cannot achieve what I'm seeking, in the manner I'm proposing. Short of the Solver being modified to enable the exposure and control of its internal operations, including its ability to call an external sub (for evaluation of the integral), I think I'm out of luck with the Solver. It's specifications simply do not provide for such (obvious?) functionality. Graeme <snip |
Using Solver to Minimise the Result of Another Iterative Routi
Hi Dana,
Thank you for your excellent work, and your preparedness to work through this issue with me. Unfortunately, I don't understand how Solver has been able to minimise the integral, as your results show. 1. I did not know that Solver can (automatically) make repeated calls to the integration code, and wait for it to complete its loop before checking the new value in the target cell. I thought this was not possible. I thought Solver would only work with cell formulas. 2. Perhaps it can work this way where the code is written as a Function instead of a Sub...? 3. Perhaps it can work this way where the Solver is called via its dialog box, and not via the VBA code? Being well and truly far from an Excel expert, I'm obviously missing something very basic, in that I'm not properly understanding the thrust of your message. There's a small number of combinations of the points above which I don't have a strong grasp on, as you can see. I'd be most grateful for your clarifying advice on how you managed to obtain the Solver results. Graeme ---------------------------------- "Dana DeLouis" wrote: Hi. I don't see any of my posts in this thread, but I can see yours. I'll do my best with this limitation. I'm having a hard time with this one re-writing it for a simple vba routine. The equation squares the values, so the area under the x-axis flips to positive, and that's where we get 3 areas. I'm really at a loss for this one. Looking at a table of results near the solution show that it's easy for the 'next best guess' to diverge. Having looked at a graph, let's go back to Solver and try for a possible simple 3-4 digit accuracy solution. On a worksheet, set up two cells to hold v1 & v2, and place 16 & 0.38 in them. Enter the target cell of =Fx(A1,A2) (a1 = v1, a2 = v2) Let's make a very simple vba routine to calculate the integral. Here, I've just divided it into 0.01 sections. Good enough to about 3 digits. Function Fx(v1, v2) Const k As Double = 0.01 Dim x, t With WorksheetFunction For x = 60 To 145 Step k t = t + ((.Asin((x ^ 2 + 500 * v1 - v1 ^ 2) / (500 * x)) - v2) / x) ^ 2 Next x End With Fx = t * k End Function With Solver, try to minimize your Fx function. Set Solver's option to: Precision & Convergence to 1E-14 check "Automatic Scaling For the Estimates, try using "Quadratic" this time. (based on a graph of the data) I get: 1.24247E-06 v1: 16.5943 v2: 0.3787 This is only good to about 2-3 digits. Remember, we have a lot going against us. The integral is not very accurate to start. The slope oscillates a little close to the solution. It is also flat closer to the solution making next guesses difficult. And of course, Solver is not really good at working around small values like this. Yes, programs such as Mathematica can usually handle these types of problems. What's nice is sometimes a simple graph is great to give insight into a problem. I still show an approx solution is: 1.2422239376097446*^-6, v1 - 16.596819242093723 v2 - 0.378686758570206 I don't think I did anything wrong, but just for a laugh, here are some caution messages: NIntegrate::inumr: The integrand (<<1)^2/x^2 has evaluated to \ non-numerical values for all sampling points in the region with \ boundaries {{60,145}}. NIntegrate::slwcon: Numerical integration converging too slowly; \ suspect one of the following: singularity, value of the integration \ is 0, highly oscillatory integrand, or WorkingPrecision too small. NIntegrate::ncvb: NIntegrate failed to converge to prescribed \ accuracy after 9 recursive bisections in x near {x} = {67.8014}. \ NIntegrate obtained 4.278178128124259`*^-21 and \ 7.808237883702284`*^-22 for the integral and error estimates. FindMinimum::lstol: The line search decreased the step size to within \ tolerance specified by AccuracyGoal and PrecisionGoal but was unable \ to find a sufficient decrease in the function. You may need more \ than 100.` digits of working precision to meet these tolerances. Despite these, it still seems to like the solution above. -- HTH :) Dana DeLouis <snip |
Using Solver to Minimise the Result of Another Iterative Routi
Hi Dana,
I managed to reproduce your example code and obtain results identical to your own, so I now have a working test bed to review for understanding and enhancement. Your code examples have worked splendidly, and have given me much insight into using the Solver. I am now able to perform numerical integration on the function f(x), and to use Solver to minimise the integral by making changes to its two parameter values. Wonderful work! This has enabled me to achieve my initial goal in this thread. My next step will be to attempt to transplant my iterative integrator code. Now if only there was a way to define and use f(x) in a cell formula instead of coding it in VBA... Oh well. Thank you for the generousity of your time and knowledge, and patience to assist a beginner. I salute you. Graeme PS I understand that Frontline's Premium Solver has the option to call VBA code after making changes to the variable cells, then waiting on the completion of the VBA call before it checks the target cell's value... I think that would allow me to do this task via Sub calls, have f(x) defined in a cell, and have values passed from VBA to cells for use by f(x), such as the x value supplied by the integrator code. All outputs would be written to specified cells. This would still need to have the cells hard coded in VBA, as there's no way to specify them otherwise - I think. Now if a UDF could write to cells, all input and output cells could be defined totally in the UDF arguments. Life would be much easier. PPS Further to the above. As you are a very experienced programmer in Excel and VBA (and perhaps other areas), I'd appreciate your thoughts on this broad issue: Is there some fundamental reason/rule for why UDFs are not allowed to, say, write to cells? I don't mean because that is part of their specification, but why such a specification exists in the first place? Why does it HAVE to be this way? What/who would prevent that specification from changing/evolving in the future, and why should it be prevented from doing so? Taking it further, why shouldn't we have the option to use a third (yet to be developed) structure, which has perhaps the cumulative options and capabilities of Subs and UDFs? One such (new) structure could then do all the things we now do with Subs and UDFs. It would simply be a matter of setting a set of options to control or achieve the desired behaviour and outcomes. ------------------------------- "Dana DeLouis" wrote: Hi. 1. I did not know that Solver can (automatically) make repeated calls to the integration code I thought Solver would only work with cell formulas. Yes, Solver can work this way by using custom functions. (This is why Solver has to figure out the derivative via small samples around the area in question) 2. Perhaps it can work this way where the code is written as a Function instead of a Sub...? A function returns a value, so it must be a Function. 3. Perhaps it can work this way where the Solver is called via its dialog box, and not via the VBA code? It can work either way. Put 16 in A1, and 0.38 in A2. In C1, put the function =Fx(A1,A2). You should get the result of 1.95 E-6 With Solver, Minimize this Target value by changing the cells in A1:A2. For this example, I didn't add any constraints, but it's always a good idea. Change some of the options as mentioned, and after a while, I got a solution. In VBA Code, see if this works for you. You need to set a vba library reference to Solver. Sub Demo() [A1] = 16 'Changing Cells [A2] = 0.38 [C1].Formula = "=Fx(A1,A2)" '<-Target Cell 'Minimize C1... SolverOk SetCell:="C1", MaxMinVal:=2, ByChange:="A1:A2" SolverOptions _ MaxTime:=500, _ Iterations:=100, _ Precision:=0.000000000001, _ AssumeLinear:=False, _ StepThru:=False, _ Estimates:=2, _ Derivatives:=1, _ SearchOption:=1, _ IntTolerance:=1, _ Scaling:=True, _ Convergence:=0.000000000001, _ AssumeNonNeg:=False SolverSolve True End Sub 'This is a very simple integration of the function from 60 to 145. Function Fx(v1, v2) Const k As Double = 0.01 Dim x, t With WorksheetFunction For x = 60 To 145 Step k t = t + ((.Asin((x ^ 2 + 500 * v1 - v1 ^ 2) / (500 * x)) - v2) / x) ^ 2 Next x End With Fx = t * k End Function Again, not the greatest, but something that could work for now. -- HTH :) Dana DeLouis "Graeme" wrote in message ... Hi Dana, Thank you for your excellent work, and your preparedness to work through this issue with me. Unfortunately, I don't understand how Solver has been able to minimise the integral, as your results show. 1. I did not know that Solver can (automatically) make repeated calls to the integration code, and wait for it to complete its loop before checking the new value in the target cell. I thought this was not possible. I thought Solver would only work with cell formulas. 2. Perhaps it can work this way where the code is written as a Function instead of a Sub...? 3. Perhaps it can work this way where the Solver is called via its dialog box, and not via the VBA code? Being well and truly far from an Excel expert, I'm obviously missing something very basic, in that I'm not properly understanding the thrust of your message. There's a small number of combinations of the points above which I don't have a strong grasp on, as you can see. I'd be most grateful for your clarifying advice on how you managed to obtain the Solver results. Graeme <snip |
Using Solver to Minimise the Result of Another Iterative Routi
Thank you Dana. I now need to look at your example in detail.
Graeme ------------------------- "Dana DeLouis" wrote: Hi. Glad it helped. :) Now if only there was a way to define and use f(x) in a cell formula instead of coding it in VBA... Oh well. If I understand the question, I don't think it can be done like that. However, here's a poor-man's version of that idea. This is not the greatest idea, but can sometimes help in certain situations. The problem you have is that it's an integration. :~ Step1 just loads the formula for you onto a worksheet. Step2 grabs that string and tries to evaluate the function. Note however, that this method is rather slow. Again, not the best way, but something to add to your library of routines. Sub Step1() [C1].Formula = "=((Asin((x ^ 2 + 500 * y - y ^ 2) / (500 * x)) - z) / x) ^ 2" End Sub Sub Step2() Dim f, x, t Const k As Double = 0.01 'Load Start Values ActiveWorkbook.Names.Add "y", 16 ActiveWorkbook.Names.Add "z", 0.38 'Get Function as a string f = [C1].Formula For x = 60 To 145 Step k ActiveWorkbook.Names.Add "x", x t = t + Evaluate(f) Next x Debug.Print t * k End Sub -- HTH :) Dana DeLouis |
hi Greame and Dana
I just read your exchange of posts, and in fact I am struggling with a very similar problem. I have a macro to calculate an integral numerically, and would like to use solver to make the value of the integral equal to a given value by varying the domain of integration. Hence, I have the impression that the solution of Greame's problem might entail a solution of mine. I found your posts by just looking on the web with search on 'integral', 'solver', 'Excel'. There is however something weird, namely I can read Greame's posts, but not Dana's. Dana's texts seems to be encrypted in some way, and only appear as a set of symbols on my browser. First I thought that perhaps I needed to become a member of the forum to be able to read Dana's text, but now that I have subscribed, it still is unreadable for me. Can you help me out in some way?
Diederik |
hi Greame and Dana
Hi. I'm now trying out a different News Reader than Windows Mail.
Feel free to send me your data/equation. I'd be happy to look at it. - - Dana DeLouis Diederik Aerts wrote: I just read your exchange of posts, and in fact I am struggling with a very similar problem. I have a macro to calculate an integral numerically, and would like to use solver to make the value of the integral equal to a given value by varying the domain of integration. Hence, I have the impression that the solution of Greame's problem might entail a solution of mine. I found your posts by just looking on the web with search on 'integral', 'solver', 'Excel'. There is however something weird, namely I can read Greame's posts, but not Dana's. Dana's texts seems to be encrypted in some way, and only appear as a set of symbols on my browser. First I thought that perhaps I needed to become a member of the forum to be able to read Dana's text, but now that I have subscribed, it still is unreadable for me. Can you help me out in some way? Diederik |
hi Greame and Dana
Hi Dana,
I meanwhile have found that the exchanges you made with Greame appear in different fora. The one I could not read was the egghead, but now I answer you on the microsoft forum (where I can read the posts you made to Greame). Hence, the problem I am trying to solve is very similar. I need to 'fix the value of an integral over a fixed function by by making vary the domain of integration' and was attempting to do this with Solver. The problem is that the function is a Gaussian, more specifically a Gaussian in two dimensions, namely cEXP(-(ax^2+by^2)), where a, b and c are given constans, and x and y the variables of the function. Since it is a Gaussian, there does not exists an indefinite integral of it, and hence I need to make a numerical integration. The domain I need to integrate over is a square with fixed surface of 1/100 cm^2. Hence, what I had in mind is to parametrize such a square by its center, hence writing it as [X-0.05,X+0.05] times [Y-0.05,Y+0.05], and then taking X and Y as the parameters to vary by Solver to fix the integral to a given value. I found a code for VBA that does an integration of a function of two variables at http://digilander.libero.it/foxes/in..._integrals.htm My question is now. Would it be posisble to use the code in http://digilander.libero.it/foxes/in..._integrals.htm and work out a code for a "user defined function' for Excel, hence in my case a function of the variables X, Y, the coordinates of the center of the square of integration for the fixed function cEXP(-(ax^2+by^2)). I must admit that although I used Solver a lot, I am not capable of writing VBA codes. I am theoretical physicist, hence the math aspects are not problematic for me, but I know only little of programming. Anyhow thanks beforehand if you would be willing to ponder my problem Dana, Diederik |
| All times are GMT +1. The time now is 05:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com