Solver Example

Apache OpenOffice 4.1 Calc Guide

Chapter 9: Data Analysis

< Previous Section

Next Section >

• Introduction
• Consolidating Data
• Creating Subtotals
• Using “What If” Scenarios
  • Creating Scenarios
  • Settings
  • Changing Scenarios
    • Changing Scenario Properties
    • Changing Scenario Cell Values
  • Working with Scenarios Using the Navigator
  • Tracking Values in Scenarios
• Using Other “What If” Tools
  • Multiple Operations in Columns or Rows
    • Calculating with One Formula and One Variable
    • Calculating with Several Formulas Simultaneously
  • Multiple Operations Across Rows and Columns
    • Calculating with Two Variables
• Working Backwards Using Goal Seek
  • Goal Seek Example
• Using the Solver
  • Solver Example

< Previous Page

Next Page >

Solver Example

Let's work on the example mentioned in the Introduction: you have a $1000 credit at a company, and you want to spend it on three products: A, B, and C. The products cost $27.45, $13.87, and $54.19, respectively. You have to buy at least 10 of A, 5 of B, and 7 of C to meet your current needs. How many of each product should you buy to spend exactly $1000? To find the answer using Solver:

1. We set up the data as shown in Figure 257. The Solver is flexible enough to accommodate many different data arrangements. Here, there are columns for the Item, Price per item, Quantity, and Cost. The Cost column has an extra cell at the bottom that sums the cost of the individual items. We will want to adjust the quantities in column C to achieve a total cost of $1000.
   1. The cells D2:D4 have formulas like =B2 * C2.
   2. The cell D5 has the formula =SUM(D2:D4).
2. You can enter arbitrary values, which can be 0 or blank, in the cells of column C, the cells whose values will be adjusted. We have chosen to enter the minimum purchase of each product.

Figure 257: Example setup for Solver

3. Choose Tools → Solver. The Solver dialog opens.

Figure 258: The Solver dialog after filling in values

4. Click in the Target cell field. In the sheet, click in the cell that contains the target value or simply type in the cell address. In this example it is cell D5 .
5. Select Value of and enter 1000 in the field next to it.
6. Click in the By changing cells field and select the cells C2:C4 in the sheet.
7. Enter limiting conditions for the variables by selecting the Cell reference, Operator, and Value fields. In this example, we set the cells to be greater than or equal to the minimum order quantity, and we have limited C2, C3, and C4 to be integers. Only the integer limitation for C2 is visible in Figure 258, but all three cells have been limited to integers.
8. Click  OK . A dialog appears informing you that the Solving successfully finished. Click Keep Result to enter the results in the cells with the variable values and the target cell.

Figure 259 shows three different Solver calculations. The first one, in rows 1 – 5, shows the outcome of the process just described. The target value is met exactly with integer quantities in C2:C4.

The calculation in rows 7 – 11 shows the result if the Price of Item C is changed to $54.18. It is no longer possible to meet exactly the $1000 target with integer values in C8:C10. Solver limits two of the Quantities to integers and allows one to take a decimal value to meet the target. You can see that the Item A Quantity is about 20.09.

Figure 259: Result of three Solver operations

The default solver supports only linear equations. For nonlinear programming requirements, try the Solver for Nonlinear Programming [Beta]. It is available from the OpenOffice extensions repository. (For more about extensions, see Chapter 14 (Setting up and Customizing Calc.)

You can control which Quantity is allowed to take decimal values by changing the specification of the By changing cells box in the Solver dialog shown in Figure 258. In the calculation in rows 7 – 11, the specification was written as C8:C10. Alternatively, you can write each cell address separately, separating them with semicolons. (This is how you would have to enter the cells if they were not contiguous.) The calculation in rows 13 – 17 shows the outcome of specifying C15;C14;C16 in the By changing cells box. Now, the Quantity of Item B, in row 15, is allowed to be a non-integer. The cell that is listed first in the By changing cells specification is the one that takes non-integer values.