precision in solver

[Randi]

Hi.. Does anyone know what the precision means when you run the solver? We
have to change the number from 0,000001 to 0,001 before solver could find an
optimal solution but I don't know how this affect the solution.
I hope someone can help us!

 

[driller]

Randi,

The solver solution will have acceptable results as to be dictated by the
sensitivity of your objective, below is a documentation from xls help file.

Degree of precision
In the Precision box, type the degree of precision that you want— the
smaller the number, the higher the precision.

*IF-*
Solver cannot reach an optimal solution.
The following lists completion messages displayed by the Solver.
Solver cannot improve the current solution. All constraints are satisfied.
Only an approximate solution has been found, but the iterative process
cannot find a better set of values than those displayed. Either further
accuracy is not achievable, or the precision setting is too low.
Try changing the precision setting in the Solver Options dialog box to a
larger number, and then run the problem again.

regards,

 

[Joe User]

Hi.. Does anyone know what the precision means when
you run the solver? We have to change the number from
0,000001 to 0,001 before solver could find an optimal
solution but I don't know how this affect the solution.

It may or may not have. Most numbers with decimal fractions cannot be
represented exactly in the internal form that Excel uses (binary
floating-point). This is especially true of computed values. So it is very
common that when we intend to test the equality of two numbers, A and B, we
actually test ABS(A-B)<=precision -- that is, "is the difference within some
precision?". I believe this is how Solver uses the Precision option.

 

[Jerry W. Lewis]

It is difficult to say in the absense of details about your calculation. One
example where it would be quite acceptable would be if you asked Solver to
find inputs that would make
=(calc-const)
equal to zero, where const is a lage value, say around 1E+12. In that
case, precision of 0,001 would mean that calc and const differened in the
16th figure, (beyond what Excel will display). Thats as close to a solution
as you could hope to get, unless you happened to luck out and get exact
equality.

Jerry

 

[Dana DeLouis]

Hi.. Does anyone know what the precision means when you run the solver? We
have to change the number from 0,000001 to 0,001 before solver could find an
optimal solution but I don't know how this affect the solution.
I hope someone can help us!

Hi. Precision in Solver isn't so much about numerical calculation. It
is more about telling Solver what is "acceptable" in order to Solve
certain problems in a reasonable time, or to solve them at all.
Because of the hundreds of functions a spread sheet can have, it is
impossible to calculate a "derivative" of the target cell that is made
up of many dependent cells. Solver uses "finite differences" to around
10^-8 to determine a derivative for it calculations. When Solver
converges on a solution in certain models, it is often that Constraints
are not met "exactly".
For example, you may have a constraint that xx <= 6.
If Solver ran for a long time, and found that a constraint was xx =
6.0001, what would you do? Do you accept the solution, or do you run it
for a much longer time to try to get it to xx = 6.000000001, or 6 exactly?
You may need it <=6 exactly, but it is often the case that this is not
necessary.
It sounds like your model was caught up in this. It really depends on
the complexity of your model.

If is hard to quantify the precision of Solver's solution also in part
to the options you see in Solver's options. Changing the Solver option
for "Derivatives" and "Search" can also have an impact on the final
solution.

One common situation is using Solver for Financial analysis is having a
constraint like x=0. In these models, using a precision of 0.0001 is
often good enough by allowing x to go as low as -0.0001, This is
usually "good enough" and there really isn't any need for Solver to work
a long long time with a precision of 0.000000001.

If I need buy pipes cut to 50', I would hate to be told I need to spend
$10,000 where the constraints were met at exactly 50', but Solver passed
on a earlier possible solution of $2,000 when the constraint were met at
49.999999. We have to know our model, and know that this is for all
practical purposes good enough to be 50.0. Again, it all depends on
the model. It's more of an art than science when picking an acceptable
precision for your model.

HTH
Dana DeLouis