Find a Required Input with Goal Seek in LibreOffice Calc

Goal Seek changes one input until a formula approaches a target. It does not choose the best business plan, impose integer constraints, or solve several variables at once. This example derives the answer first, so a successful dialog is not the only check.

Build a simple profit model

Use LibreOffice Calc 26.2 desktop, English standard menus on Windows/Linux, in an ODS practice copy. The procedure is supported by the 26.2 guide; no native run or save/reopen test is claimed. Enter these labels in column A and values or formulas in column B:

Row	Label	B value/formula
1	Units	100
2	Price per unit	20
3	Variable cost per unit	12
4	Fixed cost	500
5	Profit	=B1*(B2-B3)-B4

B5 should be 300. The contribution per unit is 20 − 12 = 8. To earn 1100, the independent calculation is (1100 + 500) / 8 = 200 units. Keep B1 as a plain numeric input, not a formula, and keep the target out of B5: overwriting B5 would destroy the model you want to solve.

Solve and decide whether to accept

1. Select B5 and choose Tools > Goal Seek.
2. Set Formula cell to $B$5. It must contain the profit formula.
3. Set Target value to 1100, without currency symbols.
4. Set Variable cell to $B$1. Price, variable cost and fixed cost must remain unchanged.
5. Click OK and read the result dialog. For this linear fixture, the proposed input should be about 200.
6. Choose Yes to put the proposed input into the variable cell only after checking it. Verify B1 is about 200 and B5 is about 1100; tiny floating-point residuals may require more displayed decimal places to inspect.

The algorithm iterates toward the target, so inspect both the formula value and the input’s meaning. Formatting B1 as an integer only hides decimals; it does not enforce whole-unit sales.

Change one business assumption

Set B1 back to 100, then change B2 from 20 to 22. Baseline profit is now 500. Run the same Goal Seek with target 1100 and variable B1. The expected input is 160 because (1100 + 500) / (22 − 12) = 160. Confirm that the price remains 22 and cost remains 12 after acceptance.

For a rejection rehearsal, restore B1 to 100 and run the solve again, but choose No in the result dialog. Confirm the original input remains 100 and profit remains 500. This gives you a controlled way to inspect a proposed answer without committing it.

Recognize an impossible target

In a separate disposable copy, set B2 and B3 both to 12. B5 becomes −500 for every possible units value. A target of 1100 is therefore impossible with B1 as the only variable. If Calc reports failure and offers to insert its closest value, choose No. Do not accept that value as a solution merely because it was calculated.

Other failures can come from a wrong Formula cell, a variable not referenced by the formula, or a formula with discontinuities. Review the dependency with Detective rather than repeatedly changing initial guesses blindly. A negative or fractional solution may satisfy the algebra while violating the real task; use a constrained method when those conditions matter.

Restore and preserve the model

Return B1:B4 to 100, 20, 12 and 500. Confirm B5 is 300, then solve once more to 200 units if you want to save the target case. Record which assumption set and target the saved answer represents. Save, close and reopen the ODS; verify the formula is still present and recomputes when B1 changes. Goal Seek leaves an input value in the workbook, not an automatically maintained target rule. Changing costs later will not automatically seek 1100 again.

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.