Effect of manual/automatic calc Solver (2003 SP2)

Topic

New Reply

Subject edited by HansV to summarize question (“Excel 2003” was rather general)

We have a user who has asked the following question:

Are there any research reports out there that does any study on the advantages of using Automatic calculations versus Manual Calculations in Excel? Also how does having either one affect running the “Solver Add-in program”?

This is relevant to my deal because I ran a scenario in my model using a solver add-in program to seek a desired solution (I don’t know whether manual or automatic calc was on). I ran “solver” a few more times and arrived at a solution. Afterwards, I ran solver again and this time my answer changed by .001 however the prior iteration was already the “final” version. One is unable to “undo” after solver is run. My team needs to know whether it matters or not if Auto or manual calc is on when running “Solver”. I contend that it doesn’t matter which type of calculation is turned on because solver will find the solution you are looking for regardless of which calc is turned on.

Does anyone have any insights they can share?

Reply | Quote

Replies

Welcome to Woody’s Lounge!

In general, it is advisable to have automatic calculation turned on, because if it is set to manual, it is all too easy to forget that the results you see on screen may not be accurate because the formulas haven’t been recalculated since the last change.
You should set calculation to manual only if you’re working with very large and complicated models where each recalculation takes a long time.

When you run the Solver, Excel forces formulas to be recalculated, regardless of whether calculation is set to manual or automatic – otherwise, it wouldn’t be possible to find a solution! So it doesn’t matter whether calculation is set to manual or automatic, as far as Solver is concerned.

Depending on the nature of the model, there may not be an exact solution. In that case, Excel will try to find the nearest approximation within the boundaries specified in the Solver > Options dialog. This approximation depends on the start values, so if you run Solver several times, the solution it finds may be slightly different each time. That is nothing to be worried about.

Reply | Quote

Just a minor addition to Hans’ response:

If you use Excel models that have intentional circular references (and many do in financial institutions), then you should set your calc mode to manual and enable iterations. Some such models will not even open unless these are the settings.

Regards,

Reply | Quote

Good point!

Reply | Quote