Finding the Values which nets to 0 using Solver - Help please

Hi alansidman,

First of all thanks for the response I will look into that link you provided and get back.

Meanwhile let me ask you one thing, i have spoken with Solver help desk representative, he mentioned that solver will work for all target values including zero.

but for me its working except zero. please let me know do i need to change any settings in solver?

i.e Engine, values etc

Thanks in advance,

Originally Posted by alansidman

Look at threads 5 and 6 of this link.

http://www.excelforum.com/excel-form...o-a-total.html

Solver is doing exactly what you tell it i.e. SUMPRODUCT(B2:B12,C2:C12) should be 0 and solver does that by setting all elements in range C2:C12 to 0.

To get the kind of solution you wish for you must add constraints telling solver what it can't do.

Try running solver on sheet "Solver_Constraint"

Alf

Hi Alf,

Solver Constraint also doesnt give the exact value. i have tried giving constraints as much as possible but it doesnt seems working.

if you know any other options please let me know, or if you can workout something it would be grateful.

Thanks.

Originally Posted by Alf

Solver is doing exactly what you tell it i.e. SUMPRODUCT(B2:B12,C2:C12) should be 0 and solver does that by setting all elements in range C2:C12 to 0.

To get the kind of solution you wish for you must add constraints telling solver what it can't do.

Try running solver on sheet "Solver_Constraint"

Alf

Solver Constraint also doesnt give the exact value.

????

See pgn Solver_1 and Solver_2

Alf

Thats really Good its working, Please let me know the settings and which engine is used.

And your solver looks different, please see the attached which is including screenshots and results.Solver_sample.xlsx

Originally Posted by Alf

????

See pgn Solver_1 and Solver_2

Alf

You are using Excel 2010 and I'm using the 2007 version of Solver that explains the difference in how the Solver set up looks. In options I've not set model as linear so my model will run GRG Nonlinear as well.

As I to why you get decimal values and not binaries as I do I really can tell. I think I've stumbled over that problem before. Will check to my other solver postings and see if I can find that particular posting and report back in the meantime you could test setting range C2:C12 to both binary and integer. Not sure it will help but may be worth a try.

Alf

Hi Alf,

Yes, this works now..

Thanks a lot.

Originally Posted by Alf

Hah! Found it " in the Solver setup dialog, click the Options button and look for a checkbox that says Ignore Integer Constraints on the All Methods tab." and remove check mark if found.

From the "FrontlineSolver" info



Alf

Glad to be of help and thanks for feed back.

Alf