Optimize a constrained model with Solver in OpenOffice Calc

Use Tools > Solver when you need to choose several inputs to maximize or minimize a result subject to constraints. The built-in Solver documented for Apache OpenOffice supports linear models; do not assume an engine list or nonlinear capability from another spreadsheet program.

Set up a continuous production model

Use Apache OpenOffice Calc 4.1 desktop, English menus, and a disposable ODS file. The procedure is documentation-qualified, not native-tested. This example uses divisible production batches, so fractional quantities would be allowed. It deliberately avoids relying on integer-solver behavior.

Enter the following model:

Cell	Meaning	Value or formula
B2	Product A batches	0
C2	Product B batches	0
B3	Profit per A batch	30
C3	Profit per B batch	20
B5	Total profit	=B2*B3+C2*C3
B6	Labour used	=2*B2+C2
C6	Labour available	100
B7	Materials used	=B2+2*C2
C7	Materials available	80

Place descriptive labels elsewhere without overwriting the listed cells. Save this zero-production baseline. The input variables are B2:C2; coefficients B3:C3 and capacities C6:C7 must remain fixed during a solve.

Describe the decision precisely

1. Choose Tools > Solver.
2. Set Target cell to $B$5 and choose Maximum. You are maximizing profit, not setting profit to a guessed target.
3. Set By changing cells to $B$2:$C$2.
4. Add limiting conditions $B$6 <= $C$6 and $B$7 <= $C$7.
5. Add nonnegative conditions for B2 and C2 individually. Do not rely on an unexamined default nonnegativity option.
6. Review every reference and run the calculation with OK. If a successful solution is offered, inspect it before choosing Keep Result.

Expected quantities are B2=40 and C2=20, giving profit 1600. Labour is 2×40+20=100, and materials are 40+2×20=80. Both capacities are fully used, and neither variable is negative.

Prove what the answer means

For this two-variable linear model, check the feasible corner points independently. Producing neither product gives profit 0. Producing only A is limited to 50 batches by labour, yielding 1500. Producing only B is limited to 40 by materials, yielding 800. The two capacity lines meet at (40,20), yielding 1600. That is the best corner and therefore the optimum for this bounded linear exercise.

A successful solver message alone is insufficient. If the returned values violate a capacity or are negative, do not keep them as a valid plan. Recheck the constraints and selected variable cells. Small floating-point differences near a boundary may need tolerance-aware inspection, but a substantive violation is not rounding.

Re-solve after a real change

Change labour capacity C6 from 100 to 110, leaving materials at 80. Run Solver again with the same model references. Expected quantities become A=46⅔ and B=16⅔, giving profit 1733⅓. Labour is 110 and materials remain 80. Display enough decimal places to check the formulas rather than demanding rounded quantities exactly satisfy both constraints.

This changed case shows why the variable definition matters. If a “batch” must be indivisible, the continuous answer is not an executable plan. Do not round it and claim Solver found the integer optimum. Create and validate a separate integer-constrained model using a documented available engine and check every constraint afterward.

Recover and save the decision

Cancel an unsuccessful solve rather than deleting constraints until a result appears. Restore the saved zero-production copy if the working inputs become confusing. For the final fixture, restore C6=100 and solve again to obtain 40, 20 and 1600.

Save the ODS workbook, close and reopen it, and check variable values, formulas and capacity calculations. Record the objective and constraints alongside the sheet so the stored numbers remain interpretable even if dialog settings differ in another installation. No extension installation is required for this linear exercise.

Official reference

Apache OpenOffice 4.1 Calc Guide, Chapter 9, Using the Solver. Recovered guide retrieved 19 October 2026; independent corner calculations do not constitute native Solver execution.