Search Not Just Numbers

Tuesday, 18 March 2014

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!

Excel Expert Course

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".


  1. I find many useful things herewith. It is really amazing for us. Where ever we are, just click on the website and Take advantages .Your article impressed me very heartily. From a last decade I was searching such type of web site. Thanks for giving me a space in your blog. I refer this to my close friends who are already seeking for the same .thanks again guys…
    Excel Expert

  2. He was the world biggest arms manufacturer replica handbags, but after a literal change of heart, Tony Stark It seems like he gets to play with the Iron Man armor all by himself, which is a louis vuitton outlet shame, but imagine how much cool stuff he must come up with in his spare time replica watches uk that he hands off to the R department. The beginning of Iron Man 2 also shows he knows how to throw rolex replica one heck of a company party.

  3. Perhaps the most common thought could be the reality if you are dressed up in excellent along with expert attire eventually you are likely to sense a lot more expert which boosts productiveness. replica hublot It really is crucial in which people should be able to feel comfortable regarding just what they are going to have got about since this kind of eventually exhibits independently outlook on life about living. replica prada These can be a important part of modern society in which people should be able to like a trend advancement, which acts often to generate the particular people with each other. hublot replica You will find diverse brands of garments which bring in along with press apart picked consumers. chanel replica Choosing the right attire utilize is usually a big problem relating to every person. cartier replica sale When you've got about clothes that's not considered to be 'classy a, this could substantially effect the particular outlook on life inside the unfavourable approach given that think that modern society will be with an individual in different ways.

  4. There are now several free options for Google Sheets, and Excel Online provided by the same people who build the Excel Solver( They also sell upgrades that go well beyond simple Optimization problems.