# how to force solver to use only a certain number of variables

#### airone12

##### New Member
hi there,
i'm trying to use excel solver to optimize a portfolio.
the problem is that i am not able to model the problem correctly: it maximizes the expected return of the portfolio using all the variables, but i would like to use not all the 234 stocks i listed but 10 at max.
any idea how to force him to produce solutions that use only a subset of the variables available?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Eric W

##### MrExcel MVP
Welcome to the forum.

This depends on how you have the sheet set up. It sounds like you have 234 variables set up, and all 234 are entered in the solver as variables. And you're looking for solutions that require a maximum of 10 changes to those variables. If so, you might try duplicating those 234 cells somewhere else, then adding a formula that counts how many changed, something like =SUMPRODUCT(--(A1:A234<>B1:B234)) then adding another constraint in the Solver setup requiring that cell to be <10.

I can't swear that this will work, but I think it's worth a shot. Hope this helps.

Last edited:

#### airone12

##### New Member
actually i tried something like that, but it does not work: the solver stops itself at a suboptimal solution which also breaks the constraint... i think that this kind of constraint is so not-linear that in an already not linear contest breakes the solver capacity to find a local maximum/minimum... i hope i was able to explain the mess in my head

#### Eric W

##### MrExcel MVP
Hmm. After writing the first paragraph, I actually realized that what you said might happen. Which is why I went back and edited the post, adding that line that temporized a bit.

It actually seems that you might need some multi-variate analysis, and selectively remove the variables with the smallest impact. Maybe you could run the analysis with all 234 variables (I don't know how long that would take), then identify the 10 variables with the largest change, then rerun it letting only those vary.

Just throwing out some ideas, if any light bulbs go off, I'll let you know.

#### airone12

##### New Member
Hmm. After writing the first paragraph, I actually realized that what you said might happen. Which is why I went back and edited the post, adding that line that temporized a bit.

It actually seems that you might need some multi-variate analysis, and selectively remove the variables with the smallest impact. Maybe you could run the analysis with all 234 variables (I don't know how long that would take), then identify the 10 variables with the largest change, then rerun it letting only those vary.

Just throwing out some ideas, if any light bulbs go off, I'll let you know.

ok, i'll think about it, i'll let you know if something works.
thanks again

Replies
0
Views
386
Replies
0
Views
209
Replies
0
Views
603
Replies
2
Views
2K
Replies
4
Views
1K

1,190,870
Messages
5,983,321
Members
439,839
Latest member
iblackie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back