Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver with If-Then-Else?
You can generally emulate an IF statement using binary variables (adjustable cells restricted to integer values of 0 or 1) and some constraints. For example, say you wanted cell A1 to be 0 or between 500 and 5000. You create an adjustable cell, Z1, and restrict it to be binary (0 or 1). Then add the constraints:
A1 <= Z1*5000 A1 = Z1*500 If A1 is greater than zero then the first constraint forces Z1 to be 1. If Z1 is one, then the second constraint forces A1 to be greater than 500. This example uses an upper bound of 5000. However, in your example you do not appear to have a production capacity. In this case you need to put in some artificial upper bound ? something high enough you know it will not hit. I work for a company that has developed and maintains What?sBest, an optimization add-in for Excel that essentially competes with Excel?s internal solver. It would allow you to use standard Excel IF statements to model this situation. If you wanted to check it out you could download a trial version from www.lindo.com maywood wrote: Solver with If-Then-Else? 14-Dec-09 Hi, I am using the Excel Solver to optimize my production-quantity. Now I have a problem with 2 products X & Y which are produced on the same machine: The sum of production of X & Y is capacity constrained. There are also restrictions concerning a minimum and maximum inventory for both products. Demand should be satisfied each month. Demand can be satisfied through usage of inventory, production and external purchase of a product. The Solver should minimize costs (inventory=cheapest, purchase=most expensive) times quantities of inventory, production and purchase. The problem is the following: The production of product Y is a campaign. That means the production should only start when there are insufficient inventories of Y. If it starts the minimum production quantity is 500. If the inventory is sufficient, the production should be 0. As you can see in the example file that doesnt work at the moment. The Solver cant find a solution and the production of Y is min. 500 each month. Do you have any ideas how to solve it correctly? Is it possible to use an If-Then-Else Equation with the Solver? http://www.herber.de/bbs/user/66575.xls Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Dr. Dotnetsky's Cool .NET Tips and Tricks No. 25 http://www.eggheadcafe.com/tutorials...-cool-net.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver VBA - Defining Solver Options | Excel Programming | |||
referencing Solver.xlam vs. Solver.xla - 2007 vs. 2003 | Excel Programming | |||
How to replace Excel solver with some free solver codes in Excel V | Excel Programming | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Resetting Solver Manually to Fix Solver Bug | Excel Programming |