Skip to Content

ROOT_SCALAR

Overview

The ROOT_SCALAR function finds a real-valued root of a single-variable equation using methods provided by scipy.optimize.root_scalar. It supports bracketing techniques such as Brent’s method and Ridder’s method, as well as open methods like Newton and the secant algorithm. This wrapper lets Excel users specify the equation as a Python expression in x, choose a numerical method, and control termination tolerances. This example function is provided as-is without any representation of accuracy.

Usage

To evaluate the function in Excel:

=ROOT_SCALAR(func_expr, [bracket], [x_zero], [root_scalar_method], [xtol], [rtol], [maxiter])
  • func_expr (string, required): Expression defining the function of x whose root will be found.
  • bracket (2D list, optional): Single row [[a, b]] specifying the search interval for bracketing methods (brentq, brenth, bisect, ridder, toms748).
  • x_zero (float, optional): Initial guess for open methods (newton, secant).
  • root_scalar_method (string (enum), optional, default="brentq"): Algorithm to use. Valid options: brentq, brenth, bisect, ridder, newton, secant, toms748.
  • xtol (float, optional, default=1e-12): Absolute tolerance for the termination criterion.
  • rtol (float, optional, default=1e-12): Relative tolerance for the termination criterion.
  • maxiter (int, optional, default=100): Maximum number of iterations allowed.

The function returns a scalar root (float) when successful or a descriptive error message string if validation fails or the solver does not converge.

Function Expressions

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

Function/ConstantDescriptionExample
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 sinesinh(x)
cosh(x)Hyperbolic cosinecosh(x)
tanh(x)Hyperbolic tangenttanh(x)
exp(x)Exponential function (exe^x)exp(x)
log(x) or ln(x)Natural logarithm (base ee)log(x) or ln(x)
log10(x)Base-10 logarithmlog10(x)
sqrt(x)Square rootsqrt(x)
abs(x)Absolute valueabs(x)
pow(x, y)Power function (x raised to power y)pow(x, 2)
piMathematical constant π (≈3.14159)x * pi
eMathematical constant e (≈2.71828)e**x

Important Notes:

  • All trigonometric functions use radians, not degrees
  • Use ** (double asterisk) or ^ (caret) for exponentiation. Both work equivalently.
  • Examples: "x**2 - 2", "sin(x) - 0.5", "exp(x) - 10"

Expression Examples

Common mathematical expressions and their func_expr notation:

  • f(x)=x22f(x) = x^2 - 2 (root ≈ 1.414) → "x**2 - 2"
  • f(x)=sin(x)0.5f(x) = \sin(x) - 0.5"sin(x) - 0.5"
  • f(x)=ex10f(x) = e^x - 10"exp(x) - 10" or "e^x - 10"
  • f(x)=x32x5f(x) = x^3 - 2x - 5"x**3 - 2*x - 5"

Examples

Example 1: Square Root with All Parameters

Inputs:

func_exprbracketx_zeromethodxtolrtolmaxiter
x**2 - 202brentq1E-111E-11150

Excel formula:

=ROOT_SCALAR("x**2 - 2", {0,2}, , "brentq", 1E-11, 1E-11, 150)

Expected output:

Result
1.414

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

Example 2: Cubic Root with TOMS748

Inputs:

func_exprbracketmethod
x**3 - x - 212toms748

Excel formula:

=ROOT_SCALAR("x**3 - x - 2", {1,2}, , "toms748")

Expected output:

Result
1.521

Example 3: Newton Method for Square Root

Inputs:

func_exprx_zeromethod
x**2 - 41.5newton

Excel formula:

=ROOT_SCALAR("x**2 - 4", , 1.5, "newton")

Expected output:

Result
2.000

Example 4: Cosine Fixed Point with Custom Tolerances

Inputs:

func_exprbracketmethodxtolrtolmaxiter
math.cos(x) - x01brentq1E-101E-1050

Excel formula:

=ROOT_SCALAR("math.cos(x) - x", {0,1}, , "brentq", 1E-10, 1E-10, 50)

Expected output:

Result
0.739

Python Code

import math from typing import List, Optional, Union from scipy.optimize import root_scalar as scipy_root_scalar Number = Union[int, float] def root_scalar( func_expr: str, bracket: Optional[List[List[Number]]] = None, x_zero: Optional[Number] = None, root_scalar_method: str = "brentq", xtol: float = 1e-12, rtol: float = 1e-12, maxiter: int = 100, ) -> Union[float, str]: """Find a real root of a scalar function using SciPy's ``root_scalar``. Args: func_expr: Function expression in the variable ``x`` (for example, ``"x**2 - 2"``). bracket: Optional 2D list ``[[a, b]]`` defining the bracketing interval for bracketed solvers. x_zero: Optional initial guess used by open methods such as ``newton`` or ``secant``. root_scalar_method: Solver algorithm. Valid options: ``brentq``, ``brenth``, ``bisect``, ``ridder``, ``newton``, ``secant``, ``toms748``. xtol: Absolute tolerance for termination (must be positive). rtol: Relative tolerance for termination (must be positive). maxiter: Maximum number of iterations (must be positive). Returns: Floating-point root when successful, or an error message string if validation fails or the solver does not converge. This example function is provided as-is without any representation of accuracy. """ if not isinstance(func_expr, str) or func_expr.strip() == "": return "Invalid input: func_expr must be a non-empty string." if "x" not in func_expr: return "Invalid input: func_expr must contain the 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." bracketing_methods = {"brentq", "brenth", "bisect", "ridder", "toms748"} derivative_methods = {"newton", "secant"} interval = None if root_scalar_method in bracketing_methods: 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 = { name: getattr(math, name) for name in dir(math) if not name.startswith("_") } # expose the math module name itself so expressions using 'math.<fn>' work safe_globals["math"] = math 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)

Example Workbook

Link to Workbook 

Last updated on