Solver Problem

[SpicyP]

Hi everyone,

I'm using the Excel solver function in order a minimize a calculated risk field by changing 8 variables.

I've set the constraints so that the 8 variables are integers and greater than -500 but less than +500 and the solver works fine and seems to find the optimal solution. However, I'm trying to go a step further now and I'm looking to add in a constraint (for practical purposes) that the solver can only change 3 of the 8 variables. I've tried using IF formulas (e.g. counting number of 0 cells) and setting a constraint that it must equal 5 and other similar workarounds but to no avail.

Any ideas or thoughts would be much appreciated

Thanks in advance,
SpicyP

[MrShorty]

Perhaps I am not reading between the lines very well, but my first thought is to select only the 3 cells you want to change in the " by changing cells" box. How do you really want to use this "8 by changing cells but really only 3 by changing cells" scenario?

[SpicyP]

The thing is that I don't know which 3 cells are the most important to minimize the risk field.

I'm trying to solve for the most efficient way (e.g. max 3 trades) to minimize the risk subject to the integer and +/- constraints.

[SpicyP]

The thing is that I don't know which 3 cells are the most important to minimize the risk field.

I'm trying to solve for the most efficient way (e.g. max 3 trades) to minimize the risk subject to the integer and +/- constraints.

[MrShorty]

I think I understand a little better. I can't think of any good way off the top of my head to help Solver do this.

In the work I do, I usually analyze for "the 3 most important cells that impact the OF" outside of Solver. Partial derivatives or some other analysis to tell me how each decision variable impacts the solution, then choose the 3 most important based on that analysis. This usually requires some detailed knowledge of the OF. Since we are talking in rather general terms, it is difficult to talk about specifics for this kind of analysis.

I will sometimes use something like "scenarios" to tease out the impact of individual decision variables. I have not used it much, but Excel has a built in scenario tool https://support.microsoft.com/en-us/...2-19ec479f55a2 which you can access from Solver (see the end of https://www.dummies.com/software/mic...-as-scenarios/ ), but this seems inclined to end up being "solve each variation of 3 decision variables, then choose the best." It's kind of brute force which makes it tedious, but should work.

Can you share any details about your objective function? Maybe upload a sample file? Working out a specific solution for this is going to require some specific information about your objective function and how each decision variable figures into it.

[SpicyP]

Thanks MrShorty.

What you described below is what I currently do, it can be tedious and while it can provide a solution that could be close to optimal, I'm wondering if I can find 'the' optimal solution using Excel Solver.

I've attached a sample spreadsheet (comments below):
- Try various scenarios on rows (10, 11:16) across columns C:J on the 'Position' tab
- Columns Q:X, take the current position and add the scenario
- The risk figures are calculated on the 'Data' tab by multiplying each position by the risk moves on that week and getting the standard deviation over the past ~5yrs

Appreciate any help.

Thanks,
SpicyP

[Hydraulics]

I'm wondering if I can find 'the' optimal solution using Excel Solver.

I doubt it, but here is a different setup you may want to try.

There are 8 binary variables that will switch scenarios on or off, and their sum is set to 3. The real variables are now in row 8, and the integer constraint on them have been relaxed. If, at the end of calculations, some of the variables are fractional, you can easily round them to the nearest integer. This puts much less strain on Solver engine, while still providing a reasonable solution.

You may want to play with Multistart option or, at least, use different starting points. The result on the worksheet has been found setting all continuous variables to 500 and binary variables to 1.

HTH,

Francesco

[SpicyP]

This is great! Never thought of using the binary approach and appreciate the advice re: integers (I had assumed the opposite e.g. using whole numbers would actually increase performance :P)

Thanks so much Francesco

[Hydraulics]

Glad I could help.

If this has taken care of your problem, you may mark the thread as SOLVED, and maybe thank the users who helped you by adding some reputation.

Francesco