Excel Solver Issue

[Zacross]

Hello to everyone,

I'm a first-yeat student in Business&Economics. My "homework" for this week-end is to solve problems that can be linearly written using the Add-in Solver of Excel
However, although I am pretty sure my model is fine, Solver detects an error in the objective or contraints cells.
It is quite hard to explain how I build the model without showing the problem...
The only thing I can say is that when I did all the constraints, I have a "Div/0" issue and I have no idea how to fix it...

What can I do? Thanks in advance

edit : As I see, I can attach a file =) It will give you a better overview of what I did exactly, and maybe allow you to help me out...
Is my model wrong? Or is there a subtility I don't know about to make that work?
I'll summarize quickly the actual problem in case the model I made make no sense to you without its context

I must "build" a special chemical product with 3 Compound at the lowest cost.
Compound 1 : 5$ - 20% sulfur - 60% Iron - 20% Potassium
Compound 2: 5.25$ - 40% sulfur - 30% Iron - 30% Potassium
Compound3 : 5.5$ - 10% sulfur - 40% iron - 50% Potassium

The final products has to contrain at least 20% sulfur, 30% iron and 30% potassium, but having less than 45% potassium

[Alf]

There is nothing wrong with your setup of solver. The "Div/0" error is cause by the fact that before you run solver G15 = 0. After running solver everything is ok.

Still if this bothers you I've set up an alternative solver model where I avoid this problem.

Alf

[Zacross]

Alf, thanks a lot for your response.

I would like to precise it is only the second time I use Solver (and starting to use the very basic functions of excel for a couple of months). So the mistake is probably stupid, but I'm really new in it =/

I think you didn't understand my problem thought.
I know where the "Div/0" come from. I'm well aware G15 = 0. However, this is supposed to be changed by solver, since G15 is determined by the Variable cells! (D3;E3;F3)
However, when I launch solver (after having setup the objective, variable and constraints cell), it just reports the error and find no solution at all (saying that "one of the cells in the worksheet became an error when Solver tried certain values for the variable cells)

edit: I really thank you a lot for the alternative model you made for me, but I would like to understand, and be able to do the model by myself, so I would really appreciate if you would take a bit more of your time to explain me exactly what is wrong in my model and how I can improve it

[Alf]

Did you try the model I set up?

Alf

[Zacross]

Seems to work =)

But i'm not sure how you manage to do that... You get the % weight by compound, which allows you to have the total sulfure/iron/potassium as a Sumproduct function (which prevents you from dividing each by the sum of the compound, leading to the div/0 error)

And it still doesn't tell me why my model refuses to work =(

edit : I'm analyzing you alternative model... It is really slighty different from mine... I mean in itself it makes the differences huge since your model works and mine doesn't, but you do the exact same thing I did, just using another "way" to write it...
Is solver that sensitive to the model? Besides you tell me there was nothing wrong with my setup, which makes me a bit curious to know why it isn't working...

edit2 : Did you get % weight just by setting that the sum of the variable cells had to be equal to 1 in your contraints?

[Alf]

Is solver that sensitive to the model? Besides you tell me there was nothing wrong with my setup, which makes me a bit curious to know why it isn't working...

I started by testing your model and I had no problem running it. As I use Solver (Excel 2007) and you use Solver (Excel 2010) this must be the cause of the problem. See MathSolver1.png and MathSolver2.png

In Excel 2010 there are 3 different Solver models. I think you should try "Engine1" i.e. "Nonlinear GRG Solver"

Perhaps you can upload some pictures of you Solver settings?

Did you get % weight just by setting that the sum of the variable cells had to be equal to 1 in your contraints?

Yes that's excatly what I did.

Alf

[Zacross]

mathsolver.png

Here you have the picture.
As I compare to your setup, it is exactly the same... I tried as you said to change to "Nonlinear solver", Solver says it encounters an answer, but every value stays at 0...

edit : Gosh this **** is driving me crazy... I used your alternative model, just changing the formulas you used in the cells in order to make them fit to the formula I used in my model, solver has a problem again... I really have no idea why it would be just fine on excel 2007 and bug on 2010...

[Alf]

You could try to change D3 to 1 (just to get rid of "Div/0" error) and see if Solver runs ok.

You could also try to set (in Solver) $G$15 = $H$15.

My last idea (a long shot but you never know) 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.

Then there is the possibility to contact the makers of solver http://www.solver.com/ and see if they could tell you what the problem is.

Alf

[Zacross]

Actually I already tried the to set D3 = 1, solver has the exam same error (which is a bit weird)

I'll try the other methods =)

Thanks a lot for your help anyway

[Alf]

Ok hope it works.

Just out of curiosity could you perhaps upload a picture with the error message?

Alf

[Zacross]

So...

I didn't find any checkmark where you told me to check!
I tried to set G15 = H15, didn't work out
Setting D3 to 1 didn't work out either.
Finally, I uploaded the error message......

It really annoys me... Somehow it means that the models I create cannot be solve by this god damn program... I'll have to twist my mind to think differently and find other way to model the problems...

[Alf]

Strange! Let's hope forum guru shg a real Solver wizard sees this thread and take a look at this problem.

At the moment I'm out of ideas of what else to suggest.

Alf

[Alf]

Got it!

Change formula in range G12 to

Please Login or Register  to view this content.

and make the same adjustment to G13 and G14

Don't know if you use ";" or "," in formulas in Belgium so change if needed. Select "GRG Nonlinear" and run Solver.

As this seems to solve your problem don't forget to mark your thread solved and if you like to give a rating to my answer click on the small star (bottom left) in my post.

Alf

Ps

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

[Alf]

Difference between your model and mine is your formulas in G12, G13 and G14. Since you divide result by total volume (G15) your model is not linear anymore, mine is so I can use the "Simplex LP". Your setup require the "GRG Nonlinear" solver

The "hub" of the matter is the potassium constraint i.e. the min value of 0,3. None of the other contents sulphur and iron are constraining so by setting the min value of potassium to 0,2 one would expect Solver only to choose 100 % of compound 1 as this is the cheapest component and the objective function is set to min.

Using both the "Simplex LP" and the "GRG Nonlinear" solver you get the expected result i.e. both solutions chooses 600 lb of compound 1 so both models give the same answer. And if the min potassium level is 0,3 both solver models gives the same answer. So whatever method you chose this should not matter to the result.

Regarding you initial problems with your model I found this bit of information on the solver site:

Basic Solver - Solver encountered an error value in the objective cell or a constraint cell (9).
This message appears when Solver recalculates your worksheet using a new set of values for the decision variable cells, and discovers an error value such as #VALUE!, #NUM!, #DIV/0! or #NAME? in the objective cell or one of the constraint cells.

So it seems that the 2010 solver is not so "robust" as the 2007 or 2003 versions.

Alf