Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeated application of Solver to new equations
I'm trying to help a friend with this one. He has about 5,000
equations, which for the sake of discussion can be taken of the form 2*x^2 - 5*x + 1. The multiplicity of equations comes about because each eqn. has a different coefficient of the linear term in x. So the picture is something like this: there is a column of (say) 5000 values of the linear coefficient - in column A, say. Column B holds the quadratic, using the linear coefficient from Col. A. What he wants to do is to repeatedly apply Solver to all these equations in turn. I am (very) weak in VBA, but this simple macro/program (basically obtained by recording) does work ok, interactively, for one equation: Sub Solve() SolverOk SetCell:="$E$2", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$2" SolverSolve End Sub Would anyone have any ideas which would help to streamline the process of obtaining the solution of 5000 such equations? Thanks very much for any advice, John Cordes |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeated application of Solver to new equations
The general solutions to a* x^2+b*x+c==0 is...
= (-b - Sqrt(b^2 - 4*a*c))/(2*a) = (-b + Sqrt(b^2 - 4*a*c))/(2*a) Would that help? Solver would give only 1 solution. The two solutions to your example (2*x^2 - 5*x + 1) would be: 0.2192235936 2.280776406 HTH -- Dana DeLouis Win XP & Office 2003 "John Cordes" wrote in message ... I'm trying to help a friend with this one. He has about 5,000 equations, which for the sake of discussion can be taken of the form 2*x^2 - 5*x + 1. The multiplicity of equations comes about because each eqn. has a different coefficient of the linear term in x. So the picture is something like this: there is a column of (say) 5000 values of the linear coefficient - in column A, say. Column B holds the quadratic, using the linear coefficient from Col. A. What he wants to do is to repeatedly apply Solver to all these equations in turn. I am (very) weak in VBA, but this simple macro/program (basically obtained by recording) does work ok, interactively, for one equation: Sub Solve() SolverOk SetCell:="$E$2", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$2" SolverSolve End Sub Would anyone have any ideas which would help to streamline the process of obtaining the solution of 5000 such equations? Thanks very much for any advice, John Cordes |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeated application of Solver to new equations
Dana DeLouis wrote:
The general solutions to a* x^2+b*x+c==0 is... = (-b - Sqrt(b^2 - 4*a*c))/(2*a) = (-b + Sqrt(b^2 - 4*a*c))/(2*a) Would that help? Solver would give only 1 solution. The two solutions to your example (2*x^2 - 5*x + 1) would be: 0.2192235936 2.280776406 HTH Thank you for responding. Probably the quadratic expression was a poor choice to illustrate the problem. I believe my friend's actual usage will require solver's capabilities, if he is to use Excel at all. So let's assume that the equations he really needs to solve are either transcendental, or perhaps higher order polynomials. I am waiting for him to get back to me about this, so I will know more precisely what form of equations he wants to solve. I suppose it is possible that he really does have quadratics, but was thinking in terms of Solver (actually he was originally using Goal Seek, till I mentioned Solver - would Goal Seek be any easier?) because he wants to specify some range for the desired zero. Maybe there could be another column to test the explicit quadratic formula results for a desired interval. If we use the quadratic formula we'd have to be careful to do some testing and use a numerically stable form. Regards, John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeated application of Solver to new equations
Tushar Mehta wrote:
If you still have to use Solver after reading Dana's post... The easiest way to use Solver is to solve different problems is to set each up anew and use SolverReset to reset the previous conditions. So, in the simple example of the 'Set Cell' being column E and the 'By changing cell' being column B, and the different problems constructed in rows 2:10, one would use the untested: for i=2 to 10 SolverReset SolverOk SetCell:="$E$" & cstr(i), MaxMinVal:=3, _ ValueOf:="0", ByChange:="$B$" & cstr(i) SolverSolve SolverFinish 'I believe you will need this; check the help Next i Tushar, Thank you - your suggestion of using SolverReset seems to be working in one test I've made so far (only 2, rather than 10, equations). One has to click OK on Solver's result after each equation, so 5000 will be rather painful by this technique, unless there's also a way to use VBA to accept each result in turn (ignoring any error trapping for now at least). John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeated application of Solver to new equations
Maybe there is something useful in he
http://support.microsoft.com/support...ver/solver.asp Creating Visual Basic Macros that Use Microsoft Excel Solver -- Regards, Tom Ogilvy "John Cordes" wrote in message ... Tushar Mehta wrote: If you still have to use Solver after reading Dana's post... The easiest way to use Solver is to solve different problems is to set each up anew and use SolverReset to reset the previous conditions. So, in the simple example of the 'Set Cell' being column E and the 'By changing cell' being column B, and the different problems constructed in rows 2:10, one would use the untested: for i=2 to 10 SolverReset SolverOk SetCell:="$E$" & cstr(i), MaxMinVal:=3, _ ValueOf:="0", ByChange:="$B$" & cstr(i) SolverSolve SolverFinish 'I believe you will need this; check the help Next i Tushar, Thank you - your suggestion of using SolverReset seems to be working in one test I've made so far (only 2, rather than 10, equations). One has to click OK on Solver's result after each equation, so 5000 will be rather painful by this technique, unless there's also a way to use VBA to accept each result in turn (ignoring any error trapping for now at least). John |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeated application of Solver to new equations
Tom Ogilvy wrote:
Maybe there is something useful in he http://support.microsoft.com/support...ver/solver.asp Creating Visual Basic Macros that Use Microsoft Excel Solver Thank you, Tom. That looks to be a very comprehensive page - a lot of reading required! The crucial step suggested there seems to be to use SolverSolve UserFinish:=True That prevents the Solver dialog box from popping up. Progress is being made, thanks to you knowledgeble and generous folks here. John |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeated application of Solver to new equations
You may want to consider these additional ideas with Solver...
Sub Demo() Dim R As Long 'Row Dim Results As Long For R = 2 To 10 SolverReset SolverOk _ SetCell:=Cells(R, 5).Address, _ MaxMinVal:=3, _ ValueOf:=0, _ ByChange:=Cells(R, 2).Address Results = SolverSolve(True) Select Case Results Case 0, 1, 2 'Results should be valid SolverFinish ' Keep results (default) Case Else 'Your code here 'SolverFinish 2' You may want to discard results here End Select Next R End Sub = = = = = = = Case 0,1,& 2 correspond to these messages... Sub Demo2() '// Dana DeLouis Dim Result As Long With Workbooks("SOLVER.XLA").Sheets("LANGUAGE") For Result = 0 To 2 Debug.Print Result; .Cells(Result + 51, 1) Next Result End With End Sub Returns the following... 0 Solver found a solution. All constraints and optimality conditions are satisfied. 1 Solver found a solution within tolerance. All constraints and optimality conditions are satisfied. 2 Solver has converged to the current solution. All constraints are satisfied. -- Dana DeLouis Win XP & Office 2003 "John Cordes" wrote in message ... Tom Ogilvy wrote: Maybe there is something useful in he http://support.microsoft.com/support...ver/solver.asp Creating Visual Basic Macros that Use Microsoft Excel Solver Thank you, Tom. That looks to be a very comprehensive page - a lot of reading required! The crucial step suggested there seems to be to use SolverSolve UserFinish:=True That prevents the Solver dialog box from popping up. Progress is being made, thanks to you knowledgeble and generous folks here. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I use the solver to solve polynomial equations? | Excel Worksheet Functions | |||
Printing text in a repeated cell/row that is longer than repeated | Excel Discussion (Misc queries) | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Resetting Solver Manually to Fix Solver Bug | Excel Programming | |||
Repeated application of AutoFilter | Excel Programming |