BASIN_HOPPING

Overview

The BASIN_HOPPING function wraps scipy.optimize.basinhopping to provide a global optimization routine for a single-variable expression inside Excel. Basin-hopping combines random displacements with deterministic local minimization so the search can escape local minima while still converging to low-energy regions. For a scalar objective $f(x)$ , each iteration perturbs the incumbent point and refines it with a local solver:

\begin{align*} x_{\text{trial}} &= x_{\text{current}} + \delta,\\ x_{\text{min}} &= \operatorname{argmin}_x f(x) \text{ started from } x_{\text{trial}},\\ P(\text{accept}) &= \begin{cases} 1, & \Delta f \leq 0,\\ \exp(-\Delta f / T), & \Delta f > 0, \end{cases} \end{align*}

where $\delta$ is drawn from a user-scaled distribution, $T$ controls uphill acceptance, and $\Delta f = f(x_{\text{min}}) - f(x_{\text{current}})$ . This wrapper accepts expressions as strings, restricts optimization to a single variable, and forwards a subset of SciPy’s parameters that are most useful in spreadsheets. This example function is provided as-is without any representation of accuracy.

Usage

To use the function in Excel:


=BASIN_HOPPING(func_expr, x_zero, [niter], [T], [stepsize], [basin_hopping_method])

func_expr (string, required): Single-variable math expression written in Python syntax (for example, "x**2 + 10*sin(x)").
x_zero (float, required): Initial guess for the variable.
niter (int, optional, default=100): Number of basin-hopping iterations; must be a positive integer.
T (float, optional, default=1.0): Temperature parameter for the acceptance test; must be a non-negative number.
stepsize (float, optional, default=0.5): Magnitude of the random displacement applied before each local minimization; must be positive.
basin_hopping_method (string (enum), optional, default="L-BFGS-B"): Local minimization algorithm. Valid options: L-BFGS-B, Powell, BFGS, Nelder-Mead, TNC, CG, SLSQP.

The function returns a 2D list [[x_minimum, minimum_value]] with float entries when successful. If validation fails or SciPy raises an error, the function returns a 2D list containing a single descriptive error message string.

Function Expressions

The func_expr parameter accepts mathematical expressions using any of the following functions and constants. All functions are case-sensitive.

Function/Constant	Description	Example
`sin(x)`	Sine function (radians)	`sin(x)`
`cos(x)`	Cosine function (radians)	`cos(x)`
`tan(x)`	Tangent function (radians)	`tan(x)`
`asin(x)` or `arcsin(x)`	Arc sine function (radians)	`asin(x)` or `arcsin(x)`
`acos(x)` or `arccos(x)`	Arc cosine function (radians)	`acos(x)` or `arccos(x)`
`atan(x)` or `arctan(x)`	Arc tangent function (radians)	`atan(x)` or `arctan(x)`
`sinh(x)`	Hyperbolic sine	`sinh(x)`
`cosh(x)`	Hyperbolic cosine	`cosh(x)`
`tanh(x)`	Hyperbolic tangent	`tanh(x)`
`exp(x)`	Exponential function ( $e^x$ )	`exp(x)`
`log(x)` or `ln(x)`	Natural logarithm (base $e$ )	`log(x)` or `ln(x)`
`log10(x)`	Base-10 logarithm	`log10(x)`
`sqrt(x)`	Square root	`sqrt(x)`
`abs(x)`	Absolute value	`abs(x)`
`pow(x, y)`	Power function (x raised to power y)	`pow(x, 2)`
`pi`	Mathematical constant π (≈3.14159)	`x * pi`
`e`	Mathematical constant e (≈2.71828)	`e**x`
`inf`	Positive infinity	`x + inf` (rarely used)

Important Notes:

All trigonometric functions use radians, not degrees
Use ** (double asterisk) or ^ (caret) for exponentiation. Both are automatically converted to Python’s ** operator. Examples: x**2, x^2, e**x, e^x, 2**x, 2^x all work equivalently
Examples: "x**2 + sin(x)" or "x^2 + sin(x)", "exp(-x) * cos(2*pi*x)", "e**(-x**2)" or "e^(-x^2)"

