Optimize a constrained model with Solver in LibreOffice Calc

Solver can maximize an objective while obeying several constraints. Write the model and verify its feasible limits before clicking Solve; a reported solution is useful only if it satisfies the actual problem.

Build a bounded production model

Use LibreOffice Calc 26.2 desktop with English standard menus on Windows/Linux. This documentary example has independently checked mathematics, but no native Solver run or save/reopen test. Use a disposable ODS file. It needs an already available linear Solver engine, not Java installation or a security change.

Enter the following labels and cells:

Cell	Meaning	Entry
B1	Product A units	0
B2	Product B units	0
B3	Contribution	=30*B1+20*B2
B4	Labor used	=2*B1+B2
B5	Material used	=B1+2*B2
B6	Labor limit	100
B7	Material limit	80

Put labels in column A. Both products must have nonnegative, whole-number quantities. The objective is contribution, not revenue or total accounting profit.

The resource boundaries meet at A=40, B=20: labor 2×40+20=100; material 40+2×20=80; contribution 1600. The other nonnegative corner solutions are (0, 0), (50, 0) and (0, 40), with contributions 0, 1500 and 800. Thus 1600 is the continuous linear maximum and also a valid integer solution.

Configure the objective and all limits

1. Choose Tools > Solver.
2. Set Target cell to $B$3 and Optimize result to Maximum.
3. Set By changing cells to $B$1:$B$2. Do not include resource formulas or the objective itself.
4. Add limiting conditions: $B$4 <= $B$6; $B$5 <= $B$7; $B$1 >= 0; $B$2 >= 0.
5. Add Integer conditions for B1 and B2. Integer is an operator, not the text “Integer” entered into a resource-value cell.
6. Open Options and select an available LibreOffice Linear Solver or LibreOffice CoinMP Linear Solver. Keep the model linear and record which engine you used.
7. Choose Solve. Inspect the result before choosing Keep Result. Use Restore Previous if it does not match the model or if you only wanted a trial.

The source guide notes that nonlinear evolutionary engines can depend on Java configuration. They are unnecessary for this fixture. If neither documented linear engine is available, stop and record that availability blocker rather than enabling unrelated runtime settings.

Audit the proposed solution

Expect approximately 40 and 20, contribution 1600, and both resources exactly at their limits. Inspect enough decimals to distinguish a tiny numerical tolerance from a materially fractional quantity. Whole-number formatting alone is not an integer constraint.

Independently recompute both resource uses from the proposed units. A maximum higher than 1600 in the original model usually means a missing or reversed constraint. A negative unit quantity indicates omitted nonnegativity. A result of 1500 at (50, 0) is feasible but not the optimum because (40, 20) earns more.

Change a resource and test infeasibility

Change B6 to 110 and solve again with the same model. The expected integer maximum is A=47, B=16, contribution 1730. Labor is 110 and material 79, so one material unit remains unused. The continuous intersection would be (46.6667, 16.6667), with contribution 1733.3333, but it is not an integer solution. Rounding both coordinates upward violates the material limit. Enumerating integer A from 0 through 55 and B from 0 through 40 confirms 1730 is the greatest feasible contribution; keep the integer constraints active for this second run.

For an infeasibility rehearsal, in a separate copy require B1 ≥ 60 while restoring the labor limit 100. Even with B2=0, labor 120 exceeds 100. Do not keep a failed result. Remove that temporary condition and restore the original inputs/limits.

Save the ODS and reopen Tools > Solver to inspect stored settings; the guide states Solver configurations are saved per sheet. Verify the expected 40/20 result again rather than assuming a workbook’s last displayed numbers prove its constraints. Detective can help trace an objective to incorrect resource formulas.

Official references

• LibreOffice 26.2 Calc Guide

Sources were captured on 20 October 2026 and reviewed from retained snapshots. All outcomes above are expected checks, not a claim of native execution.