Originally posted by: papap

Hi, I would like to ask whether CPLEX in Excel:

- Can provide the pool of solutions?

- How many constraints and decision variables can it handle?--is there any limit?

- Can it solve non-linear problems?Is there any plan for this in the future?

Kind Regards

I'm not an expert with the Excel connector, but you can find answers to some of your questions here.

- Can provide the pool of solutions?

At the least, you can step through MIP solutions.

- How many constraints and decision variables can it handle?--is there any limit?

I think you're limited to the number of rows/columns that Excel itself can handle.  The "important" message here, says, "All cells used in specifying a model (variables, constraints, bounds, and objective) must belong to the worksheet for which the model is defined."

- Can it solve non-linear problems?Is there any plan for this in the future?

On the Overview page it says,

IBM ILOG CPLEX for Microsoft Excel can also solve several extensions to LP:

• Quadratic Programming (QP) problems, where the LP objective function is expanded to include quadratic terms.

• Quadratically Constrained Programming (QCP) problems that include quadratic terms among the constraints. In fact, CPLEX can solve Second Order Cone Programming (SOCP) problems.

• Mixed Integer Programming (MIP) problems, where any or all of the LP, QP, or QCP variables are further restricted to take integer or binary values in the optimal solution and where MIP itself is extended to include constructs like Special Ordered Sets (SOS) and semi-continuous variables.

CPLEX cannot solve problems which contain terms which are not quadratic or linear in the decision variables. For example, CPLEX cannot solve problems where a decision variable is an argument to the exponential function.

Hope that helps.

Originally posted by: TalhaRashid

interesting..

Originally posted by: TalhaRashid

Hi I am new to CPLEX and am working on the below equations to be modeled:

There are a range of values of Ri, Cj and Ri* while sigma and B are constant. The issue is with the value of xj it says:

xj=

what code should I put it get this? I was thinking about an If statement but not sure. Kindly help with it. Up till now I have used the below code:

int M=...;  // Set of Risks
int N=...;  //Set of Mitigation Strategies

range risks=1..M;
range mitig=1..N;

float currentrisk[risks]=...;
float costmitig[mitig]=...;
float targetrisk[risks]=...;

//variables

dvar float+ R[risks][mitig];
dvar float+ x[mitig];

maximize sum(i in risks, j in mitig)R[i][j]*x[j]/currentrisk[i];

subject to {

sum(j in mitig) x[j]*costmitig[j] <= 0.7*2000000;

forall(i in risks)
  
currentrisk[i]-sum(j in mitig) R[i][j]*x[j] <= targetrisk[i];

forall(i in risks)

sum(j in mitig) R[i][j]*x[j] >= 0;

}

In the code currentrisk is Ri, Target risk is Ri* and costmitig is Cj. when I run it, at first it tries to install visual basic which it is unable to and then it gives me error saying: internal error please notify IBM

Kindly Help.

Hi,

if you want x to be binary you should write

dvar boolean x[mitig];

Can you attach .dat and xls file ?

regards

Originally posted by: TalhaRashid

There you go, uploaded. Oh okay, I'll put in the dvar boolean x[mitig] code for it. Also, do you think I need to install visual basic to run the model?

Originally posted by: TalhaRashid

Also, this is the result I am trying to reach at as attached.

Regards

Hi,

you do not need VB

I rewrote your .mod into

int M=...;  // Set of Risks
int N=...;  //Set of Mitigation Strategies

range risks=1..M;
range mitig=1..N;

float currentrisk[risks]=...;
float costmitig[mitig]=...;
float targetrisk[risks]=...;

//variables

dvar float+ R[risks][mitig];
dvar float+ Rx[risks][mitig];
dvar boolean x[mitig];

maximize sum(i in risks, j in mitig)Rx[i][j]/currentrisk[i];

