Solver with > 100 variables (All)

Topic

New Reply

Hi,
factually this is a question in two parts…
1. does anyone know a workaround which allows you to launch Excell’s solver feature for a problem with > 100 variables?
For example, I should want to use the Solver for determining a maximum (after calculation of a number of variables (among which cost and gain for this parameter), sums, totals, with some restrictions) based on a grid with 5 columns * 100 rows of cells, all having values = 0 / 1.
2. As an alternative, I’m thinking about using VBA to populate all cells with 0/1-values and log the result from those calculations (trying this out for all possible combinations) in search for the maximum I’m looking for. Is it necessary to refresh Excell after having populated the cells BEFORE reading the value from the calculated cell into memory, or is this refreshing done automatically? (I realise this isn’t the most time efficient way, but I don’t immediately see an alternative…)

Reply | Quote

Replies

Have you thought about the implications? 500 cells each of which can be 0 or 1 result in 2^500 ~ 3 * 10^150 possible combinations. Even with a very fast supercomputer you’d need the lifetime of several universes to loop through all of them…

Reply | Quote

Let’s assume that this isn’t a problem
(or is this not a wise assumption…? I assume you can use always use the time limit option, let it ‘roll’ overnight and see what you can get…)

((Honestly, some( wishful )thin(kin)g(?) makes me still hope I can solve this problem with excel and not with bare programming .
In the programming option I mentioned, I could reduce the number of ‘trials’ consistently by validating each possible combination to some restrictions first before I populate the excell cells with is. (E.g. one of the restrictions is that each set of 100 ‘bits’ should contain exactly 30 ‘ones’.) But even then, I assume it would still require considerable time.))

Reply | Quote

Even with imposing extra conditions, the number of possibilities remains huge. Don’t think in terms of days, or months, or even years.

There are advanced methods involving algebraic geometry for solving this kind of problem in less time (relatively speaking), but I doubt that Excel or VBA is the tool of choice for these methods. The applied mathematics department of a university near you may be able to tell you more about this.

But it would be more useful to see if you can rephrase the problem to involve fewer unknowns.

Reply | Quote

OK, Hans,
as the situation I’m dealing with is more kind of a hobby (it’s about finding a ‘most successfull selection’ out of a poole of many candidates), I think I’ll have to try other ways (e.g. using Access in several steps, like e.g. automatically generating a list of all possible combinations by making a query based on twice the same table (or something alike) without using a join, then launch further calculations on each combination, sort the results, etc, or something…) or, more obviously, accept my defeat (…) Thanks for your time!

Reply | Quote

FWIW, about two years we were looking at a real-world problem that we had narrowed down to 24 variables to look at correlation. Then we presented this to some statisticians. They wouldn’t even consider doing this on Excel or even Access. They used dedicated programs for dealing with that many variables (unfortunately, I can’t remember what programs they used).

Reply | Quote

FWIW, about two years we were looking at a real-world problem that we had narrowed down to 24 variables to look at correlation. Then we presented this to some statisticians. They wouldn’t even consider doing this on Excel or even Access. They used dedicated programs for dealing with that many variables (unfortunately, I can’t remember what programs they used).

Reply | Quote

OK, Hans,
as the situation I’m dealing with is more kind of a hobby (it’s about finding a ‘most successfull selection’ out of a poole of many candidates), I think I’ll have to try other ways (e.g. using Access in several steps, like e.g. automatically generating a list of all possible combinations by making a query based on twice the same table (or something alike) without using a join, then launch further calculations on each combination, sort the results, etc, or something…) or, more obviously, accept my defeat (…) Thanks for your time!

Reply | Quote

Even with imposing extra conditions, the number of possibilities remains huge. Don’t think in terms of days, or months, or even years.

There are advanced methods involving algebraic geometry for solving this kind of problem in less time (relatively speaking), but I doubt that Excel or VBA is the tool of choice for these methods. The applied mathematics department of a university near you may be able to tell you more about this.

But it would be more useful to see if you can rephrase the problem to involve fewer unknowns.

Reply | Quote

Let’s assume that this isn’t a problem
(or is this not a wise assumption…? I assume you can use always use the time limit option, let it ‘roll’ overnight and see what you can get…)

((Honestly, some( wishful )thin(kin)g(?) makes me still hope I can solve this problem with excel and not with bare programming .
In the programming option I mentioned, I could reduce the number of ‘trials’ consistently by validating each possible combination to some restrictions first before I populate the excell cells with is. (E.g. one of the restrictions is that each set of 100 ‘bits’ should contain exactly 30 ‘ones’.) But even then, I assume it would still require considerable time.))

Reply | Quote

This is only true if you are using a “brute force” approach to the problem, e.g. checking all possible combinations for the best results. Even very large problems can be solved quickly when you use a more systematic approach.

Ronny

Reply | Quote

I agree completely

Reply | Quote

I agree completely

Reply | Quote

This is only true if you are using a “brute force” approach to the problem, e.g. checking all possible combinations for the best results. Even very large problems can be solved quickly when you use a more systematic approach.

Ronny

Reply | Quote

Have you thought about the implications? 500 cells each of which can be 0 or 1 result in 2^500 ~ 3 * 10^150 possible combinations. Even with a very fast supercomputer you’d need the lifetime of several universes to loop through all of them…

Reply | Quote

I’m a statistican so let me offer some input from that viewpoint. Your brief description sounds like you are trying to find the one best solution, that is, you want to perform linear programming. Since you want to limit the solutions to 0/1, you are really doing a special case of LP called integer programming. There is a bunch of software out there that can handle these types of problems and work them very fast. In fact, in the world of LP, 100 variables is not that big of deal. It would be foolish for you to try and reinvent the wheel using any Office program.

I would suggest that you look into specialized LP software. Personally, I use POM for Windows, not because it great, but because it came free with one of the textbooks I teach from. It being designed for college students, I doubt it could handle 100 variables but there are lots out there that can.

Ronny

Reply | Quote

Thanks! If the more systematic approach fails (lack of creativity?) before my interest in the problem does, maybe I’ll might check out those LP programs :-).

Reply | Quote

Thanks! If the more systematic approach fails (lack of creativity?) before my interest in the problem does, maybe I’ll might check out those LP programs :-).

Reply | Quote

I’m a statistican so let me offer some input from that viewpoint. Your brief description sounds like you are trying to find the one best solution, that is, you want to perform linear programming. Since you want to limit the solutions to 0/1, you are really doing a special case of LP called integer programming. There is a bunch of software out there that can handle these types of problems and work them very fast. In fact, in the world of LP, 100 variables is not that big of deal. It would be foolish for you to try and reinvent the wheel using any Office program.

I would suggest that you look into specialized LP software. Personally, I use POM for Windows, not because it great, but because it came free with one of the textbooks I teach from. It being designed for college students, I doubt it could handle 100 variables but there are lots out there that can.

Ronny

Reply | Quote