Expression Examples

Common mathematical expressions and their func_expr notation:

$f(x) = x^2 + 3x - 5$ → "x**2 + 3*x - 5"
$f(x) = e^{-x}$ → "exp(-x)" or "e^(-x)"
$f(x) = \sin(x) + \cos(2x)$ → "sin(x) + cos(2*x)"
$f(x) = x^4 - 2x^2 + 1$ → "x**4 - 2*x**2 + 1"
$f(x) = \ln(x) + x^2$ → "log(x) + x**2" or "ln(x) + x^2"
$f(x) = \sinh(x) + \cosh(x)$ → "sinh(x) + cosh(x)"
$f(x) = \frac{1}{1 + x^2}$ → "1 / (1 + x**2)"
$f(x) = e^{-x^2} \sin(2\pi x)$ → "exp(-x**2) * sin(2*pi*x)" or "e^(-x^2) * sin(2*pi*x)"

Examples

The following examples correspond to the four demo cases shipped with the function. Outputs are rounded to three decimal places for readability.

Example 1: Default Configuration

Inputs:

func_expr	x_zero	niter	T	stepsize	method
x*2 + 10sin(x)	0	120	1.5	0.7	BFGS

Excel formula:


=BASIN_HOPPING("x**2 + 10*sin(x)", 0, 120, 1.5, 0.7, "BFGS")

Expected output:

x_min	Minimum Value
-1.306	-7.946

This example demonstrates using non-default values for all optional parameters.

Example 2: Custom Stepsize and Starting Guess

Inputs:

func_expr	x_zero	niter	T	stepsize
x*2 + 10sin(x)	1.5	150		0.8

Excel formula:


=BASIN_HOPPING("x**2 + 10*sin(x)", 1.5, 150, , 0.8)

Expected output:

x_min	Minimum Value
1.5	-7.946

Increasing the stepsize helps the solver explore more of the search space when starting away from the optimal valley.

Example 3: Zero Temperature with Powell Minimizer

Inputs:

func_expr	x_zero	niter	T	stepsize	method
x*2 + 10sin(x)	0	200	0.0	0.5	Powell

Excel formula:


=BASIN_HOPPING("x**2 + 10*sin(x)", 0, 200, 0, 0.5, "Powell")

Expected output:

x_min	Minimum Value
-1.306	-7.946

Setting T to zero enforces monotonic descent while the Powell local minimizer refines each candidate solution.

Example 4: Quartic with Cosine Perturbation

Inputs:

func_expr	x_zero	niter	T	stepsize	method
(x2 - 4)2 + 5cos(2x)	2	200	1	0.5	L-BFGS-B

Excel formula:


=BASIN_HOPPING("(x**2 - 4)**2 + 5*cos(2*x)", 2, 200, 1, 0.5, "L-BFGS-B")

Expected output:

x_min	Minimum Value
1.825	-3.920

This non-convex quartic with periodic perturbations demonstrates that basin-hopping can traverse multiple local minima before converging to the global solution.

Python Code


import math
import re
from typing import Union
 
