solver causes laptop screen to flicker

I use Solver on a workbook which has about 300,000 cells. Within a minute or so of starting it, Solver causes my laptop screen to flicker, and the laptop itself thus becomes useless. I can't even stop solver with the esc key. (typically I'd set Solver to run for 20 minutes or so as there are about 8^15 possible iterations).

I assume part of this has to do with memory (I have 8 gigs RAM) or processor (I5).

When I run Solver I do not check the "show iteration results" box.

From what I've read in other posts and forums it seems that one solution might have something to do with a VBA code telling Excel not to refresh the screen. Another post mentioned conditional formatting.

Any ideas/suggestions? I'm wondering if I cut down the # of cores that Excel uses (under advanced options). Or perhaps tweaking my graphics settings.

Thanks

one solution might have something to do with a VBA code telling Excel not to refresh the screen.

Yes that can work but then you must run/start solver with a macro as well as set a reference to solver in VBA (VBA window -> Tools -> Reference and tick box for solver)

The command you use is

Please Login or Register  to view this content.

This may stop flickering but as to the laptop get locked up this may be caused by solver using most of the processor power and memory and upgrading cpu and increasing memory may fix that. If you have access to more powerful PC perhaps you could test you model on that and see if this works better?

As you speak about of 300,000 thousand cells you are either using an advanced solver from Frontsys or running solver in a loop.

If your problem is linear you could try the freebie "OpenSolver" to see if that works better.

Alf

Thanks Alf. I'm using the Evolutionary method in Solver so I suspect that means I'm running it in a loop?

I actually tested out the theory you mentioned that I needed more powerfuul CPU. I rented a VPS gaming computer which uses the best I-7 processor, and installed office on it, and ran Solver. The screen didn't flicker.

I'll try the macro. In the meantime though I've installed more RAM in my laptop (doubling it to 16 gigs) then also tweaked Excels Advance Options to only use 2 of the 4 available processor cores. Those two changes stopped the flickering.

I appreciate your help!

You are welcome

Thanks for feedback and rep

I'm using the Evolutionary method in Solver so I suspect that means I'm running it in a loop?

Well not necessarily. Solver has 3 different “engines” to solve problems.

If the problem is a linear one the best method to solve problems like that is using the “Simplex LP”

If the problem is a curved smooth line then the “GRG Nonlinear” engine can solve the problem

And finally if the curve is non smooth then the “Evolutionary” engine may solve your problem. So in order to get the best solution one should try to use the appropriate solver engine for the kind of problem you wish to solve.

One can always start using the "Simplex LP" and if the problem is not linear solver will tell you so. After that test "GRG Nonlinear" and if this don't work then time to test the "Evolutionary"

All these engines can be run from the data tab without any VBA. If you do have a problem where you wish to check a number of lines/ranges then you can use a macro.

Please Login or Register  to view this content.

In the code snippet above solver shall check 98 rows by finding the Z2 max value by changing cells in range N2:O2 so r goes from 2 to 100 and in the second loop r = 3
so Z3 is to be maxed by changing range N3:O3 and so forth.

Sorry I got carried away

Alf

Thank you Alf. It has been a while since I tested Simplex and Linear but I think I remember neither of them working for me, so I resorted to Evolutionary. The problems I'm solving for involve such a labyrinth of calculations that I think Evolutionary is the only way to go. But I'm intrigued by VBA and want to learn it so your post is very helpful. Thanks again!