ROOT_SOLVER

Overview

Solves systems of nonlinear equations by finding values for variables that satisfy all equations simultaneously. This function enables business users to solve complex, highly nonlinear relationships directly in Excel, leveraging the SciPy root method for advanced root-finding. It is suitable for square systems (number of equations equals number of variables).

A system of nonlinear equations can be written as:

F(\mathbf{x}) = \begin{bmatrix} f_1(x_1, x_2, ..., x_n) \\ f_2(x_1, x_2, ..., x_n) \\ \vdots \\ f_n(x_1, x_2, ..., x_n) \end{bmatrix} = \mathbf{0}

where $\mathbf{x} = [x_1, x_2, ..., x_n]$ is the vector of variables, and each $f_i$ is a nonlinear function. The goal is to find $\mathbf{x}^*$ such that all equations are satisfied simultaneously.

This function uses SciPy’s scipy.optimize.root for root-finding. The process continues until $\|F(\mathbf{x}_k)\|$ is below a specified tolerance or a maximum number of iterations is reached.

This example function is provided as-is without any representation of accuracy.

Usage

To use the ROOT_SOLVER function in Excel, enter it as a formula in a cell, specifying your equations and initial guesses:


=ROOT_SOLVER(equations, variables)

equations (2D list of string, required): Symbolic expressions for the system of equations to solve. Example: [ ["x**2 + y**2 - 1", "x - y"] ]
variables (2D list of float, required): Initial guesses for the variables. Example: [[0.5, 0.5]]

The function returns a 2D list of floats representing the solution for the variables that satisfy the system, or a string error message if the solver fails or input is invalid.

Examples

Example 1: Chemical Equilibrium

A chemist needs to solve for concentrations of two substances at equilibrium, given two nonlinear equations representing the chemical reactions.

In Excel:


=ROOT_SOLVER({"x**2 + y**2 - 1", "x - y"}, {0.5, 0.5})

Expected output:

x	y
0.7071	0.7071

Example 2: Engineering Circuit Analysis

An engineer wants to find the voltages in a nonlinear circuit described by two equations.

In Excel:


=ROOT_SOLVER({"x**3 + y - 1", "x + y**3 - 1"}, {0.7, 0.7})

Expected output:

x	y
0.6826	0.6826

Python Code


import numpy as np
import re
from scipy.optimize import root
 
 
def root_solver(equations, variables):
    """
    Solves a square system of nonlinear equations using SciPy's root-finding capabilities.
 
    Args:
        equations (list[list[str]]): 2D list of symbolic expressions for the system (e.g., [["x**2 + y**2 - 1"], ["x - y"]] or [["x**2 + y**2 - 1", "x - y"]]).
        variables (list[list[float]]): 2D list of initial guesses for the variables (e.g., [[0.5, 0.5]]).
 
    Returns:
        list[list[float]]: The solution for the variables that satisfy the system, or
        str: Error message if the solver fails or input is invalid.
 
    This example function is provided as-is without any representation of accuracy.
    """
    if not (isinstance(equations, list) and len(equations) > 0 and all(isinstance(row, list) for row in equations)):
        return "equations must be a 2D list of strings."
    if not (isinstance(variables, list) and len(variables) > 0 and isinstance(variables[0], list)):
        return "variables must be a 2D list of floats."
    # Flatten all rows of equations (works for row or column vector)
    eqn_list = [eq for row in equations for eq in row]
    n_eqns = len(eqn_list)
    var_vals = variables[0]
    n_vars = len(var_vals)
    if n_vars != n_eqns:
        return f"Error: number of variables ({n_vars}) must match number of equations ({n_eqns})."
    var_names = [chr(ord('x') + i) for i in range(n_vars)]
    def substitute_vars(expr, var_names):
        for i, name in enumerate(var_names):
            expr = re.sub(rf'(?<![A-Za-z0-9_]){name}(?![A-Za-z0-9_])', f'x[{i}]', expr)
        return expr
    def fun(x):
        local_dict = {'x': x}
        f_list = []
        for eq in eqn_list:
            try:
                eq_sub = substitute_vars(eq, var_names)
                f_list.append(eval(eq_sub, local_dict))
            except Exception as e:
                return [float('nan')]*n_eqns
        return f_list
    try:
        sol = root(fun, np.array(var_vals, dtype=float))
    except Exception as e:
        return f"Solver failed: {str(e)}"
    if not sol.success:
        return f"Solver failed: {sol.message}"
    return [sol.x.flatten().tolist()]

Live Notebook

Edit this function in a live notebook .