import numpy as np
from scipy.optimize import basinhopping as scipy_basinhopping
 
 
def basin_hopping(
    func_expr: str,
    x_zero: float,
    niter: int = 100,
    T: float = 1.0,
    stepsize: float = 0.5,
    basin_hopping_method: str = 'L-BFGS-B',
) -> Union[str, list]:
    """Minimize a single-variable expression with SciPy's basinhopping algorithm.
 
    This function wraps scipy.optimize.basinhopping. For more information, see:
    https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.basinhopping.html
 
    Args:
        func_expr: Expression of variable x (e.g., "x**2 + 10*sin(x)").
        x_zero: Initial guess for x as a scalar value.
        niter: Number of basinhopping iterations. Defaults to 100.
        T: Temperature parameter for acceptance criterion. Must be non-negative. Defaults to 1.0.
        stepsize: Step size for random displacement. Must be positive. Defaults to 0.5.
        basin_hopping_method: Local minimization method. Defaults to "L-BFGS-B".
 
    Returns:
        [[x_minimum, objective_minimum]] on success, or an error message string.
 
    This example function is provided as-is without any representation of accuracy.
    """
    if not isinstance(func_expr, str):
        return "Invalid input: func_expr must be a string."
    if func_expr.strip() == "":
        return "Invalid input: func_expr must be a non-empty string."
 
    func_expr = re.sub(r'\^', '**', func_expr)
 
    if "x" not in func_expr:
        return "Invalid input: func_expr must reference variable x (e.g., x)."
 
    if not isinstance(x_zero, (int, float)):
        return "Invalid input: x_zero must be a scalar (float or int)."
 
    try:
        niter = int(niter)
        if niter <= 0:
            return "Invalid input: niter must be positive."
    except (TypeError, ValueError):
        return "Invalid input: niter must be an integer."
 
    try:
        T = float(T)
        if T < 0:
            return "Invalid input: T must be non-negative."
    except (TypeError, ValueError):
        return "Invalid input: T must be numeric."
 
    try:
        stepsize = float(stepsize)
        if stepsize <= 0:
            return "Invalid input: stepsize must be positive."
    except (TypeError, ValueError):
        return "Invalid input: stepsize must be numeric."
 
    if not isinstance(basin_hopping_method, str) or basin_hopping_method.strip() == "":
        return "Invalid input: basin_hopping_method must be a non-empty string."
 
    safe_globals = {
        "math": math,
        "np": np,
        "numpy": np,
        "__builtins__": {},
    }
    safe_globals.update({
        name: getattr(math, name)
        for name in dir(math)
        if not name.startswith("_")
    })
    safe_globals.update({
        "sin": np.sin,
        "cos": np.cos,
        "tan": np.tan,
        "asin": np.arcsin,
        "arcsin": np.arcsin,
        "acos": np.arccos,
        "arccos": np.arccos,
        "atan": np.arctan,
        "arctan": np.arctan,
        "sinh": np.sinh,
        "cosh": np.cosh,
        "tanh": np.tanh,
        "exp": np.exp,
        "log": np.log,
        "ln": np.log,
        "log10": np.log10,
        "sqrt": np.sqrt,
        "abs": np.abs,
        "pow": np.power,
        "pi": math.pi,
        "e": math.e,
    })
 
    try:
        x0_val = float(x_zero)
    except (TypeError, ValueError):
        return "Invalid input: x_zero must be convertible to float."
 
    try:
        initial_eval = eval(func_expr, safe_globals, {"x": x0_val})
        float(initial_eval)
    except Exception as exc:
        return f"Error: Invalid expression at initial guess: {exc}"
 
    def objective(x_value):
        try:
            x_scalar = float(x_value)
            result = eval(func_expr, safe_globals, {"x": x_scalar})
            numeric_value = float(result)
        except Exception as exc:
            raise ValueError(f"Error evaluating func_expr: {exc}")
        if not math.isfinite(numeric_value):
            raise ValueError("Objective evaluation produced NaN or infinity.")
        return numeric_value
 
    try:
        result = scipy_basinhopping(
            objective,
            x0_val,
            niter=niter,
            T=T,
            stepsize=stepsize,
            minimizer_kwargs={"method": basin_hopping_method},
        )
        if isinstance(result.x, np.ndarray):
            x_min = float(result.x[0]) if result.x.size == 1 else float(result.x)
        else:
            x_min = float(result.x)
        min_val = float(result.fun)
        return [[x_min, min_val]]
    except ValueError as exc:
        return f"Error during basinhopping: {exc}"
    except Exception as exc:
        return f"Error during basinhopping: {exc}"

Example Workbook

Link to Workbook