MINIMIZE_MULTIVARIATE

Overview

The MINIMIZE_MULTIVARIATE function finds the minimum of a multivariate mathematical function using scipy.optimize.minimize. This is useful for business users in Excel who need to optimize costs, maximize efficiency, or find the best value for a given scenario involving multiple variables.

This function is designed for functions of one or more variables (i.e., it can handle both single-variable and multivariate functions), but is most useful for multivariate cases. It leverages numerical optimization algorithms to solve problems of the form:

\min_{x \in \mathbb{R}^n} f(x)

where $f(x)$ is a user-supplied function of $n$ variables, and $x$ is a vector of decision variables. The user provides $f(x)$ as a Python expression in terms of x, e.g., 'x[0]**2 + x[1]**2 + 3*x[0]'. The function supports optional bounds, enabling the solution of both unconstrained and bounded optimization problems. The underlying algorithms include BFGS, SLSQP, COBYLA, and others, which use gradient-based or simplex methods to efficiently search for the minimum.

For example, the Rosenbrock function, a classic test problem for optimization algorithms, is defined as:

f(x) = (1 - x_0)^2 + 100(x_1 - x_0^2)^2

The function can handle problems with or without bounds, making it flexible for a wide range of business and engineering applications.

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

Usage

To use the MINIMIZE_MULTIVARIATE function in Excel, enter it as a formula in a cell, specifying your function expression and any optional arguments as needed:


=MINIMIZE_MULTIVARIATE(func_expr, x_zero, [bounds], [method])

func_expr (string, required): The function to minimize, as a string. Example: “x[0]**2 + x[1]**2 + 3*x[0]”
x_zero (2D list, required): Initial guess for the variables, as a 2D list. Example: {-1,2}
bounds (2D list, optional, default=None): Optional bounds for variables, as a 2D list of (min, max) pairs. Example: {0,10;0,5}
method (string, optional, default=None): Optional optimization method. Example: “BFGS”

The function returns a 2D list: [[x0, x1, …, fun]] where x-values are the minimum and fun is the minimum value, or a string with an error message if input is invalid or an error occurs.

Examples

Example 1: Minimize Rosenbrock’s Function (2 variables)

This example minimizes the Rosenbrock function $f(x) = (1 - x_0)^2 + 100(x_1 - x_0^2)^2$ .

In Excel:


=MINIMIZE_MULTIVARIATE("(1 - x[0])**2 + 100*(x[1] - x[0]**2)**2", {-1,2})

Expected output:

x0	x1	Minimum Value
1.0	1.0	0.0

Example 2: Minimize with Bounds

This example minimizes $f(x) = (x_0-3)^2 + (x_1-4)^2 + 7$ , with $x_0 \in [0, 10]$ and $x_1 \in [0, 10]$ .

In Excel:


=MINIMIZE_MULTIVARIATE("(x[0]-3)**2 + (x[1]-4)**2 + 7", {1,1}, {0,10;0,10})

Expected output:

x0	x1	Minimum Value
3.0	4.0	7.0

Python Code


import math
from scipy.optimize import minimize
 
def minimize_multivariate(func_expr, x_zero, bounds=None, method=None):
    """
    Minimizes a multivariate function using scipy.optimize.minimize.
 
    Args:
        func_expr (str): Function to minimize, as a string (e.g., 'x[0]**2 + x[1]**2').
        x_zero (list[list[float]]): Initial guess for the variables, as a 2D list (e.g., [[0, 0]]).
        bounds (list[list[float]] or None): Optional bounds for variables, as a 2D list of (min, max) pairs.
        method (str or None): Optional optimization method supported by scipy.optimize.minimize.
 
    Returns:
        list[list[float]] or str: [[x0, x1, ..., fun]] where x is the location of minimum and fun is the minimum value, or a string with an error message.
 
    This example function is provided as-is without any representation of accuracy.
    """
    if not isinstance(func_expr, str):
        return "func_expr must be a string."
    if x_zero is None or not (isinstance(x_zero, list) and len(x_zero) > 0 and isinstance(x_zero[0], list)):
        return "x_zero (initial guess) must be provided as a 2D list, e.g., [[0, 0]]."
    x0_row = x_zero[0]
    if not all(isinstance(v, (int, float)) for v in x0_row):
        return "x_zero (initial guess) must be a 2D list of numbers."
    bounds_list = None
    if bounds is not None:
        if not (isinstance(bounds, list) and all(isinstance(b, (list, tuple)) and len(b) == 2 for b in bounds)):
            return "bounds must be a 2D list of (min, max) pairs or None."
        bounds_list = [tuple(b) for b in bounds]
    method_str = None
    if method is not None:
        if isinstance(method, list):
            if len(method) > 0 and isinstance(method[0], list):
                method_str = method[0][0] if len(method[0]) > 0 else None
            elif len(method) > 0 and isinstance(method[0], str):
                method_str = method[0]
            else:
                return "method must be a string, 2D list, or None."
        elif isinstance(method, str):
            method_str = method
        else:
            return "method must be a string, 2D list, or None."
    if 'x' not in func_expr:
        return "Function expression must contain the variable 'x'."
    def func(x):
        try:
            return eval(func_expr, {"x": x, "math": math})
        except Exception as e:
            return float('inf')
    kwargs = {}
    if bounds_list is not None:
        kwargs['bounds'] = bounds_list
    if method_str is not None:
        kwargs['method'] = method_str
    try:
        result = minimize(func, x0_row, **kwargs)
        if not hasattr(result, 'x') or not hasattr(result, 'fun'):
            return "Error during minimization: Invalid result object."
        if not result.success or not isinstance(result.fun, (int, float)) or result.fun == float('inf'):
            msg = getattr(result, 'message', None)
            if msg:
                return f"Error during minimization: {msg}"
            return "Error during minimization: Optimization failed."
        x_list = [float(xi) for xi in result.x]
        return [x_list + [float(result.fun)]]
    except ValueError as ve:
        return str(ve)
    except Exception as e:
        return f"Error during minimization: {str(e)}"

Live Notebook

Edit this function in a live notebook .