Alec Erskine -
It is my understanding that the only change to standard Solver from Excel
2003 to Excel 2007 is to accommodate the increase in rows and columns.
According to the Help item "Excel specifications and limits, Worksheet and
workbook specifications," the number of "Adjustable cells in Solver" is 200
in both Excel 2003 and Excel 2007. Each of those adjustable cells can be a
binary variable used in a knapsack problem.
- Mike Middleton
http://www.MikeMiddleton.com
"Alec Erskine" wrote in message
...
I am delighted to see that Microsoft have made an important development in
Excel 2007 with their optimiser. You have effectively introduced a
combinatorial optimiser in Excel 2007 which was not available in Excel
2003.
This allows us to solve the €śKnapsack problem€ť in Excel. The Knapsack
problem, sometimes called the Capital Investment problem is as follows.
Suppose we have a list of schemes, each with defined cost and defined
benefit, and we have a budget ceiling. Which schemes should we do to
maximise the total benefit, while keeping the total cost under budget?
The
Excel version appears to use the fairly robust method of a standard linear
simplex optimiser combined with a branch-and-bound searcher. Can you
confirm?
An example involving 70 schemes with randomised C and B is easy to create.
There seems to be a small starting position effect in that you seem to
have
to run the optimiser twice from 0s to get it to work properly, it only
gets
the €śright€ť answer €“ or what I hope is the right answer on the second
attempt.
I would have assumed that this optimser is independent of starting
position
and am quite worried that it claims to have optimised when it has not.
This
is a bug, really. Any thoughts as to why it is finishing at the wrong
answer?
I cannot find any email addresses on your site to send this to and am
being
warned not to send contact information. So I guess that's it then.
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/comm...lic.excel.misc