USTChEX Documentation - Core Numerics

USTCHEX_NRSYS: Newton–Raphson solver for scalar equations and nonlinear systems

• Description:
  Solves a nonlinear equation or a system of nonlinear equations using the Newton–Raphson method with numerically approximated derivatives. The function accepts equations written directly as Excel formulas and iteratively updates the solution vector until the squared residual norm satisfies a specified tolerance or a maximum iteration count is reached. Numerical derivatives are evaluated using either forward or central finite differences. To improve robustness, update steps are adaptively damped based on residual reduction, allowing stable convergence for strongly nonlinear problems.

• Syntax:
  =USTCHEX_NRSYS(Rng, xo, n, [ctr_chk], [beta], [h], [tol])

• Rng:
  Range containing the nonlinear equation(s).
  • For a single equation, this is one cell containing a formula of the form =f(x)
  • For a system, this is a vertical range where each cell contains one equation
  Each cell must contain a formula defining the residual to be driven to zero. The equal sign is removed internally.

• xo:
  Initial guess for the unknown(s).
  • Single cell for a scalar problem
  • Vertical range for a system of equations
  Must have the same size and ordering as Rng in the system case.

• n:
  Maximum number of Newton–Raphson iterations permitted.

• ctr_chk (Optional):
  Logical flag controlling numerical differentiation scheme.
  • TRUE → central finite differences
  • FALSE or omitted → forward finite differences

• beta (Optional):
  Initial damping factor applied to Newton updates. Values less than 1 reduce step aggressiveness and can improve convergence for difficult problems.
  Default: 1

• h (Optional):
  Finite-difference step size used for numerical derivative evaluation.
  Default: 0.01

• tol (Optional):
  Convergence tolerance based on the squared residual norm.
  Default: 1×10⁻¹²

• Returns:
  • Scalar problem: a single converged root
  • System problem: a vertical array containing the converged solution vector

• Notes:
  • Supports both scalar and multi-variable nonlinear systems.
  • Uses numerical Jacobian evaluation; no analytical derivatives are required.
  • Adaptive step damping is applied automatically to ensure residual reduction.
  • Convergence is assessed using the sum of squared residuals.
  • May fail or converge slowly if the initial guess is poor or the Jacobian is ill-conditioned.

• Example:
  =USTCHEX_NRSYS(A1:A3, B1:B3, 50, TRUE, 1, 0.01, 1E-10)

USTCHEX_SIMPSON: Adaptive composite Simpson 3/8 numerical integration (grid refinement)

• Description:
  Computes the definite integral from xo to xf of a user-defined function f(x) written as an Excel formula. The method applies the composite Simpson 3/8 rule on a uniform grid and repeatedly refines the grid by doubling the number of subintervals until the absolute change between successive composite estimates falls below a specified tolerance (or until a refinement limit is reached). The integrand is evaluated using Excel’s formula evaluation after substituting the symbolic x-variable with numeric values.

• Syntax:
  =USTCHEX_SIMPSON(Rng, xo, xf, [tol], [n_RBG_max])

• Rng:
  Cell containing the integrand formula.
  • This is one cell containing a formula of the form =f(x)
  The equal sign is removed internally.

• xo:
  Cell containing the lower limit xo. Its cell reference or defined name is used as the symbolic x-variable inside the integrand expression.

• xf:
  Upper limit of integration xf.

• tol (Optional):
  Absolute convergence tolerance based on the difference between successive composite integral estimates.
  Default: 0.001

• n_RBG_max (Optional):
  Maximum number of grid-refinement levels permitted. The number of subintervals is 2^n_RBG at refinement level n_RBG.
  Default: 15

• Returns:
  A single scalar estimate of the definite integral.

• Notes:
  • Uses the Simpson 3/8 rule on each subinterval with internal evaluation points at 1/3 and 2/3 of the subinterval.
  • Refinement doubles the number of subintervals each iteration (n_interval = 2^n_RBG).
  • Convergence is assessed by Abs(area_old − area_new).
  • If convergence is achieved immediately, the returned value corresponds to the last computed estimate at termination.
  • Best suited for smooth integrands; discontinuities or highly oscillatory functions may require tighter tolerances or alternative quadrature.

• Example:
  If A1 contains =EXP(-x^2) and B1 is the x-variable cell (named or referenced in the formula):
  =USTCHEX_SIMPSON(A1, B1, 2, 1E-6, 20)

USTCHEX_TRAPZDATA: Trapezoidal-rule integration of tabulated data

• Description:
  Computes the definite integral of tabulated data using the trapezoidal rule. The function integrates discrete x–y data pairs over a specified interval. If the integration bounds do not coincide with data points, linear interpolation is applied at the bounds so that partial end segments are handled consistently. If no bounds are specified, the full data span is integrated.

