solver (LP) constraints

hi everyone... i have the following problem.

I have like 10-20 products.. you name it.

i want the solver to actually select the ones that min the cost to run them while be within a budget ($).

However, i also want them to be within a specific age band (ie the average of the ones the excel will select)...This.. i cannot do. don t understand why.
everywhere on the net i see the formula sumproduct() of use...but no reference to other excel formulas like if(), average(), etc...

is it not working because of this fact?


please help me...it will help me in my work

(the constraint to use is in yellow cell, ie i want the yellow cell to be...say within 5-11)

Not sure I understands your problem and your model is set up as a Simplex LP but your problem is not a linear one.

Have set up a slightly modefied model as a "GRG Nonlinear" model.

Alf

the problem is that when i try to incorporate the 'age' constraint within any value (eg >=5 and <=15) the solver displays that there is an error in the formula!
before you post your version by the way, it did not specify that you need to test for linearity. So, many thanks for that!! it solved this issue plus you answered my indirect question that you can use any type of formula before you add it as a constraint. ( i incorrectly thought that if() + other complex combination did the 'damage'..)

Thanks so much.

[now what i need to examine is to research why it is non linear..as u mention]

why it is non linear

In cell I23 you devide the "SUMPRODUCT" result with the "SUM" C7:C21 this is why the model is not linear.

try to incorporate the 'age' constraint within any value (eg >=5 and <=15) the solver displays that there is an error in the formula!

As the range C7 to C21 is set as binary solver tests all possible combination and if all cells (C7 to C21) is 0 then average age is also 0 (my model) so a constraint of 5 or above makes an infisible solution. But it's ok to set a higer limit i.e. 15 or 11.

If you wish for a lover and a higer constraint for age you would have to set it =0 and lover than 11 but a the lower constraint of 0 does not make any sense so just set the upper limit i.e 11.

Alf