+ Reply to Thread
Results 1 to 6 of 6

Solver problem where 'IF Function' is bad

  1. #1
    David Adamson
    Guest

    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.




  2. #2
    Mike Middleton
    Guest

    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



  3. #3
    Tushar Mehta
    Guest

    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
    Multi-disciplinary business expertise
    + 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


  4. #4
    David Adamson
    Guest

    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
    >
    >




  5. #5
    David Adamson
    Guest

    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
    >




  6. #6
    David Adamson
    Guest

    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






+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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