
Using the Solver Function
From NeoWiki
The Solver in Calc is a tool that helps you to calculate the conditions which maximize profit and minimize cost while satisfying some constraints. It can solve linear or nonlinear problems, but keep in mind that the nonlinear problems are much more difficult to solve. The example below shows the utilisation of the Solver function in Calc.
[edit] Problem Presentation
Let us assume that a factory produces three types of machines, A1, A2, A3. Let pi be the cost of the raw material and hi the number of hours which are necessary to produce a machine of the type Ai. The profit per machine of the type Ai is equal to bi.
The decision variables are the numbers xi of machines of the type Ai which are produced.
We want to calculate the values of the numbers xi which maximize the total profit.
These data have to satisfy some constraints, for example:
 every number xi must be an integer. We want to produce at least 30 machines of each type.
 the total cost of raw material must be less than or equal to $10,000
 the total number of working hours must be less than or equal to 5,200.
Machines  A1  A2  A3 

Number of machines  x1  x2  x3 
Cost of raw material per machine  p1  p2  p3 
Number of working hours per machine  h1  h2  h3 
Profit per machine  b1  b2  b3 
Profit to maximize  b1x1+b2x2+b3x3  
Constraints  x1 >= 30, x2 >= 30, x3 >= 30  p1x1+p2x2+p3x3 <= 10 000  h1x1+h2x2+h3x3 <= 5 200 
[edit] Solving the Problem
 Open a Calc document
 In the spreadsheet, enter the data as in the table below:
The value 1890 contained in the cell B8 is the sum of working hours necessary to produce 30 machines of each type, namely SUMPRODUCT(B2:D2;B4:D4).
The value 6300 contained in the cell B9 is the cost of raw material necessary to produce 30 machines of each type, namely SUMPRODUCT(B2:D2;B3:D3).
We have to maximize the total profit: SUMPRODUCT(B2:D2;B5:D5)
 Choose Solver… from the Tools menu. The Optimization Solver window appears.
 Define the model:
 N.B All the references are automatically translated into absolute references by the solver. That explains the presence of the prefix "$" in the cells adresses.
 Click on the little green arrow to the right of the Set target cell field, then click on the cell which contains the measure to optimize, here B7 which contains the formula giving the total profit. Click on the arrow to the right of the input field again, the value $Sheet1.$B$7 is automatically placed in the Set target cell field.
 Click on Maximize. It's the goal to calculate.
 Click on the little green arrow to the right of the By changing cells field, in order to choose the cells containing the decision variables whose values allowing the profit maximization have to be computed. Select the three cells B2, C2, D2 by dragging the mouse. Click on the arrow to the right of the input field again. The range $Sheet1.$B$2:$D$2 is automatically placed in the By changing cells field.
 Define the constraints:
 Let us set the following constraints:
 SUMPRODUCT(B2:D2;B4:D4) ≤ 5 200
 SUMPRODUCT(B2:D2;B3:D3) ≤ 10 000
 B2 ≥ 30
 C2 ≥ 30
 D2 ≥ 30
 Click the Add button. The Constraint window appears.
 Click on the little green arrow to the right of the Cell reference field and click on the cell B8, then on the arrow again.
 In the Constraint field enter the value you desire, namely 5200.
 Click OK.
 Click on the Add button again and add the four other constraints in the same way. Change the sign "<=" into ">=" for the constraints that are imposed on the values xi.
 Let us set the following constraints:
 Choose the options :
 Click on the Options button
 In the Options window that appears, leave the default option Assume linear model checked and check the Allow only integer values option.
 Click OK.
 Click on the Options button
 Click on the Solve button.
 The message Solution found appears in a dialog box. Click OK.
 The values of cells B2 to D2 have been changed so that the profit may be maximum, under the conditions set by the constraints.
 In that example we get x1= 82, x2 = 31 and x3 = 30.
 The values of cells B2 to D2 have been changed so that the profit may be maximum, under the conditions set by the constraints.
 You can save that model by clicking the Save button. By clicking the Load button, you will be able to display it later and, for instance, modify the values of the constraints or of the production costs.
[edit] Link