Solver problem where 'IF Function' is bad

[David Adamson]

Solver problem where 'IF Function' is bad



What I'm looking at here is to determine if the salt level in the water
affects plant production.

What I had was



=(B181*(1-(B$130/100)*(IF($V56>B$129,$V56-B$129,0))))



Written in English is

B181 = max yield

B130 = salt threshold slope

B129 = salt threshold

V56 = salt in the water



So yield = maximum yield * salinity impact

Where salinity impact = Slope * Threshold Violation



As the Solver model was trying to solve a global solution and the salinity
impact changes as the level of salt changes in the water the threshold may
or may not be violated.

I need to be able to change the Threshold Violation into a number and not an
'If Formula'. It doesn't matter if salinity impact is less than 1 but the
number should not exceed 1.



Eg 1

Salt in the water = 40

Salt Threshold = 100

Threshold Violation = 0



Eg 2

Salt in the water = 120

Salt Threshold = 100

Threshold violation = 20



Any tips would be greatly appreciated on how I can avoid using an If or
other non-smoth function would be greatley appreciated. AS this formula is
currently used in a table of 18*11.

 

[Mike Middleton]

David -

Any tips would be greatly appreciated on how I can avoid using an If ... <

It may be possible to reformulate the problem so that you can use a binary
variable (integer zero or one) instead of the IF function.

- Mike
www.mikemiddleton.com

 

[Tushar Mehta]

The IF statement actually is a MAX(x-threshold,x) not that that helps
with Solver.

What does it mean the IF statement is "bad?"

What is the Solver model? The objective function? Are you maximizing
it? Minimizing it? What are the decision variables?

The formula you shared...what is it? How does it fit into the Solver
model.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[David Adamson]

Good questions.

Using latest and greatest Solver Platform V 6.5.

The Solver objective function is a profit maximisation problem.
Sumproduct(area * profit)

It is changing the area planted of different crops throughout a river basin
to maximise profit. As water is used upstream the salt level increases and
then at each stage

There are 20 stages in the model which water flows through. So as salt
increases yield drops in subsequent stages (Profit here = Yield * Price) and
each of the 12 crops have there own Salinity thresholds levels.

I have the sequential model working (i.e. maximise profit for each stage by
running solver seperately for each stage of the river) and that works fine
(gives the same answer in GAMS).

However, the formula isn't working when I try to solve all stages in a
global solution. It ends up with a value that is less than the sequential
version of the model and according to all economic theroy that just doesn't
happen. The number is also way less than the GAMS output that has been
generated.

The formula I shared determines if the salt level has violated the crops
threshold and if it has then the yield penalty kicks in which ultimatley
affects the profit for the crop.

I have been tearing my hair out with the model for a while until I read that
'IF statements' can cause problems with the solver.

I tried the trick of checking the model for structure, checked the model for
Transformed structure, selected the solve transformed problem and then used
solve the model and it told me the model was to big. (This is due to the
economic state contingency methodology we are useing as their are three of
the 20*12 tables to determine the effect of salt for 3 possible states of
nature).

Any suggestions would be appreciated.

 

[David Adamson]

Mike,

Thanks for the tip on the binary variable (I'll have a search and see what I
can find) as I haven't used that option yet and I always like seeing an
example.

 

[David Adamson]

Developing Binary Variables



I think I have this but I can 't get the Sumproduct to work



This gives me the correct answer if I have already determined Yield effects
of salt

Here Yield is a combination of production*Income already



Profit = Sumproduct (Area, Yield) - Sumproduct(Area,Costs)



Sumproduct(Area, Costs) = cost to produce

Sumproduct (Area, Yield) = return from production



What I need to do is alter the return from production to include the salt
impact within the function



Yield = Basic Yield - Threshold Impact * (Salt-Threshold)*Y



Where

Salt = level of salt in water

Threshold = salt level required to affect yield

Threshold Impact = impact of salt

Y = Dummy Binary Variable, which I then add as a constraint



But I have no idea how to put this into a Sumproduct formula to use in
Solver.



Sumproduct(Area, ?????)



Any suggestions would be greatly appreciated