Excel Solver: adding ratio as a constraint

hi,
Any help would be appreciated...

What's the method to add a ratio constraint in Excel Solver when there are more than 2 variables?

If I have 3 variables for a mixture to be made... Foo, bar, jar and looking to find the (min) optimized solution mix in Solver for each ingredient, with the following constraints:
1) the total of all 3 ingredients <=1000 kg
2) Foo>=300 kg; Rs 10/kg
3) Bar>=200 kg; Rs 50/kg
4) Jar>=100 kg; Rs 60/kg
5) (and this is my problem) but the ratio of Jar to the rest of the ingredients is 1:4
6) Obj Function: min total cost

The part where I'm getting stuck is figuring out how to set up the constraint: "ratio of Jar must be in a 1:4 ratio in the optimized mix in Solver".

Help?

Something like this perhaps?

Alf

@Alf: Thanks for the sample... I took a look a the solution and doesn't seem to be right -- Solver looks like its simply taking the 300 and 200 quantity and dropping it in along with the computed 125 figure. Also should not the 125 JAR output (D5) be 25% of the total 625? in D6 (which it is not)

@MrShorty: Some more information after reading your post:
Yes the decision variables are the amount of each ingredient.

I'm not sure what the ratio part means, that's where I'm stumped The problem verbatim is this: "The mixture must contain at least 200 Kg of Bar, 300 Kg of Foo, 100 kg of Jar, and the ratio of Jar to the other two ingredients cannot exceed 1 to 4". "What is the amount of each ingredient needed to be put into the mixture that will meet all the requirements of the mix and minimize total cost."

I've added a workbook for both your kind consideration.

Thanks for the help.

Solver looks like its simply taking the 300 and 200 quantity

Let me see if I can add a bit more to Mr Shorty's explanation of you problem.

The "driving" force in solver is the "Object function". This is what you wish to achieve.

In this case your "objective" is minimum cost so solver will use as little as it can of each components, the more a component costs the more solver will strive to minimise the use of the more expensive components.

So solver must find the minimum cost for the mix but according to certain rules i.e. the constraint.

And one constraint was that Foo should be equal or greater then 300, another one was that Bar should be equal or greater than 200 so of course solver have to use the minimum quantities of these two components and ad this to the smallest quantity of jar (objective minimum cost) to make the ratio

of Jar to the rest of the ingredients is 1:4

Alf

It was never my "objective function" to get you to do my homework. Thank you for NOT doing that for me.

The reason for sending the workbook was just to give you a clearer understanding of the problem. My only issue was trying to figure out how to enter and manage the ratio constraints, and your last posts gave me the information needed, and so I was able to figure out the problem with all constraints satisfied. THANK YOU BOTH!!