faq68-4742 Which of these numbers add up to my target number??

[henio]

Hi,

this faq looks interesting - I've never tried to use Solver before.

So, I've followed the instructions as given and get the error message Binary constraint Cell Reference must include only Adjustable Cells.

As it is unlikely that KenWright's faq is wrong, does anyone have any ideas about what I have done wrong?

Cheers,

Henio

 

[HansGermany]

Hello,
may I help you with a competent adress, where you can find a good answer.

Look at

http://www.spotlight.de

This is my favorite for office-solutions.

Bye!
Hans Porzel
from Germany

 

[KenWright]

I usually assume that EVERYTHING I have done is wrong, until I have checked it again

I'll walk through it step by step and see if I can spot it.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

OK, the only way I can reproduce that initially, is if I incorrectly include cell B31 in the constraints range when i select it, ie

After having put in your numbers to A1:A30, and having put 0s in B1:B30, and having put the formula in B31, and having told solver to find a value by changing cells B1:B30, if I then add the constraint B1:B31 (<<Note the 31) is 'bin', I then get the error. That range must NOT include the cell with the formula in.

If this doesn't fix your problem then by all means mail me a sample workbook you are trying to do this in, or I can mail you one, but I have just followed it step by step and it seems to work OK in XL2000/2002/2003.

I will try and expand the faq slightly though, as I guess the adding the binary constraint piece could be clearer for someone that hasn't used it before. Let me know how you get on though.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

And just for the record:-

HansGermany - Not quite sure why you felt the need to point the OP to another site in a different language. This is an extremely competent site for Office-Solutions, with a huge userbase, and I'm going to assume that it is the language difference that translated your message incorrectly.

Whilst I would never take umbridge with people giving people links to good resources, and often do myself, I would have considered it courteous to at least let people here try and help the OP before attempting to send him off somewhere else for a solution.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

LOL - I HAD missed something out, and that was the 'selecting which cells to change' bit. I have updated the FAQ and you should now be able to get it to work.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[henio]

Hi Ken,

actually I found out the hard way and should have posted the fact!

However, I found a helpful tutorial at

http://www.solver.com/stepbystep.htm

which walks you through an interesting scenario and explains what you can do with the tool, which is something the 'help' facility doesn't really do.

Regards,
Henio

 

[KenWright]

Cheers Henio - I'll add the link to the FAQ

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------