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