Python Goal Seek Like Excel? Methods and Examples

Yes. Python can do the same basic job as Excel Goal Seek: adjust one input until a formula reaches a target. For a reliable one-variable solution, use SciPy’s brentq function with a bracket that contains the answer, or use a short bisection routine if you want to avoid installing a package.

What Excel Goal Seek does

Goal Seek is a what-if analysis tool for a formula that depends on an input. You specify three things: the formula cell to reach, the target value for that cell, and the input cell Excel may change. Excel repeatedly changes the input and recalculates the workbook until the formula is sufficiently close to the target or it stops making useful progress.

For example, a business might know its fixed costs, unit cost, and sales volume, then ask what selling price would produce a particular profit. That is a one-input question: price changes while the profit formula recalculates. In Microsoft Excel for Microsoft 365 on Windows, the desktop command is Data > What-If Analysis > Goal Seek. The dialog asks for the formula cell, the desired value, and the changing cell. Excel for Mac has a Goal Seek command in the Data tab’s What-If Analysis tools, although the layout can differ. Excel for the web may not offer the same desktop what-if tools, so use desktop Excel or solve the model in Python if the command is unavailable.

Goal Seek is not the same as testing every possible input. It uses numerical iteration, so it can approach a solution without finding an exact value, and it may not identify a solution if the model is unsuitable or the starting point leads it in the wrong direction. Python has the same underlying challenge: the code needs a function that calculates the result and a numerical method appropriate to that function.

The Python equivalent with SciPy

SciPy is a widely used scientific-computing package for Python. Its scipy.optimize module includes root-finding methods. The common pattern is to turn a target problem into a zero problem: if the calculated output should equal 7,000, define a residual as calculated output minus 7,000, then find an input where that residual is zero.

For a bounded one-variable problem, brentq is often a good choice. It requires lower and upper bounds whose residuals have opposite signs. That requirement is useful: it gives the solver evidence that a continuous function crosses the target somewhere in the interval. Install SciPy in a Python environment with python -m pip install scipy if it is not already available.

Worked example: find a price for a profit target

Suppose a simple model sells 500 units, has a variable cost of $12 per unit, and has $3,000 in fixed costs. The target profit is $7,000. The profit model is (price − 12) × 500 − 3,000. The following Python code searches for a price between $12 and $50:

from scipy.optimize import brentq

def profit(price):
    units = 500
    variable_cost = 12
    fixed_cost = 3000
    return (price - variable_cost) * units - fixed_cost

target_profit = 7000
price = brentq(lambda p: profit(p) - target_profit, 12, 50)
print(price)

The result is 32.0. Checking the arithmetic, a price of $32 leaves $20 per unit after variable cost; $20 × 500 is $10,000, and subtracting $3,000 in fixed costs gives exactly $7,000. At the lower bound of $12, profit is −$3,000, below the target; at the upper bound of $50, profit is $16,000, above it. Those endpoints bracket the answer.

The equivalent setup in Excel uses a formula cell for profit and a separate input cell for price. With price in B2, variable cost in B3, units in B4, and fixed costs in B5, enter the following in B6, the profit cell:

=(B2-B3)*B4-B5

Then run Goal Seek with B6 as the formula cell, 7000 as the target value, and B2 as the changing cell. Python gives the same result while making it easier to repeat the calculation for many targets, include it in a larger program, or save the result in a data workflow.

A no-install option using bisection

If you want to keep the solution within Python’s standard library, bisection is a straightforward alternative. It repeatedly halves an interval that brackets a root. It is usually slower than more sophisticated methods, but its behavior is easy to understand and it does not need SciPy. The function must be continuous across the interval, and the residuals at the endpoints must have opposite signs or include zero.

def bisect_goal(function, low, high, target,
                x_tolerance=1e-8, residual_tolerance=1e-8,
                max_iterations=200):
    def residual(x):
        return function(x) - target

    f_low = residual(low)
    f_high = residual(high)

    if f_low == 0:
        return low
    if f_high == 0:
        return high
    if f_low * f_high > 0:
        raise ValueError('The bounds do not bracket a solution.')

    for _ in range(max_iterations):
        midpoint = (low + high) / 2
        f_mid = residual(midpoint)

        if abs(f_mid) <= residual_tolerance:
            return midpoint
        if (high - low) / 2 <= x_tolerance:
            return midpoint

        if f_low * f_mid < 0:
            high = midpoint
        else:
            low = midpoint
            f_low = f_mid

    raise RuntimeError('Maximum iterations reached before convergence.')

To use it with the price example, pass profit, bounds of 12 and 50, and a target of 7000. The returned price will be very close to 32. The two tolerances measure different things: x_tolerance limits the remaining uncertainty in the input, while residual_tolerance limits how far the calculated output may be from the target. Choose tolerances that make sense for the units and precision of your model.

How to choose a numerical method

The best method depends on what you know about the function, not on whether you are using Python or Excel. For a continuous function with a known interval around the answer, a bracketing method such as Brent’s method or bisection is a practical starting point. If you have a good initial guess and the function is smooth, methods such as Newton’s method can converge quickly, but they can also fail when the guess is poor or the slope is near zero.