subject to {

forall(i in risks,j in mitig) (x[j]==0)=>(Rx[i][j]==0);
forall(i in risks,j in mitig) (x[j]==1)=>(Rx[i][j]==R[i][j]);

sum(j in mitig) x[j]*costmitig[j] <= 0.7*2000000;

forall(i in risks)
 
currentrisk[i]-sum(j in mitig) Rx[i][j] <= targetrisk[i];

forall(i in risks)

sum(j in mitig) Rx[i][j] >= 0;

}

and I get a solution.

regards

Originally posted by: TalhaRashid

Dear Alex,

I have been trying since you posted this code but have been unable to run it. Every time I run it, it gives an error saying:

CPLEX ERROR 1016: PROMOTIONAL VERSION. PROBLEM SIZE LIMITS EXCEEDED

Initially I was using the Academic Student version of CPLEX and now I even downloaded my University specific version which is also academic but is valid for 24 months. The error is still there.

Also, is there any way to reduce the problem limits? How can I play around with that to make the code run?

Are there any other options I have?

Looking forward to your reply

Originally posted by: TalhaRashid

Also, attached are the files

Originally posted by: TalhaRashid

Really need help on this before I start pulling my hair guys!

See https://twitter.com/AlexFleischer1/status/788629015104000001

Originally posted by: TalhaRashid

Dear Alan,

Thanks for all your help. The model is now working, although not giving right results :(. Also, I wanted to know that for the variable R[risks][mitig], I have not provided any values for it in the software and since i is 10 and j is 20, I believe R[risks][mitig] will have 200 values, where do these values come from? Does CPLEX somehow finds all these values itself??

Hi,

who is Alan ?

R is used in

forall(i in risks,j in mitig) (x[j]==0)=>(Rx[i][j]==0);
forall(i in risks,j in mitig) (x[j]==1)=>(Rx[i][j]==R[i][j]);

So CPLEX will find the right values for R.

regards

Originally posted by: TalhaRashid

Sorry, Yes Thank You Alex, Appreciate!

Alex there is an issue I have observed, when I run the model and check the values of my variables currentrisk, costmitig and targetrisk for which the values I have provided in the excel sheet, I see the values are changed in the solution. After the 6th value of each variable, it becomes constant to a particular value.

For example these are the values in excel:

Originally posted by: TalhaRashid

Also, these are my final model files, it is unable to give me results for all the values, just giving me result for the first value while the rest of the values are becoming zero.

Originally posted by: TalhaRashid

Also, in order to give you a bigger picture, I am trying to remodel the attached work in the paper by myself.

Have not been able to get the first graph even.

Really banking on your support.

Best Regards,

Talha Rashid

Hi,

see https://www.ibm.com/developerworks/community/forums/html/topic?id=3faff55b-f6ab-416b-a452-bda2e22e5fba&ps=25

regards

Originally posted by: TalhaRashid

Hey Alex,

Based on the recent model that I have sent you, could you share the results you are getting for Rx and the variation in the values with respect to the variable y.

Currently I am just getting the solution solved for the first terms while the rest all becomes zero(it si solving for only 1st value of variable y).

Also, I am trying to follow the steps in the tech note you forwarded. I made the file and copied it into the destination where my opl projects are saved, I do not get the last step(see snap shot in attached file):

start the ide using command: oplide -debug oplide.options

Please explain how to do this. I am sending snap shots of how far I have reached.

Also, please do share your results too.

Best regards

Hi,

if you do not manage with the workaround yo may also wait for 12.7 : https://twitter.com/AlexFleischer1/status/796030528550043648

Or you may display the solution through writeln in an execute block

regards

Originally posted by: TalhaRashid

Oh that sounds good, would it be possible for you to share your results please so that I know its not for a lost cause.

Would really appreciate it!

Hi,

I see this.

regards

Originally posted by: TalhaRashid

oh.. you are getting all the values, that is great!

I am looking for a result for variation in values of y and the results associated with it. Just as attached below.

Could you please share the variation in y from your cplex model. Wouldn't have asked for it if it were not urgent, sorry.