Wednesday, October 28, 2009

Excel: too many adjustable cells

> My Excel Solver model gives this error message:


Yes, this means Solver has hit the built-in limit of 200 variables. See: You can upgrade to a solver from Frontline, or if you want a more structured Excel based approach consider something like Microsoft Solver Foundation. Also Cplex 12 has an Excel interface much like Solver. All the other modeling languages also have Excel interoperability.

Apparently, there are quite a few models with fewer than 200 variables, see

Note that for some models the 200 variable limit is less restrictive than it seems as intermediate variables do not have to be included in the adjustable cells.

For larger models I am a big proponent of moving to a modeling language. I have converted a number of models written in Excel to GAMS and AMPL and actually in virtually all of them I had problems in reproducing the results: all these Excel spreadsheets contained bugs. It is just too difficult to do error-free comprehensive modeling using the Excel formula paradigm. I’d suggest to use Excel where it is really good: as data editor and for reporting. For the stuff in between: the actual modeling, nothing beats a specialized modeling language.