Adding conditional statement (if) while optimising via solver

[Namrata23]

Hi. My problem is suppose I have 5 hours and hence 5 variables and i want that variables to lie between 0 to 500 with the constraint that next variable should lie between X(previous)-60<X(new)<X(previous)+180. First variable can be any. Apart from this each variable can either be 0 or 50% i.e 250 or lie between 250 to 500 following the previous constraint (of consecutive values). I am able to put the first constraint X(previous)-60<X(new)<X(previous)+180 but stuck up in 2nd one. It takes any value between 0 to 250 in between. Objective function is set.

 

[DushiPunda]

Not really following. Can you give us an example of what you have and example of what you're trying to achieve?

 

[Namrata23]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Demand[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]500[/TD]
[TD]650[/TD]
[TD]500[/TD]
[TD]595[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Max[/TD]
[TD]Min[/TD]
[TD]Off[/TD]
[TD]Cost for 1 unit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]650[/TD]
[TD]325[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]X1[/TD]
[TD]X2[/TD]
[TD]X3[/TD]
[TD]X4[/TD]
[TD]=sum(X1 :X4)*5[/TD]
[/TR]
[TR]
[TD]550[/TD]
[TD]275[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]Y1[/TD]
[TD]Y2[/TD]
[TD]Y3[/TD]
[TD]Y4[/TD]
[TD]=sum(Y1:Y4)*4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]load met[/TD]
[TD]=sum(X1+Y1)[/TD]
[TD]=sum(X2+Y2)[/TD]
[TD]...[/TD]
[TD]..[/TD]
[TD]=sum(above two)OBJECTIVE FUNCTION (MINIMISE)[/TD]
[/TR]
</tbody>[/TABLE]

The constraints on the variables (X1 to Y4) are as:
x1,2,3,4 <=650 ; y1,2,3,4<=550;
x1 to y4>=0;
x1-60<x2<x1+180;
x2-60<x3<x2+180; similarily for x4 and y2, y3 ,y4.
Also these variables can either be 0 or take value between 50% and maximum..i.e for x variables value can either be 0 or lie between 325 and 650.

I am stuck with the last condition. How to put this ?

 

[Namrata23]

x1-60<x2<x1+180;
x2-60<x3<x2+180; similarily="" for="" x4="" and="" y2,="" y3="" ,y4.
.. ignore this..
it is x1-60<x2<x1+180; applicable to all except the first ones, x1 and y1. i have incorporated this condition also.</x3<x2+180;></x2<x1+180;

 

[DushiPunda]

Yea, sorry..I'm completely lost.

So:
0 <= x1-4 <= 650 (greater than or equal to zero, less than or equal to 650)
0 <= y1-4 <= 550 (greater than or equal to zero, less than or equal to 550)

But you've lost me at the x1-60 and x2-60 part.

Are you saying that the value of x1-60 must be either 0 or somewhere between 50% and maximum?

And are you just trying to restrict the values that you can enter based on the conditions you've listed?

 

[Namrata23]

in simper words,In excel solver,i need a variable to either be zero or lie between 50% of max to 100%. i.e it should not lie between 0 to 50% but can be zero. how to add a constraint like this?

 

[Namrata23]

like for eg, if maximum is 500 the variable x should follow either x=0 or 250<=x<=500. I hope I am clear now. Sorry for the confusion.

 

[DushiPunda]

I don't know what excel solver is...but I think you really only have one option (Data Validation would be an option, but you can only set a minimum and a maximum (two values. Since you have 3 values (0, 50% of maximum, & maximum), I don't think data validation is going to work).

I think you'll likely have to do some magic within the Worksheet_Change event. I don't know if a macro is a viable solution for you, but without seeing your product, I can't think of any other way to achieve what you're trying to do.

Edit: Actually there is a Custom setting for Data Validation that may allow you to tell the cell to only allow 0 or a value between 50% of maximum & maximum...but I've never messed around with it. Might be worth looking into.

Okay I just did some testing with the custom data validation formula.

Let's assume that the maximum value is in Cell A1, and you're applying the data validation to Cell B1. The formula would be:

=OR(B1=0,AND(B1>=A1/2,B1<=A1))

Select Cell B1. Select Data in the ribbon bar. Select Data Validation. Change Allow: to Custom. Type the formula in the provided formula bar.

 

[Namrata23]

thanks for the help. Excel solver is a tool in data tab. It basically solves problems of linear programming. Can you suggest me some other platform that can do this ? Except writing a code. Any other optimisation tools

 

[DushiPunda]

If you're trying to restrict the data that can be entered into the cells, then data validation is going to be the way to go, especially since you've mentioned that you don't want to use code (macros). If you're not trying to restrict the data that can be entered, then I'm still lost and have no idea what it is you're trying to do.