MINIMIZE_SCALAR

Overview

The MINIMIZE_SCALAR function finds the minimum value of a scalar mathematical function $f(x)$ , where $x$ is a real number (i.e., a function of a single variable only). This is useful for business users in Excel who need to optimize costs, maximize efficiency, or find the best value for a given scenario. The function leverages scipy.optimize.minimize_scalar and allows users to specify the function as a string, with optional bounds and method. The minimization problem can be stated as:

\min_x f(x)

where $f(x)$ is the user-provided function expression. If bounds are provided, the minimization is performed over the interval $[a, b]$ .

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

Usage

To use the function in Excel:


=MINIMIZE_SCALAR(func_expr, [bounds], [method])

func_expr (string, required): The function to minimize, as a string (e.g., "x**2 + 3*x + 2"). Example: "x**2 + 3*x + 2"
bounds (2D list, optional): 2D list [[min, max]] for bounded minimization. Example: [[0, 10]]
method (string, optional): Optimization method: "brent", "bounded", or "golden". Example: "bounded"

The function returns a 2D list [[x, fun]] where x is the location of the minimum and fun is the minimum value, or a string error message if input is invalid or an error occurs.

Examples

Example 1: Minimize a Quadratic Cost Function

This example minimizes $x^2 + 3x + 2$ with no bounds.

In Excel:


=MINIMIZE_SCALAR("x**2 + 3*x + 2")

Expected output:

x	fun
-1.5	-0.25

Example 2: Minimize a Function with Bounds

This example minimizes $(x-5)^2 + 10$ over $[0, 10]$ using the bounded method.

In Excel:


=MINIMIZE_SCALAR("(x-5)**2 + 10", {0,10}, "bounded")

Expected output:

x	fun
5.0	10.0

Python Code


from scipy.optimize import minimize_scalar as scipy_minimize_scalar
import math
 
def minimize_scalar(func_expr, bounds=None, method=None):
    """
    Minimizes a scalar function using scipy.optimize.minimize_scalar.
 
    Args:
        func_expr (str): The function to minimize, as a string (e.g., 'x**2 + 3*x + 2').
        bounds (list, optional): 2D list [[min, max]] for bounded minimization.
        method (str, optional): Optimization method: 'brent', 'bounded', or 'golden'.
 
    Returns:
        list: [[x, fun]] where x is the location of minimum and fun is the minimum value, or a string error message if input is invalid or an error occurs.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Validate func_expr
    if not isinstance(func_expr, str):
        return "func_expr must be a string."
    if 'x' not in func_expr:
        return "Function expression must contain the variable 'x'."
    # Validate method
    if method is not None and not isinstance(method, str):
        return "method must be a string or None."
    # Validate bounds
    if bounds is not None:
        if not (isinstance(bounds, list) and len(bounds) == 1 and isinstance(bounds[0], list) and len(bounds[0]) == 2):
            return "bounds must be a 2D list [[min, max]] or None."
    # Method/bounds logic
    if method == 'bounded':
        if bounds is None:
            return "Method 'bounded' requires bounds to be specified."
    elif method in ['brent', 'golden']:
        if bounds is not None:
            return f"Method '{method}' cannot be used with bounds. Use 'bounded' instead."
    # Define function
    def func(x):
        try:
            return eval(func_expr, {"x": x, "math": math})
        except Exception:
            return float('nan')
    # Prepare kwargs
    kwargs = {}
    if bounds is not None:
        min_val, max_val = bounds[0][0], bounds[0][1]
        kwargs['bounds'] = (min_val, max_val)
    if method is not None:
        kwargs['method'] = method
    # Run minimization
    try:
        result = scipy_minimize_scalar(func, **kwargs)
        if not hasattr(result, 'success') or not result.success or math.isnan(result.x) or math.isnan(result.fun):
            return f"Error during minimization: {getattr(result, 'message', 'Unknown error')}"
        return [[float(result.x), float(result.fun)]]
    except Exception as e:
        return f"Error during minimization: {str(e)}"

Live Notebook

Edit this function in a live notebook .