Use brentq when you can provide two bounds with opposite-signed residuals. It is a strong default for many bounded, one-dimensional target problems.
Use bisection when simplicity and predictable progress matter more than speed, or when you do not want a third-party dependency.
Use a Newton-style method when you have a credible starting value and understand the function’s behavior. Check the result rather than assuming a solver succeeded.
Use an optimizer when the task is to minimize or maximize an objective, especially when there are several variables or constraints. That is a different problem from matching one output to one target.

A solver’s success message does not establish that the answer makes sense for the real-world model. Check that the result is in an allowed range, substitute it back into the original calculation, and compare the remaining difference with a tolerance meaningful for the application.

Common problems and how to handle them

No sign change at the bounds: brentq needs a bracket. If both endpoint residuals are positive, or both are negative, the interval does not show a crossing. Expand or move the bounds based on the model’s valid input range; do not blindly choose enormous limits if the formula is not meaningful there. A root might exist outside the interval, or the target might be unreachable.

More than one answer: A formula can cross the target several times. A method can return one root within its search interval without telling you that other roots exist elsewhere. Use different brackets to investigate, then decide which result fits the model. For instance, a nonlinear response curve may rise and later fall, so the same output can correspond to two input values.

Discontinuous or step-based formulas: A lookup rule, threshold, or integer-only decision can jump over a target instead of passing through it. A continuous root finder may be unsuitable. If inputs must be whole numbers, enumerate a practical range or use an appropriate discrete search; if the model has discontinuities, inspect the intervals on either side of each jump.

Units and scaling: A residual measured in dollars should not be confused with a tolerance for an input measured in dollars per item. Use separate tolerances when needed, and keep units consistent. Floating-point arithmetic also means that decimal-looking values may not be represented exactly, so a tiny nonzero residual is often normal.

Unexpected formula behavior: Confirm that the function returns a number for every candidate input, that it uses the intended assumptions, and that the target is expressed in the same units as the output. Guard against invalid operations, such as dividing by zero or taking a logarithm of a nonpositive value, by choosing a valid search interval or handling such cases explicitly.

Python versus Excel Goal Seek and Solver

Excel Goal Seek is convenient for an interactive question in an existing workbook: select the formula and changing cells, set the target, and inspect the changed input. Python is more suitable when the same calculation must run repeatedly, when inputs come from files or databases, or when the process needs to be automated and documented in code. Python also makes it straightforward to test multiple starting conditions and record results, but it requires a correct model and a suitable numerical method.

Both Goal Seek and a basic one-dimensional root finder address one input and one target formula. If several inputs can change, or if the answer must obey constraints such as minimum and maximum values, Excel Solver may be the more relevant spreadsheet tool. In Python, SciPy’s optimization tools can handle broader optimization tasks, but setting up an objective and constraints is not the same as asking a one-variable root finder to hit a target. Choose based on the mathematical question rather than the software label.

Also distinguish a workbook library from a calculation engine. A Python package that reads or writes Excel files may preserve formulas without recalculating them. For example, editing a workbook file is not by itself equivalent to opening Excel and running Goal Seek. If the result depends on Excel’s calculation behavior, use a suitable spreadsheet application or recreate the calculation explicitly in Python and verify that the models agree.

Practical checks before relying on a result

A dependable workflow starts with the model, not the solver. Write down which input is allowed to change, what the target output means, and any valid bounds. Then evaluate the function at several plausible inputs before searching. This can expose sign errors, unexpected jumps, or a target that is outside the model’s range.

Keep the calculation in a function that accepts the changing input and returns the output.
Subtract the target to form a residual, then select a solver whose assumptions fit the function.
Use realistic bounds and check that they bracket a solution when the method requires it.
After solving, recalculate the original output and report both the input and the remaining target difference.
For repeated runs, record the target, bounds, tolerances, and solver status so another person can reproduce the calculation.

These checks matter especially when the output informs a business or operational decision. A numerical answer only solves the equations supplied; it cannot confirm that the assumptions, data, or interpretation are correct.

Frequently Asked Questions

Does Python have a built-in Goal Seek function?
No single function in Python’s standard library is named Goal Seek. You can implement a numerical method such as bisection, or use a package such as SciPy, which provides root-finding and optimization tools.

What is the closest SciPy function to Excel Goal Seek?
For one continuous input and a target output, scipy.optimize.brentq is a useful match when you can provide bounds that bracket a root. It solves a numerical equation; it does not operate directly on Excel cells.

Can Python change a cell in an Excel workbook with Goal Seek?
Python can work with workbook files or automate spreadsheet applications, but simply reading or writing a file does not necessarily run Excel’s Goal Seek calculation. For a dependable numerical workflow, recreate the calculation as a Python function, or use an appropriate Excel automation approach and verify the workbook’s recalculation.

Why does a Python solver say the bounds do not bracket a solution?
For a bracketing method, the target-minus-output residual must be zero at a bound or have opposite signs at the two bounds. Try a valid wider interval if the model permits it, or check whether the target can be reached at all.

Can Python Goal Seek solve for multiple changing cells?
A one-variable root finder is designed for one unknown. For multiple unknowns, formulate a system of equations or an optimization problem and use a suitable numerical tool, such as functions in SciPy’s optimization module. The setup and constraints matter, so it is not simply the same Goal Seek operation repeated.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.