Hi everyone,
I need some help in solver function. I need to create a dynamic solver, that recalc every time the target of the function change.
Imagine this scenario:
The cell C2 has a function like "=IF(W4="";V4-V5;W4-V5)" and i want to use the solver for different vallues of cell C2 like Function=C2. Probably i will need a macro or something like that.
thks in advance
best regards
Solver is "normally" not happy with discontinuous functions i.e. "IF" and "CHOOSE" to name a few. The nonlinear GRG Solver used in Excel can sometimes deal with discontinuities which are "incidental" to the problem, but as a general statement, the nonlinear Solver cannot be expected to find optimal solutions to such problems. However, the Evolutionary Solver included in Frontline's enhanced Solver products can make progress on these problems.The cell C2 has a function like "=IF(W4="";V4-V5;W4-V5)"
I think the best solution would be a macro that checks the status of W4 probably a "Private Sub Worksheet_Change(ByVal Target As Range)" and if W4 = "" run solver1 macro else run solver2 macro.
Alf
I´m not sure you understood my question.
What i want is to make a routine that changes the content of this field (image) and recalc the function.
Capturar.JPG
The way I usually set this kind of problem up in Solver is to have a "helper" cell for the Set Target Cell. C3 can contain the target value, and C4 can be C2-C3. Then your Solver model becomes "Set Target Cell" C4 "to a value of" 0 by changing whatever C2 is dependent on. With this kind of model, your target value is always 0, so there will be no need to change the Value of field.
Originally Posted by shg
Capturar.JPG
i did no made myself clear. Hope this image help you understand.
I guess we're still not understanding exactly what the problem is.
You have given two equations for C4 -- In post one, you have indicated that it is a discontinuous IF() type function, and Alf explained how Solver tends not to like those kind of functions. In post five, C4 looks more like a simple algebraic equation. So maybe the first step in understanding your question is exactly what is in C4?
The second part of understanding your question is trying to understand exactly what you are using Solver to do. Solver is an excellent tool for finding roots of equations. If the expression in post 5 is correct, I would be tempted to suggest that the easiest way to solve that kind of equation is to solve it algebraically and program that solution into the spreadsheet -- Solver would not even be needed or used.
A sample spreadsheet would probably make this easier to understand than pictures.
Right click on the sheet where you have the C4 calculation and click "View Code". In the new windows that opens paste this code to it.
As soon as C4 changes i.e. any change made to either A1, C2 or B5 will run a macro called "test". So change this name to whatever you wish to call your solver macro.
Please Login or Register to view this content.
This of course assumes that C4 =(C2*A1^2+B5)/B5
It's also possible to substitute the line "Run test" with your solver macro. This is just a matter of personal preferences.
Alf
Last edited by Alf; 05-07-2014 at 12:14 PM. Reason: Wrong code given
https://www.dropbox.com/s/7emvsp85os...res%20EC2.xlsm
HOPE THIS HELP YOU.
Like the others, a file would help us help you better.
See if this gives you an idea or two.
When using the worksheet change event, I have done it like this:Please Login or Register to view this content.
This can also be handy.Please Login or Register to view this content.
Please Login or Register to view this content.
Sorry for all the confusion arround my question, please report to de XLS.
https://www.dropbox.com/s/7emvsp85os...res%20EC2.xlsm
Sorry for all the confusion arround my question, please report to de XLS.
https://www.dropbox.com/s/7emvsp85os...res%20EC2.xlsm
Right click your sheet VED's tab, View Code, and paste. Change X12 to 0 and 10 to see the two solutions for the X variable in X14's formula. Be sure to add the Solver reference as I detailed earlier in comments of similar code below.
Please Login or Register to view this content.
Thanks for you reply.
I didn't really understand what you meant about: "Be sure to add the Solver reference as I detailed earlier in comments of similar code below."
I´m not familiarized with VBA language.Could you please paste the exact code.
Adding this reference can take an extra step as I detailed before and here again. You have never added a reference? If not, see: http://excel-macro.tutorialhorizon.c...es-vbaproject/
' Add the Solver Reference:
' In the VBE, Tools > References, paste this in the File name box:
' C:\Program Files\Microsoft Office\OFFICExx\Library\SOLVER
' after replacing xx with your version of Excel (e.g., 11 for Excel 2003, 12 for Excel 2007, 14 fir Excel 2010).
' Change the file type dropdown to *.*, and pick SOLVER.XLA or SOLVER.XLAM
' You can't set a reference when code execution is suspended, so press the reset button (or do Tools > Reset), and then try.
Step by step...
-I added the solver reference .XLAM
-Pasted the code for the VBA console:
Capturar.JPG
And now??
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks