Solver constraint/Formula

[Guest]

Pretty straight forward solver problem, supply/demand constraints, ie Pounds Made <= Demand. What I also want to add as a constraint is that Pounds Made >= 10%ofDemand. Message to this formula
Cell Ref: $Demand$Cells >= Constraint: =0.1*$D$9:$I$

"Constraint must be number, simple reference or a formula with a numeric value
Looks like a formula to me,
Tried: Constraint: =0.1*($D$9:$I$9
Tried: Constraint: =(0.1*$D$9:$I$9
On line help say I can enter a formula, using Excel 2003. Found no help with supplied disks and on line help gaveno examples of using a formul

 

[Vasant Nanavati]

I may be wrong, but I don't think an array formula is acceptable. Or perhaps
you mean:

=0.1*SUM($D$9:$I$9)

--

Vasant

Pretty straight forward solver problem, supply/demand constraints, ie

Pounds Made <= Demand. What I also want to add as a constraint is that
Pounds Made >= 10%ofDemand. Message to this formula:

Cell Ref: $Demand$Cells >= Constraint: =0.1*$D$9:$I$9

"Constraint must be number, simple reference or a formula with a numeric value"
Looks like a formula to me,
Tried: Constraint: =0.1*($D$9:$I$9)
Tried: Constraint: =(0.1*$D$9:$I$9)
On line help say I can enter a formula, using Excel 2003. Found no help

with supplied disks and on line help gaveno examples of using a formula

 

[Guest]

Hi Vasant
Aprreciate the response. I tried it and it sort of worked, but not quite. I get a solution, but it is not the optimal solution. I figured out a way to solve it and have an answer, which I believe is optimal. Trying to optimize profit, like so many of these problems. Waht I did was figure out 10% of demand on each and used: D2:I2 >= D10:I10, Row "2" is demand adn row "10" is a calculated 10% of demand. It works, but still can't figure what formula will optimize
Thanks
Davi

----- Vasant Nanavati wrote: ----

I may be wrong, but I don't think an array formula is acceptable. Or perhap
you mean

=0.1*SUM($D$9:$I$9

--

Vasan

Pretty straight forward solver problem, supply/demand constraints, i

Pounds Made <= Demand. What I also want to add as a constraint is tha
Pounds Made >= 10%ofDemand. Message to this formula

Cell Ref: $Demand$Cells >= Constraint: =0.1*$D$9:$I$ value
Looks like a formula to me
Tried: Constraint: =0.1*($D$9:$I$9
Tried: Constraint: =(0.1*$D$9:$I$9
On line help say I can enter a formula, using Excel 2003. Found no hel

with supplied disks and on line help gaveno examples of using a formul

 

[Dana DeLouis]

I get a solution, but it is not the optimal solution.

I can't follow your setup, but just an idea. Each of your 6 cells in D2:I2
must be greater than D10:I10 (or something similar...I can't follow). You
are forcing each to have at least a value. Your "Optimal" solution may
require some of these values to be zero, or somewhere in-between 0 and 10%.
That may explain some difference from optimum. Sounds like you need a "Sum"
in there like Vasant mentioned.
From your original post, it sounds like you wish to make something less that
demand. Is this correct? Usually, one tries to make >= Demand. Sounds
like you want to make something similar to the following...
0.1 Demand <= Made <= Demand.

My guess would have been something like:
Demand <= Made <= 1.1 Demand

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Hi Vasant,
Aprreciate the response. I tried it and it sort of worked, but not quite.

I get a solution, but it is not the optimal solution. I figured out a way to
solve it and have an answer, which I believe is optimal. Trying to optimize
profit, like so many of these problems. Waht I did was figure out 10% of
demand on each and used: D2:I2 >= D10:I10, Row "2" is demand adn row "10"
is a calculated 10% of demand. It works, but still can't figure what formula
will optimize.

 

[Guest]

Pounds made 130 130 130 650 130 130 <---Changing cells
Product 1 2 3 4 5 6
Labor 6 5 4 3 2.5 1.5
Raw Material 3.2 2.6 1.5 0.8 0.7 0.3
Unit price $12.50 $11.00 $9.00 $7.00 $6.00 $3.00
Variable cost $6.50 $5.70 $3.60 $2.80 $2.20 $1.20
Demand 960 928 1041 977 1084 1055
Unit profit cont. $6.00 $5.30 $5.40 $4.20 $3.80 $1.80
10% of Demand 96 92.8 104.1 97.7 108.4 105.5 <----Added constraint,
10% of demand
Profit $5,632.52 <---Optimize profit
Available
Labor Used 4422. <= 4500 <- Constraint on labor
Raw Material Used 1600 <= 1600 <- Constraint on material

(Above is the lay out of the worksheet, the 10% of demand is a line added to make the constraint of having "pounds made" at least 10% of demand. Above is not optimal solution. This is optimal Pounds made 96 92.8 104.1 97.7 1036.9 105.5 with a profit of $6,170.44. What I would like to bea able to do is create a formula in the constraints area, but I am unable to do that.)

 

[Dana DeLouis]

Your model worked fine for me. For the changing cells (Made), I added the
constraint that they are integers. Solver had an integer solution of:
96 93 105 100 1032 106

The only other constraints were:
Demand >= DemandTenPercent
Labor <= 4500
RawMaterial <= 1600.

Profit was 6,168.3
Labor Used 4,500
Raw Matherial 1540.7

I don't think you need to add a constraint on a upper limit of what you make
because the Labor and Raw material will take care of that.

I am not sure what you are using, but for your Labor Constraint, make sure
you use something similar to this. Same for Raw Material.

=SUMPRODUCT(Made, Labor) <= 4500

HTH.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Pounds made 130 130 130 650 130 130 <---Changing cells
Product 1 2 3 4 5 6
Labor 6 5 4 3 2.5 1.5
Raw Material 3.2 2.6 1.5 0.8 0.7 0.3
Unit price $12.50 $11.00 $9.00 $7.00 $6.00 $3.00
Variable cost $6.50 $5.70 $3.60 $2.80 $2.20 $1.20
Demand 960 928 1041 977 1084 1055
Unit profit cont. $6.00 $5.30 $5.40 $4.20 $3.80 $1.80
10% of Demand 96 92.8 104.1 97.7 108.4 105.5 <----Added constraint,
10% of demand
Profit $5,632.52 <---Optimize profit
Available
Labor Used 4422. <= 4500 <- Constraint on labor
Raw Material Used 1600 <= 1600 <- Constraint on material

(Above is the lay out of the worksheet, the 10% of demand is a line added

to make the constraint of having "pounds made" at least 10% of demand. Above
is not optimal solution. This is optimal Pounds made 96 92.8 104.1 97.7
1036.9 105.5 with a profit of $6,170.44. What I would like to bea able to
do is create a formula in the constraints area, but I am unable to do that.)

 

[Tushar Mehta]

I don't think you need to add a constraint on a upper limit of what you make
because the Labor and Raw material will take care of that.

Maybe, I am missing something, but just because a constraint is not
binding is no reason to believe it is unneeded.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Business solutions leveraging technology
Microsoft Most Valuable Professional (MVP) 2000-2004

 

[Tushar Mehta]

You can specify a formula in the right hand side of the constraint. In
your example, it is possible to individual-Made >= 0.1*individual-
Demand, i.e., one cell at a time. So, =$F$1>=0.1*Sheet1!$F$7 is OK.
But, one cannot specify an array formula of all-cells-in-Made >= all-
cells-of-Fractional-Demand. For that one must use a range as you did,
i.e., =$B$1:$G$1>=Sheet1!$B$9:$G$9

Personally, I minimize the use of hardcoded values within Solver. All
that the use of hardcoded values does is to make the optimization model
and the worksheet that much more difficult to understand. And, the
added benefit of not hardcoding numbers is the ability to use array
formulas!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Business solutions leveraging technology
Microsoft Most Valuable Professional (MVP) 2000-2004

 

[Guest]

Thank you Tushar. I was looking for a formula to use and did not see a way to use an array formula, which you confirmed. Each constraint must be added for individual demand
Thanks Again
Davi
----- Tushar Mehta wrote: ----

You can specify a formula in the right hand side of the constraint. In
your example, it is possible to individual-Made >= 0.1*individual
Demand, i.e., one cell at a time. So, =$F$1>=0.1*Sheet1!$F$7 is OK.
But, one cannot specify an array formula of all-cells-in-Made >= all
cells-of-Fractional-Demand. For that one must use a range as you did,
i.e., =$B$1:$G$1>=Sheet1!$B$9:$G$

Personally, I minimize the use of hardcoded values within Solver. All
that the use of hardcoded values does is to make the optimization model
and the worksheet that much more difficult to understand. And, the
added benefit of not hardcoding numbers is the ability to use array
formulas

--
Regards

Tushar Meht
www.tushar-mehta.co
Business solutions leveraging technolog
Microsoft Most Valuable Professional (MVP) 2000-200

 

[Dana DeLouis]

Thanks Tushar. You're correct on the Upper bounds of the constraint. One
should include an upper bound on production if necessary. I also made a
typo. I used the following, and it appears to work ok.

Made >= DemandTenPercent
Labor <= 4500
RawMaterial <= 1600.

With the added constraint of
Made <= Demand

Just for discussion, when you mention

You can specify a formula in the right hand side of the constraint. In
your example, it is possible to individual-Made >= 0.1*individual-
Demand...

Were you referring to a constraints directly into Solver?
Excel's Solver I believe does not like Formulas on the right hand side,
although it mostly works. This would be an added benefit of putting these
constraints into a cell as you mentioned.
I believe with a formula on the rhs of a solver constraint, Solver can not
tell if it depends on a changing cell, and must do a lot more work in order
to function. By placing this equation into a cell, Solver is able to check
early in the process if the constraint is dependent of a changing cell. If
not, it does no have to do extra work.

If one wanted to place the following directly into a Solver Constraint..
individual - Made <= -Demand + 0.1 individual

I believe it would be better to rearrange the equation to place the constant
on the rhs.

made - 0.9 *individual <= demand

Because there are no formulas on the rhs, and demand is a constant, Excel
would not have to do extra work.

If one wanted to put the following into Solver,
$F$1>=0.1*Sheet1!$F$7

I believe it would be better to write it as:
10*F1 > F7

Like you mention, it is better to place these into a cell. Just thought I'd
add another benefit.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

You can specify a formula in the right hand side of the constraint. In
your example, it is possible to individual-Made >= 0.1*individual-
Demand, i.e., one cell at a time. So, =$F$1>=0.1*Sheet1!$F$7 is OK.
But, one cannot specify an array formula of all-cells-in-Made >= all-
cells-of-Fractional-Demand. For that one must use a range as you did,
i.e., =$B$1:$G$1>=Sheet1!$B$9:$G$9

Personally, I minimize the use of hardcoded values within Solver. All
that the use of hardcoded values does is to make the optimization model
and the worksheet that much more difficult to understand. And, the
added benefit of not hardcoding numbers is the ability to use array
formulas!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Business solutions leveraging technology
Microsoft Most Valuable Professional (MVP) 2000-2004

added to make the constraint of having "pounds made" at least 10% of demand.
Above is not optimal solution. This is optimal Pounds made 96 92.8 104.1
97.7 1036.9 105.5 with a profit of $6,170.44. What I would like to bea
able to do is create a formula in the constraints area, but I am unable to
do that.)

 

[Tushar Mehta]

Hi Dana,

Just for discussion, when you mention

Were you referring to a constraints directly into Solver?
Excel's Solver I believe does not like Formulas on the right hand side,
although it mostly works. This would be an added benefit of putting these
constraints into a cell as you mentioned.
I believe with a formula on the rhs of a solver constraint, Solver can not
tell if it depends on a changing cell, and must do a lot more work in order

In response to the OP, I selected a cell ($F$10 in the left side of the
constraint dialog box and entered a formula (0.1*Sheet1!$F$7) on the
RHS. XL accepted it. When I saved the model the constraint was shown
as =$F$1>=0.1*Sheet1!$F$7

You are most probably correct in that Solver doesn't handle well
equations on the RHS. Most textbooks on the subjexr also show
optimization equations as having a constant on the RHS. But then
Solver also allows a lot of things that most introductory optimization
courses cannot handle well.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions