Advanced EXCEL topic : overcoming the limitations of EXCEL Solver

[fireant911]

I imagine that not many folks have an interest in this but for those that do - read on...

For those of you that have not used Solver: it is an optimizing tool that allows the user to test thousands, tens of thousands, or even millions of different combinations in order to arrive at an optimal solution – regardless of whether one wants to maximize the utilization of a resource or minimize a cost in order to achieve a certain goal. Granted, the version of Solver that comes packaged in EXCEL may not be the ABSOLUTE optimal solution, it does do a very nice job in achieving this goal (this is NOT a discussion about how optimal the solution from EXCEL is!).

I have used EXCEL Solver for several years and have grown to like this tool… despite it’s limitations. I have often proclaimed that the ‘Solver’ function within EXCEL is the most valuable AND overlooked/ignored/under-appreciated tool within EXCEL. With that said, the issue that I repeatedly ran into, is even though there are a seemingly large number of constraints (limited to 100) as well as decision variables (maximum of 200), there was not enough room to perform some of the other analyses that I had envisioned. There are, however, some add-on packages that available that increase these restrictions but are a bit expensive.

Fast forward to the period of this past Christmas of 2014: I had an idea concerning the scheduling of patients in a doctor’s office that I wanted to experiment with (my wife is a physician). I quickly determined that the available Solver constraints and number of decision variables were too restrictive thus I proceeded to investigate further. First, I was quickly reminded that the Solver developer’s add-on was not an option (as this option was too cost prohibitive $$$!), so I continued looking. After a while, I came across an add-on package that was developed by a New Zealand engineering professor and does NOT have the size limitations as the version in EXCEL – too good to be true???

I sent myself the link (»OpenSolver for Excel) and was going to examine this further upon coming back to Alabama. I did download this tool and experimented with adding some constraints and it initially appeared to have much promise. Shortly thereafter, I used VBA to programmatically add some constraints (adding them manually would take too much time as there were greater than 2K of these) and ran the OpenSolver program with EXCEL. A short time later, I had a feasible solution that passed the logic test (i.e., the provided solution would work). Over the past few days I have refined the constraints which has yielded a logical/workable schedule.

With all of this said, the primary message that I would like to state is to the users of Solver that have encountered size limitations issues … download this OpenSolver tool and experiment!

[BlueSkyJaunte]

Cool.

Optimization is not my specific field (though I took many classes in it during my university days) but I work very closely with a bunch of scheduling and optimization experts. Generally the problems they work on are so big that they almost exclusively use iLOG and CPLEX. Still, every once in a while a "quick and dirty" analysis comes along and invariably someone complains about Excel Solver's limitations. I'll be sure to pass the word along (though I strongly suspect they already know about OpenSolver and just like to b1tch).

[fireant911]

BlueSkyJaunte,
I used to teach this when I was at the university. I fondly remember once, after I had already shown the solution, that one of the students spoke up and said that the answer that was revealed by solver was, surprisingly, the same answer that he had already figured out - in his head... I was expecting his retraction after questioning but he stood firm. Amazingly, this feat of superior mental strength could not be repeated! Granted, the problem I had given the class was somewhat simple there was absolutely no way that he was able to compute this (not that someone could do it but not this particular student).

The problem I had posed to the class was actually a real-world problem that I had dealt with earlier in manufacturing. I used to work as an engineer at a brass foundry and occasionally it became necessary to switch materials in the furnace from one alloy to another (given the existing amount in the furnace at the given percentages of elements from the laboratory, can we go to another alloy without overfilling the furnace, and, if we can, what is recipe to do this while maintaining the various ranges of the elements of the new alloy while concurrently during this at the least expensive cost).

[scottmandue]

Yes, very cool.
As I'm staring down retirement but do not want my brain to dry up (and I would like some kind of job after retirement) I'm starting to brush up on my software skills. I have started on java and HTML but I want to dig into Excel more too.