Excel 2003 + Solver

[xxsawer]

Hi,
I was solving some task and needed to use Solver in MS Excel. When I
finished all tables, all conditions and did some settings to Solver and tried
to solve it. I always got a message that Solver didnt find a solution...
The problem is that Solver even didnt try to find it. How do I know that? I
used "Show Iteration Results" and Changing cells always stayed all 0. After 3
iterations Solver stoped and no result was found.
After hours of experimenting with settings I was able to reduce the problem
just to few tables and here it is:

http://www.stud.fit.vutbr.cz/~xslavi13/snap038.jpg

I have some table (Changing table) where Solver is trying to put some
values. Conditions for this area are >= 0 and integer. The goal is to have
solution equal of 10. The function in the target cell is SUM of the Countif
table.
The Countif table counts number of columns of the Changing table > 0 (using
COUNTIF function).
If you run Solver on this task, NOTHING happens and Solver immediately tells
no solution was found. Solver even does not try to put some initial values.
You can see this when you use Show Iteration Results in Solver options.

Second table IF
There is just simple IF function. If appropriate cell in the Canging table
is > 0 then put 1, otherwise 0. If I use SUM of this table for the target
cell, again Solver even does not try to put some initial values end ends
immediately with no result.

Third table SUM
There is SUM of each row of the Changing table. If I use SUM of this table
for the target cell, Solver finds correct sollution.

Why???
Why it does not work for previous two tables???

I put the .xls I am talking about here:
http://www.stud.fit.vutbr.cz/~xslavi13/solver1.xls
Note...it came from czech excel, I am not sure if it is not necessary to
rename the function names to English equivalent...

I will explain another problem with Solver in little more complex example...
I have these tables:
http://www.stud.fit.vutbr.cz/~xslavi13/snap039.jpg
Changing table has to be again integers >= 0, SUM table below it is sum of
each column of the changing table and the sum has to be equal of 2000, 3000,
etc. SUM to the right of the changing table is sum of each row and each of
this sum has to be <= 10000. If there is at least 1 item in a row of the
Changing table, there is an addition to final prise indicated to the right.
At last, values of the changing table are multiplicated with corresponding
values of the left table and the result is put to the table without any name.
Solution is SUM of the table without any name + SUM of the table Add to
final prise.
What is the problem?
When I use Solver for this problem with default settings, the result is
total nonsence...
OK, I change the precision to 1E-100 , I get again nonsence result BUT even
with decimal digits. How is it possible that if there is condition in the
Changing table that all values have to be integers, Solver puts there e.g.
1044.891 ???

Ok, I use Central Derivatives option, results are not decimals, but again
the result is NOT minimal.

When I change also Estimates to Quadratic I get correct result...
WHy??? According to help, Estimates relates only to some initial values and
have nothing common with the evaluation of the result...

Second .xls I was now talking about can be found here:
http://www.stud.fit.vutbr.cz/~xslavi13/solver2.xls

If anyone knows the answers I would be really grateful

 

[Dana DeLouis]

The Countif table counts number of columns of the Changing table> 0

(using COUNTIF function).

Second table IF
There is just simple IF function.

In a nutshell, Solver can not work with CountIf and If functions in the
model. This is because these are discontinuous functions. Solver is
not capable of determining a "finite difference" to help with a
derivative. Solver will often give up without warning at the first sign
of confusion.
Your model would have to be reworked without these functions.
= = = = = = =
Dana DeLouis

 

[xxsawer]

Ok, I understand...
So what functions can I use and what functions not? Is somewhere a list?
I was searching in Solver help and didn't find anything like that...The only
limitation I found is that Solver can't handle more than 200 changing cells...
Other problem is...if I omit that condition where using IF and COUNTIF
functions and use only those limitations shere SUM is, then set precision to
1E-100, convergency to 1E-16 e.g., I will not get correct result. BUT if I
change Derivatives to Central and Estimates to Quadratic, I will get correct
result.
Why???
Or in other words...how can I be sure that the result is correct when Solver
finds it only under certain conditions. Moreover if I keep Estimates to
Tangent, I will get a message that target cell is not convergenting...

And last question...is there any other tool (I tried tool from solver.com)
that can handle IF, COUNTIF and similar function and where I get correct
result when it exists?


Dana DeLouis píše:

 

[Dana DeLouis]

So what functions can I use and what functions not?

Hi. I can't seem to find the old reference either.
Basically, they are functions that, if plotted, would not have a smooth
derivative. Functions like: IF, CHOOSE, LOOKUP, Max, Min, Count*, etc

then set precision to
1E-100, convergency to 1E-16 e.g., I will not get correct result.

Don't hold me to this, but I believe Solver uses a finite difference of
E-8 to calculate a finite difference (Used for Derivative)
Using a very small precision has unknown consequences...depending on the
complexity of the model of course.

BUT if I
change Derivatives to Central and Estimates to Quadratic,
I will get correct result. Why???

These use different techniques in determining which way to move. Your
initial values may have been close to the solution that it just "happen"
to work.

Are you sure it's the correct solution? Remember, Solver can only find
"Local" solutions, and not "Global" solutions.

For example, use Solver to find the minimum of
x*Sin(x) with the constraint x is between 0 and 15.

If you start with x near 0, Solver will happily tell you the solution is
0 without any warning. However, this is wrong!

Start with x near 5, and it will tell you the solution is -4.8. This is
wrong also.

Start with x near 11, and it will tell you the correct solution near -11.04

As you see, it depends on where you start !!!

Just to give a taste of some "possible" solutions, here is one topic.
If one wanted to choose between two values, one can not use IF()
function. What one would do is add a constraint (say cell B1) to
"Binary". This makes it either 0 or 1.

To choose between A1 or A2, you would write it as
= A1*B1 - A2*(B1-1)

If Solver tries B1 to 0, then you have A2. If Solver tries B1 to 1, you
have A1.

= = = = = = = = =
HTH
Dana DeLouis