Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How accurate is SOLVER?
I'm using SOLVER to change three variables in order to get a minimum
sum squared error. I've noticed that if I change the initial values for the 3 variables before running solver, that the solver will return different values! There should only be one value for each associated with a minimum sum of squared error, and I have placed non constraints on what Excel can change the three variables in order to reach that minimum value for the target cell (e.g. the sum squared error value). Therefore, what the values for those three cells are prior to running the solver shouldn't matter, even if they say nothing at all. This concerns me about the accuracy of SOLVER. Has anyone else had this issue? If so, is there an agreed upon way to set up the initial variables prior to running SOLVER? Please let me know if I need to elaborate. Thanks, Hosley |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How accurate is SOLVER?
"Augabog" wrote...
.... I'm using SOLVER to change three variables in order to get a minimum sum squared error. I've noticed that if I change the initial values for the 3 variables before running solver, that the solver will return different values! There should only be one value for each associated with a minimum sum of squared error, and I have placed non constraints on what Excel can change the three variables in order to reach that minimum value for the target cell . . . Therefore, what the values for those three cells are prior to running the solver shouldn't matter, . . . Well, you're dead wrong. First off, Solver, like any other iterative numerical optimization software, only iterates until the change between a specified number of iterations is less than a specified threshold. If the initial values lead to a plateau or local minimum, that's what Solver is going to give as its result, even if it's not a GLOBAL minimum. There are settings in Solver's Options dialog which YOU can change to increase the odds of Solver winding up with a global minimum. There's a lot of literature on the pitfalls of numerical optimization. You might want to consider buying a textbook on the subject. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I CALCULATE ACCURATE HUMAN AGES ON A SPREADSHEET ? | Excel Worksheet Functions | |||
My calculations are not always exact/accurate | Excel Discussion (Misc queries) | |||
How can I fit an accurate trendline and formulae | Excel Worksheet Functions | |||
goal seek wont calculate an accurate value past 3 decimal places | Excel Discussion (Misc queries) | |||
getting accurate results when sorting | Excel Worksheet Functions |