XL_Find_Root.txt

/** Find a root of a scalar function */
root_scalar = LAMBDA(f, x_low, x_high, [x_atol], [x_rtol], [y_tol], [max_iter],
    LET(
        x_atol, IF(ISOMITTED(x_atol), 0.000000000001, x_atol),
        x_rtol, IF(ISOMITTED(x_rtol), 0.000001, x_rtol),
        y_tol, IF(ISOMITTED(y_tol), 1E-22, y_tol),
        max_iter, IF(ISOMITTED(max_iter), 20, max_iter),
        f_low, f(x_low),
        f_high, f(x_high),
        IF(
            f_low * f_high <= 0,
            _Anderson_Bjorck(f, x_low, x_high, f_low, f_high, x_atol, x_rtol, y_tol, max_iter),
            #VALUE!
        )
    )
);

_Anderson_Bjorck = LAMBDA(f, x_low, x_high, f_low, f_high, x_atol, x_rtol, y_tol, max_iter,
    IF(
        OR(
            ABS(x_high - x_low) <= x_atol + x_rtol * ABS(x_low),
            ABS(f(x_high)) <= y_tol,
            max_iter <= 0
        ),
        x_high,
        LET(
            x_new, (x_low * f_high - x_high * f_low) / (f_high - f_low),
            f_new, f(x_new),
            IF(
                f_new * f_high < 0,
                _Anderson_Bjorck(
                    f,
                    x_high,
                    x_new,
                    f_high,
                    f_new,
                    x_atol,
                    x_rtol,
                    y_tol,
                    max_iter - 1
                ),
                IF(
                    f_new * f_high > 0,
                    _Anderson_Bjorck(
                        f,
                        x_low,
                        x_new,
                        f_low * IF(f_new / f_high < 1, 1 - f_new / f_high, 0.5),
                        f_new,
                        x_atol,
                        x_rtol,
                        y_tol,
                        max_iter - 1
                    ),
                    x_new
                )
            )
        )
    )
);