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 ofxwhose 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/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 () | exp(x) |
log(x) or ln(x) | Natural logarithm (base ) | 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 |
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:
- (root ≈ 1.414) →
"x**2 - 2" - →
"sin(x) - 0.5" - →
"exp(x) - 10"or"e^x - 10" - →
"x**3 - 2*x - 5"
Examples
Example 1: Square Root with All Parameters
Inputs:
| func_expr | bracket | x_zero | 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:
| Result |
|---|
| 1.414 |
This example demonstrates using non-default values for all optional parameters.
Example 2: Cubic Root with TOMS748
Inputs:
| func_expr | bracket | method | |
|---|---|---|---|
| x**3 - x - 2 | 1 | 2 | toms748 |
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_expr | x_zero | method |
|---|---|---|
| x**2 - 4 | 1.5 | newton |
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_expr | bracket | method | xtol | rtol | maxiter | |
|---|---|---|---|---|---|---|
| math.cos(x) - x | 0 | 1 | brentq | 1E-10 | 1E-10 | 50 |
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)