Calc Goal Seek with INDIRECT: verify the returned number in a three-cell test

By ON Lab · Sources checked November 8, 2026

When Calc Goal Seek returns a number, put that candidate back into the variable cell and recalculate the target formula before accepting it. In our LibreOffice 26.8.2.2 test, direct references reached the goal, while two INDIRECT forms returned the starting value and still evaluated to 16 rather than the requested 10.

A November 2026 reader supplied a three-cell example after troubleshooting a larger matrix workbook. We reproduced that exact small mathematical dependency with native Calc on macOS 27.2 arm64. This guide concerns the minimal example and result verification; it does not fix or validate the reader's singular-value calculation.

Build the three-cell control

A1: 2
A2: =A1^2*4
A3: =INDIRECT("A2")

A1 is the variable, and A2 is an ordinary formula depending on it. A3 reads the result through a text-built reference. Initially both formula cells evaluate to 16.

Change A1 to 1.5 and recalculate. In all our tested reference variants, the target became 9. This control matters: INDIRECT could evaluate the dependency during ordinary recalculation. A failure during the goal search was not evidence that the cell could never calculate the value.

Restore A1 to 2 before each separate search. Use a target value of 10 and A1 as the variable. Test A2 directly first, then use A3 with each of the reference forms below.

Compare the native search results

We called Calc's documented XGoalSeek interface, retained the returned candidate, explicitly assigned that candidate to A1, and recalculated the document. The target values below use the full returned number, not the rounded number displayed in the table.

The successful controls were close to 10, with an absolute target difference of about 3.1 × 10⁻¹¹. The INDIRECT cases missed the goal by 6. Both nevertheless held ordinary numeric results without a cell formula error after recalculation. A numeric return and an error-free cell are insufficient success checks.

Our test used original disposable spreadsheets in an isolated profile. It exercised the native API, not clicks in the Goal Seek dialog, and it did not open the reader's actual workbook. The reader reported macOS 15.8.2; our operating-system version differed.

Separate a candidate from an applied solution

In our API calls, A1 remained 2 after the search returned. We then assigned the returned Result explicitly. If you automate the operation, inspect the actual variable value and forward-evaluate the target rather than assuming that receiving a result object has changed the sheet.

The API also returns Divergence, described as the result change in the last iteration. Our direct and INDEX controls returned zero for that field, while the two INDIRECT cases returned a very large value. We still checked the target formula itself; we did not use Divergence as a substitute for a business or numerical acceptance criterion.

When using the dialog, Calc's official Goal Seek guide describes the formula cell, requested target and variable cell. Use those same three inputs in a copy of the small example, and inspect the resulting formula value before accepting a proposed change. The native tests here do not establish the exact wording of your version's dialog message.

Do not treat concatenating a reference as a general fix

A reply in the reader discussion reports that concatenating the address worked in that environment. In our native test, INDIRECT("A"&"2") did not reach the target. We have not established the cause of that difference.

Keep the literal and concatenated variants as separate controls. A result from one environment is not proof that rewriting every address string will repair another workbook. Preserve the exact formula, version, starting input and target when comparing results.

Use direct-reference controls to locate the boundary

For this fixed-cell example, =A2 and =INDEX(A2) both reached the goal. They provide useful comparison paths because they access the same ordinary calculated value without the tested INDIRECT expression.

They are not automatic replacements for arbitrary dynamic ranges. If your workbook selects arrays of changing size, replacing a reference can change what the model computes. Validate the selected cells and the resulting mathematics before adopting a workaround. We did not reproduce the reader's matrix construction, numerical conditioning or root-selection requirements.

The simple equation itself has both positive and negative roots. Our starting input was positive, and the successful controls returned the positive root. Do not infer that a single goal-search result enumerates every mathematically valid solution.

Keep a reproducible acceptance check

1. Record the Calc version, operating system, starting variable and target.
2. Confirm that changing the variable normally changes the target.
3. Test the underlying direct formula before adding reference indirection.
4. Restore the same starting input for each comparison.
5. Apply the full returned candidate in a disposable copy and recalculate.
6. Measure the difference between the actual target and the requested target using a tolerance appropriate to the model.

If only one reference form fails, retain the small example and its observed results for further investigation. We did not verify an official fixed version or an authoritative statement that all INDIRECT formulas are unsupported. The evidence supports the specific boundary above, not a universal diagnosis for every Goal Seek failure.

Sources

• ask.libreoffice.org: source guidance
• help.libreoffice.org: source guidance
• api.libreoffice.org: source guidance
• api.libreoffice.org: source guidance