Solver Problem - constraints

[WhamBam]

Hello Excel Experts

I have a problem concerning the excel solver, I was hoping you could help me with.
I am trying to do an optimization of a problem, where I wish to find the minimum value of a cell with a set of changeable variables. The variables can take on any values, but must all sum to 1. I have the following problems concerning constraints:

1) I wish to set the following constraint for each of the variables:
x(i) = "0" or x(i) > 5% or x(i) < -5%. Meaning that each variable should be unable to take on values between -5% and 5% (excluding "0").

2) I wish to add a constraint that sets a maximum number of variables "in use", meaning that e.g. a maximum of 4 out of the total set can take on values other than "0".

Best Regards

WhamBam

[eorlandi]

It's hard to tell without seeing an example of the file but one thing for sure is that you have incompatible constraints the way you currently have the problem set up. You can't require a variable (x(i)) to be equal to zero and also to be greater than 5 or less than negative 5. It can't be equal to more than one value at a time. Even if we ignored the =0 constraint, you can't have a variable required to remain in two non-contiguous regions (like >5 and less than -5) at the same time. The Solver model doesn't understand that you are allowing the variable to be EITHER >5 OR <-5. It assumes that all constraints must be met simultaneously.

What is it you are trying to accomplish. Perhaps a little more detail would help.

[WhamBam]

Hi Eorlandi
Thanks for the quick reply.

I am doing a portfolio optimization problem using the excel solver. I have 10 possible asset classes, where I have calculated the mean returns, standard deviations and covariances in order to do an asset allocation. I use the solver to find the portfolio with the minimum standard deviation for a given level of expected return.

Cardinality constraint: I wish to make the solver choose a maximum of 5 of the 10 possible asset classes when solving for the optimal portfolio and it should be able to choose the 5 from any of the 10 possible asset classes. (it doesn't matter which 5).
Buy-in constraint: I have allowed for short sales, meaning that the weights can take on any values between -infinity to +infinity. When solving the unconstrained portfolio in excel it sometimes results in a very small weight for one of the assets, e.g. +0.1% or -0.1%. I wish to set a minimum invested in a given asset of say +5% or -5%, meaning that the weights are allowed to take on any value between -infinity to -5% or 5% to infinity, or of course be excluded (zero).
Again I don't know for sure it is possible.

I hope the further explanation helped a little, if not, please let me know.

Best Regards.