• Syntax:
  =USTCHEX_TRAPZDATA(xdat, ydat, [xa], [xb])

• xdat:
  Range containing x-values of the data points (single column). Values are assumed to be ordered.

• ydat:
  Range containing y-values corresponding to xdat (single column). Must have the same number of points as xdat.

• xa (Optional):
  Lower integration bound. If omitted or zero together with xb, the first x-value in xdat is used.

• xb (Optional):
  Upper integration bound. If omitted or zero together with xa, the last x-value in xdat is used.

• Returns:
  A single scalar estimate of the definite integral over the specified interval.

• Notes:
  • Applies linear interpolation at xa and xb if they fall inside a data segment.
  • Segments completely outside the integration interval are ignored.
  • If the bounds lie outside the data range, the function terminates with an error message.
  • Suitable for nonuniformly spaced data.
  • Accuracy depends on data resolution and smoothness.

• Example:
  =USTCHEX_TRAPZDATA(A2:A101, B2:B101, 1.5, 7.0)

USTCHEX_SIMPSONDATA: Simpson-style polynomial panel integration of tabulated data

• Description:
  Computes the definite integral of tabulated x–y data using Simpson-style polynomial panel integration. The method constructs local interpolating polynomials using Lagrange interpolation and integrates each polynomial panel exactly. Cubic panels (four points) are used wherever possible; if fewer points remain, the method automatically switches to quadratic (three-point) or linear (two-point) panels. This approach supports nonuniform x-spacing while retaining the core idea of Simpson-type integration based on polynomial interpolation.

• Syntax:
  =USTCHEX_SIMPSONDATA(xdat, ydat, [xa], [xb])

• xdat:
  Range containing x-values of the tabulated data (single column). Values must be strictly monotone (either increasing or decreasing) with no duplicates.

• ydat:
  Range containing y-values corresponding to xdat (single column). Must have the same number of points as xdat.

• xa (Optional):
  Lower integration bound. If omitted or if both xa and xb are zero, the integration starts at the first x-value in xdat.

• xb (Optional):
  Upper integration bound. If omitted or if both xa and xb are zero, the integration ends at the last x-value in xdat.

• Returns:
  A single scalar estimate of the definite integral over the specified interval.

• Notes:
  • Uses cubic polynomial panels when four data points are available.
  • Automatically falls back to quadratic panels when three points remain.
  • Automatically falls back to linear (trapezoidal) integration when only two points remain.
  • Each panel is integrated exactly over its local interval, allowing nonuniform x-spacing.
  • Integration bounds that cut through a panel are handled by partial-panel integration.
  • For strongly noisy data, lower-order methods (such as trapezoidal integration) may be more robust.

• Example:
  =USTCHEX_SIMPSONDATA(A2:A21, B2:B21)
  =USTCHEX_SIMPSONDATA(A2:A21, B2:B21, 1.5, 7.0)

USTCHEX_GAULEG: Composite Gauss–Legendre numerical integration (fixed 100-point rule per subinterval)

• Description:
  Computes the definite integral of a user-defined function f(x) using Gauss–Legendre quadrature applied over one or more uniform subintervals. Each subinterval is integrated using a fixed 100-point Gauss–Legendre rule on the transformed interval, and the total integral is obtained by summing contributions from all subintervals. The integrand is supplied as an Excel formula and is evaluated directly through Excel’s expression engine after substitution of the independent variable.

• Syntax:
  =USTCHEX_GAULEG(Rng, xo, xf, [n_interval])

• Rng:
  Cell containing the integrand formula.
  • This is one cell containing a formula of the form =f(x)
  The equal sign is removed internally.

• xo:
  Cell containing the lower integration limit xo. Its cell reference or defined name is used as the symbolic variable inside the integrand expression.

• xf:
  Upper limit of integration xf.

• n_interval (Optional):
  Number of uniform subintervals used to partition the integration domain. Gauss–Legendre quadrature is applied independently on each subinterval.
  Default: 1

• Returns:
  A single scalar estimate of the definite integral.

• Notes:
  • Uses a fixed 100-point Gauss–Legendre rule on each subinterval.
  • The integration interval [xo, xf] is linearly mapped to the reference interval for each subinterval.
  • Increasing n_interval improves robustness for functions with localized curvature or mild non-smoothness.
  • Particularly effective for smooth integrands where very high accuracy is desired with relatively few subintervals.
  • More computationally expensive per interval than Simpson-style methods due to the large number of function evaluations.

• Example:
  If A1 contains =EXP(-x^2) and B1 is the x-variable cell:
  =USTCHEX_GAULEG(A1, B1, 2)
  =USTCHEX_GAULEG(A1, B1, 2, 5)