# Solver problem where 'IF Function' is bad

1. ## Solver problem where 'IF Function' is bad

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.  Register To Reply

2. ## Re: Solver problem where 'IF Function' is bad

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  Register To Reply

3. ## Re: Solver problem where 'IF Function' is bad

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
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <dgtdkt\$2pae\$1@bunyip2.cc.uq.edu.au>, d.adamson1@uq1.edu.au
says...
> 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  Register To Reply

4. ## Re: Solver problem where 'IF Function' is bad

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.

"Tushar Mehta" <tm_200310@tushar_hyphen_mehta_dot_see_oh_em> wrote in
message news:MPG.1d9cb488470e815598b1f3@msnews.microsoft.com...
> 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
>
>  Register To Reply

5. ## Re: Solver problem where 'IF Function' is bad

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.

"Mike Middleton" <middleton@mikemiddleton.com> wrote in message
news:uPchss5vFHA.3452@TK2MSFTNGP14.phx.gbl...
> 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
>  Register To Reply

6. ## Re: Solver problem where 'IF Function' is bad

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  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1