ROOT_SCALAR
Overview
The ROOT_SCALAR function finds a real root of a scalar function—that is, a value x where f(x) = 0. Root-finding is a fundamental operation in numerical analysis with applications ranging from solving equations in engineering and physics to financial modeling and optimization problems.
This function wraps SciPy’s root_scalar, which provides a unified interface to multiple root-finding algorithms. The function accepts a mathematical expression as a string (e.g., "x^2 - 2") and automatically parses it, supporting standard mathematical functions like sin, cos, exp, log, and sqrt.
Available Methods
The function supports two categories of solvers:
Bracketing methods require an interval [a, b] where f(a) and f(b) have opposite signs, guaranteeing a root exists by the intermediate value theorem:
- brentq (default): Brent’s method, a hybrid algorithm combining bisection, the secant method, and inverse quadratic interpolation. It guarantees convergence while achieving superlinear convergence for well-behaved functions.
- brenth: A variant of Brent’s method using hyperbolic extrapolation.
- bisect: The bisection method, which repeatedly halves the interval. Simple and robust, but slower than other methods.
- ridder: Ridders’ method, using exponential interpolation for faster convergence.
- toms748: Algorithm 748, an advanced method that typically converges faster than Brent’s method.
Derivative-free open methods require an initial guess x_zero:
- newton: The secant method approximation to Newton’s method (since no derivative is provided).
- secant: Uses two initial points to approximate the derivative.
For most use cases, the default brentq method provides an excellent balance of reliability and speed. The algorithm terminates when either the absolute tolerance (xtol) or relative tolerance (rtol) criterion is satisfied, or when the maximum number of iterations (maxiter) is reached.
For more information, see the SciPy root_scalar documentation and the SciPy GitHub repository.
This example function is provided as-is without any representation of accuracy.
Excel Usage
=ROOT_SCALAR(func_expr, bracket, x_zero, root_scalar_method, xtol, rtol, maxiter)func_expr(str, required): Expression defining the function of x whose root will be found (e.g., “x^2 - 2”).bracket(list[list], optional, default: null): Single row [[a, b]] specifying the search interval for bracketing methods.x_zero(float, optional, default: null): Initial guess for open methods (newton, secant).root_scalar_method(str, optional, default: “brentq”): Solver algorithm to use.xtol(float, optional, default: 1e-12): Absolute tolerance for termination (must be positive).rtol(float, optional, default: 1e-12): Relative tolerance for termination (must be positive).maxiter(int, optional, default: 100): Maximum number of iterations (must be positive).
Returns (float): Root value (float), or error message string.
Examples
Example 1: Square root of 2 using Brent’s method with custom tolerances
Inputs:
| func_expr | bracket | x_zero | root_scalar_method | xtol | rtol | maxiter | |
|---|---|---|---|---|---|---|---|
| x^2 - 2 | 0 | 2 | brentq | 1e-11 | 1e-11 | 150 |
Excel formula:
=ROOT_SCALAR("x^2 - 2", {0,2}, , "brentq", 1e-11, 1e-11, 150)Expected output:
1.414
Example 2: Cubic equation root using TOMS748 algorithm
Inputs:
| func_expr | bracket | root_scalar_method | |
|---|---|---|---|
| x^3 - x - 2 | 1 | 2 | toms748 |
Excel formula:
=ROOT_SCALAR("x^3 - x - 2", {1,2}, "toms748")Expected output:
1.521
Example 3: Square root of 4 using Newton-Raphson method
Inputs:
| func_expr | x_zero | root_scalar_method |
|---|---|---|
| x^2 - 4 | 1.5 | newton |
Excel formula:
=ROOT_SCALAR("x^2 - 4", 1.5, "newton")Expected output:
2
Example 4: Cosine fixed point with custom tolerances
Inputs:
| func_expr | bracket | root_scalar_method | xtol | rtol | maxiter | |
|---|---|---|---|---|---|---|
| cos(x) - x | 0 | 1 | brentq | 1e-10 | 1e-10 | 50 |
Excel formula:
=ROOT_SCALAR("cos(x) - x", {0,1}, "brentq", 1e-10, 1e-10, 50)Expected output:
0.7391
Python Code
import math
import re
import numpy as np
from scipy.optimize import root_scalar as scipy_root_scalar
def root_scalar(func_expr, bracket=None, x_zero=None, root_scalar_method='brentq', xtol=1e-12, rtol=1e-12, maxiter=100):
"""
Find a real root of a scalar function using SciPy's ``root_scalar``.
See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.root_scalar.html
This example function is provided as-is without any representation of accuracy.
Args:
func_expr (str): Expression defining the function of x whose root will be found (e.g., "x^2 - 2").
bracket (list[list], optional): Single row [[a, b]] specifying the search interval for bracketing methods. Default is None.
x_zero (float, optional): Initial guess for open methods (newton, secant). Default is None.
root_scalar_method (str, optional): Solver algorithm to use. Valid options: brentq, brenth, bisect, ridder, newton, secant, toms748. Default is 'brentq'.
xtol (float, optional): Absolute tolerance for termination (must be positive). Default is 1e-12.
rtol (float, optional): Relative tolerance for termination (must be positive). Default is 1e-12.
maxiter (int, optional): Maximum number of iterations (must be positive). Default is 100.
Returns:
float: Root value (float), or error message string.
"""
if not isinstance(func_expr, str) or func_expr.strip() == "":
return "Invalid input: func_expr must be a non-empty string."
# Convert caret (^) to double asterisk (**) for exponentiation
func_expr = re.sub(r'\^', '**', func_expr)
if not re.search(r'\bx\b', func_expr):
return "Invalid input: func_expr must reference variable x."
valid_methods = {"brentq", "brenth", "bisect", "ridder", "newton", "secant", "toms748"}
if root_scalar_method not in valid_methods:
return (
"Invalid method: {0}. Must be one of: {1}".format(root_scalar_method, ", ".join(sorted(valid_methods)))
)
try:
xtol_value = float(xtol)
rtol_value = float(rtol)
maxiter_value = int(maxiter)
except (TypeError, ValueError):
return "Invalid input: xtol and rtol must be numeric and maxiter must be an integer."
if xtol_value <= 0 or rtol_value <= 0:
return "Invalid input: xtol and rtol must be positive."
if maxiter_value <= 0:
return "Invalid input: maxiter must be positive."
def to2d(x):
"""Normalize input to 2D list format."""
if x is None:
return None
if not isinstance(x, list):
return [[x]]
if len(x) > 0 and not isinstance(x[0], list):
return [x]
return x
bracketing_methods = {"brentq", "brenth", "bisect", "ridder", "toms748"}
derivative_methods = {"newton", "secant"}
interval = None
if root_scalar_method in bracketing_methods:
bracket = to2d(bracket)
if not (
isinstance(bracket, list)
and len(bracket) == 1
and isinstance(bracket[0], list)
and len(bracket[0]) == 2
):
return f"Invalid input: method '{root_scalar_method}' requires bracket as [[a, b]]."
try:
lower, upper = float(bracket[0][0]), float(bracket[0][1])
except (TypeError, ValueError):
return "Invalid input: bracket values must be numeric."
if not math.isfinite(lower) or not math.isfinite(upper):
return "Invalid input: bracket values must be finite."
if lower >= upper:
return "Invalid input: bracket must satisfy a < b."
interval = (lower, upper)
if root_scalar_method in derivative_methods:
if x_zero is None:
return f"Invalid input: method '{root_scalar_method}' requires x_zero."
try:
x_zero_value = float(x_zero)
except (TypeError, ValueError):
return "Invalid input: x_zero must be numeric."
else:
x_zero_value = None
safe_globals = {
"math": math, # Expose the math module itself
"np": np,
"numpy": np,
"__builtins__": {}, # Disable built-in functions for safety
}
# Add all math module functions
safe_globals.update({
name: getattr(math, name)
for name in dir(math)
if not name.startswith("_")
})
# Add common numpy/math function aliases
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, # Natural log alias
"log10": np.log10,
"sqrt": np.sqrt,
"abs": np.abs,
"pow": np.power,
"pi": math.pi,
"e": math.e,
"inf": math.inf,
"nan": math.nan,
})
def _function(x_value: float) -> float:
try:
result = eval(func_expr, safe_globals, {"x": x_value})
except Exception as exc:
raise ValueError(f"Error evaluating func_expr: {exc}")
try:
numeric_value = float(result)
except (TypeError, ValueError) as exc:
raise ValueError(f"Function did not return a numeric value: {exc}")
if not math.isfinite(numeric_value):
raise ValueError("Function evaluation produced a non-finite value.")
return numeric_value
kwargs = {
"method": root_scalar_method,
"xtol": xtol_value,
"rtol": rtol_value,
"maxiter": maxiter_value,
}
if interval is not None:
kwargs["bracket"] = interval
if x_zero_value is not None:
kwargs["x0"] = x_zero_value
try:
result = scipy_root_scalar(_function, **kwargs)
except ValueError as exc:
return f"root_scalar error: {exc}"
except Exception as exc:
return f"root_scalar error: {exc}"
if not result.converged:
flag = getattr(result, "flag", "Solver did not converge.")
return f"root_scalar failed: {flag}"
if result.root is None or not math.isfinite(result.root):
return "root_scalar failed: result is not finite."
return float(result.root)