Excel Tip: Finding optimal solutions when the Excel Solver Add-In won't cut it

I've just got another short post for you this week, that's a follow-up to a post I wrote a few years' back.

Back in 2009, I wrote a post about Excel's Solver Add-In, which enables you to essentially reverse engineer an excel spreadsheet.

It allows you to take an existing spreadsheet, set a target for the result of the spreadsheet, and identify what inputs will achieve that target while satisfying any constraints you specify.

For more details on what the Solver Add-In does, take a look at the earlier post.

While working on a client's spreadsheet last week, I came across one of the Solver Add-In's biggest limitations - and here's how I got around it...

I was working on a spreadsheet to pick the optimum Fantasy Football team, and realised that I could have anything up to 500 players to choose from - meaning that there were 500 variables in the Solver model. It was at this point that I discovered that the Solver Add-In restricts the number of variables to 200!

Fortunately, after a little Googling, I found this:

OpenSolver for Excel
The Open Source Optimization Solver for Excel

This is a free Add-In for Excel, developed and maintained by Andrew Mason and students at the Engineering Science department, University of Auckland, New Zealand.

It works very similarly to the standard Solver Add-In, however, crucially, it does not have this artificial limit of 200 variables.

If you've never used the Solver Add-In, I would recommend that you take a look. If you have, but have a problem that requires over 200 variables, you could do worse than downloading OpenSolver.

Have fun!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".