maywood -

Do you have any ideas how to solve it correctly? <

If you have to use the standard Solver shipped with pre-2010 Excel, you can

replace the IF function with linear equations and a binary variable. See

below for a message containing a description of the method by Tushar Mehta.

Also, the topic is covered in most linear programming textbooks.

Is it possible to use an If-Then-Else Equation with the Solver? <

No, not directly. See above. The Premium Solver for Education, included with

many textbooks, can handle some non-continuous functions.

For more details, see

http://www.solver.com/xlsplatformb.htm
One of my recent messages is included below.

- Mike

http://www.MikeMiddleton.com
maywood said:

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

+++++++++++++++++++++++++++++++++++++++++++++++

I found where you address the IF statement, but I'm having trouble

following. I think this is on the right path for me, so I'd appreciate

any patience and help to clarify. I've posted your previous post below

with questions at the end of each sentence:

"First, the IF statement. Suppose that a firm has a choice of 2

plants where it can produce a product. If it uses a particular plant

to

produce any amount of the product, it incurs a fixed cost of say

$50,000." -----Do I understand this as choose the plant that creates

more product for the fixed $50,000 price?

"This has the nature of an IF statement of the type [IF x>0 then K

else 0], where K is a constant." -----I don't get what x or K

represent. Does x = amount of product and K = $50,000?

"One can replace the IF with linear equations by introducing a binary

variable, b, and a large constant, say, M. Now, the IF statement

becomes

K*b

x <= M*b

b = 0/1 (b is binary)

x >= 0

How does it work? If x is anything other than 0, the x <= M*b will

be

satisfied only if b is 1. If b is 1, the K*b will evaluate to K!

Also,

since M is a very large number, once b is 1, x <= M*b will always be

true no matter how large x becomes". ----Does x represent essentially

the binary threshold (i.e. less than x then with this plant, more than

x go with the other plant). If so, can it be a non-zero number? ----

Also I don't get how the binary is applied in Excel.

Thanks!

K

+++++++++++++++++++++++++++++++++++++++++++++++

Kerry -

Are there other Solver limitations I need to know about that could be

causing the issue? <

Bernard Liengme suggested checking

www.solver.com, where you will see that

Premium Solver can automatically transform nonsmooth functions like IF, MIN,

MAX, ABS, AND, OR, and NOT. As you have found, standard Solver generally

cannot.

For the standard Solver add-in shipped with pre-2010 Excel, Tushar Mehta

suggested a workaround for dealing with the nonsmooth IF function using a

binary variable.

For a brief description of these issues, see

http://www.solver.com/xlsplatformb.htm
What is a binary variable in excel, how do I incorporate it and wouldn't

it also cause the function gaps or sudden jumps that Solver has issues

with? <

A binary variable is restricted to the values zero or one. On the Solver

Parameters dialog box, you click the Add button (for the Constraints),

specify the cell reference where your model's binary variable is located,

and use the unlabeled "relationship" drop-down list to select "bin," which

automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth

function, because Solver uses a different algorithm for model formulations

that contain a binary or integer variable.

- Mike

http://www.MikeMiddleton.com