Excel solver binary constraint help

June 19, 2008 11:53 AM

Why is Excel Solver not using the binary constraints I've set?

I've created a table which calculates an answer based on a set of binary flags (0,1) --- basically a sumproduct type function. I'm trying to minimize the answer. But when I run solver (setting a binary constraint on those cells), it doesn't actually use binary numbers and instead is using a whole bunch of fractions.

posted by mtstover to computers & internet (12 comments total)

I assume you've double-checked the binary constraint is applied to the correct cells?
posted by Mike1024 at 12:17 PM on June 19, 2008

yep
posted by mtstover at 1:12 PM on June 19, 2008

Have you tried the same setup with TRUE / FALSE instead of 0 / 1 ?
posted by Andrew Brinton at 2:42 PM on June 19, 2008

How many binary flags are there in total?

Is there any way you can post the spreadsheet for us to look at?

(as you can tell, I'm rather at a loss as to what the problem could be)
posted by Mike1024 at 3:21 PM on June 19, 2008

Best guesses...

(1) Might be the settings in your Solver options. Start by making sure all the options are set to the defaults. If that doesn't work, experiment. E.g., Assume Linear Model? Search: Newton vs. Conjugate? Etc. Apparently logistic regressions are what you need for binary data (see item #3 in that article). I think Solver is supposed to figure out what type of regression you need, but maybe one of the settings is causing a problem.

(2) Might be a bug. I know this is a terrible answer, but when I took a mathematical-modeling-for-business class in grad school the professor told us lots of colorful stories about bugs in Excel. Solver is something Microsoft licenses from a 3rd party, so it can be extra-twitchy.

If all else fails, you could try your formula on another version of Excel. If you like, private-message me for my email and I can give it a try on Excel 2007 (for Vista).
posted by CruiseSavvy at 3:27 PM on June 19, 2008

Suggestions for place to post, and I'll drop it right up there?
posted by mtstover at 3:28 PM on June 19, 2008

Suggestions for place to post

There's always places like http://rapidshare.com/ - you don't get much control, but it sure is simple.

If you'd rather not put your spreadsheet online I can mefi mail you my e-mail address.
posted by Mike1024 at 3:45 PM on June 19, 2008

Here you go!

http://rapidshare.com/files/123676023/sitemodel.xls.html
posted by mtstover at 3:50 PM on June 19, 2008

Well, I'm not sure what the trouble with the solver is - but solving nonlinear, nondifferentiable problems like yours is pretty hard!

However, I've made a few modifications to your spreadsheet and come up with a few results - 299 is the best I've got so far. Are you familiar with macros?
posted by Mike1024 at 4:51 PM on June 19, 2008

You can get my modified version of your spreadsheet here:

http://michaelt.uwcs.co.uk/excelsolve/

in sitemodel2.xls if you run macro1 then wait it will randomly try combinations not stopping until it finds a good solution which meets your criteria (definition of 'good enough to stop' you can set by editing the macro). Then capture the result with a printscreen!

You can also see some of my best solutions in that directory too.

If you aren't too happy running an internet stranger's macro (and why would you be?) I can run my solver for longer tomorrow - but for now it's 1am so I'm off to bed!
posted by Mike1024 at 4:59 PM on June 19, 2008 [1 favorite has favorites]

Mike1024 -- you are so cool! How great is the green that you can have a stranger in the UK (why aren't you asleep?) futz with your spreadsheet and give it back to you. Good karma all around for you Mike!!!!

I've got it down to 221 so far by reducing the target number in the macro. Nice and elegant - good work. Thanks so much.
posted by mtstover at 8:07 PM on June 19, 2008

I've posted some modifications to that - sitemodel3.xls lets you set a time limit and sitemodel4.xls locks in some elements of a good solution. It also shows one of my best solutions, which has a distance of 167.
posted by Mike1024 at 12:09 PM on June 20, 2008