Skip to Content

LINEAR_PROG

Overview

The LINEAR_PROG function solves linear programming problems by wrapping scipy.optimize.linprog. It minimizes a linear objective subject to inequality, equality, and bound constraints of the form

minx  cTxextsubjecttoAubxbub,Aeqx=beq,xu\begin{align*} \min_x \; c^T x \\ ext{subject to } A_{ub} x \leq b_{ub}, \quad A_{eq} x = b_{eq}, \quad \ell \leq x \leq u \end{align*}

where cc is the objective coefficient vector and xx is the decision vector. The solver leverages the HiGHS optimization suite by default but also exposes legacy SciPy simplex and interior-point algorithms for compatibility. This example function is provided as-is without any representation of accuracy.

Usage

To evaluate the function in Excel:

=LINEAR_PROG(c, [A_ub], [b_ub], [A_eq], [b_eq], [bounds], [linear_prog_method])
  • c (2D list of float, required): Objective coefficients. Provide as a single row or column.
  • A_ub (2D list of float, optional): Coefficient matrix for inequality constraints.
  • b_ub (2D list of float, optional): Right-hand side for inequality constraints; required when A_ub is provided.
  • A_eq (2D list of float, optional): Coefficient matrix for equality constraints.
  • b_eq (2D list of float, optional): Right-hand side for equality constraints; required when A_eq is provided.
  • bounds (2D list of float or None, optional): List of [lower, upper] bounds for each variable. Use None for an unbounded side.
  • linear_prog_method (string (enum), optional, default="highs"): Solver algorithm. Valid options: HiGHS, HiGHS Dual Simplex, HiGHS Interior Point, Interior Point, Revised Simplex, Simplex.

The function returns a single-row 2D list containing the variable values followed by the optimal objective value, or a string error message if the inputs are invalid or the problem is infeasible.

Examples

Example 1: Basic Maximization with All Parameters

Maximizing profit subject to resource constraints with non-default solver.

Inputs:

cA_ubb_ubA_eqb_eqboundsmethod
35-1-2-80Nonehighs-ipm
-2-1-80None

Excel formula:

=LINEAR_PROG({3,5}, {-1,-2; -2,-1}, {-8; -8}, , , {0,; 0,}, "highs-ipm")

Expected output:

x₁x₂Objective
2.6672.66721.333

This example demonstrates using non-default values for all optional parameters.

Example 2: Basic Minimization

Minimizing a simple objective function subject to constraints.

Inputs:

cA_ubb_ub
-1-112
10

Excel formula:

=LINEAR_PROG({-1,-1}, {1,2}, 10)

Expected output:

x₁x₂Objective
10.0000.000-10.000

Example 3: Single Variable

Minimizing a single variable objective with a constraint.

Inputs:

cA_ubb_ub
2-1-4

Excel formula:

=LINEAR_PROG(2, -1, -4)

Expected output:

x₁Objective
4.0008.000

Example 4: Two Constraints

Minimizing with two bound constraints.

Inputs:

cA_ubb_ub
11105
013

Excel formula:

=LINEAR_PROG({1,1}, {1,0; 0,1}, {5; 3})

Expected output:

x₁x₂Objective
0.0000.0000.000

Python Code

import numpy as np from scipy.optimize import linprog def linear_prog(c, A_ub=None, b_ub=None, A_eq=None, b_eq=None, bounds=None, linear_prog_method=None): """Solve a linear programming problem using SciPy's ``linprog``. Args: c: 2D list containing the objective coefficients. Treated as a row or column vector. A_ub: Optional 2D list representing inequality constraint coefficients where ``A_ub @ x <= b_ub``. b_ub: Optional 2D list providing the right-hand side for inequality constraints. A_eq: Optional 2D list representing equality constraint coefficients where ``A_eq @ x = b_eq``. b_eq: Optional 2D list providing the right-hand side for equality constraints. bounds: Optional 2D list of ``[lower, upper]`` bounds for each decision variable. linear_prog_method: Optional name of the solver algorithm. Valid options: ``highs``, ``highs-ds``, ``highs-ipm``, ``interior-point``, ``revised simplex``, ``simplex``. Returns: Single-row 2D list containing the optimal variable values followed by the objective value, or an error message string if validation fails or the problem is infeasible. This example function is provided as-is without any representation of accuracy. """ def to2d(x): return [[x]] if not isinstance(x, list) else x def conv(mat, name, cols=None): if mat is None: return None if not isinstance(mat, list) or len(mat) == 0: return f"Invalid input: {name} must be a 2D list with at least one row." out = [] for ri, row in enumerate(mat): if not isinstance(row, list) or len(row) == 0: return f"Invalid input: each row in {name} must be a non-empty list." if cols is not None and len(row) != cols: return f"Invalid input: {name} must have {cols} columns to match the number of variables." r = [] for ci, v in enumerate(row): try: n = float(v) except Exception: return f"Invalid input: {name} must be numeric. Problem at row {ri+1}, column {ci+1}." if not np.isfinite(n): return f"Invalid input: {name} must contain finite values. Problem at row {ri+1}, column {ci+1}." r.append(n) out.append(r) return np.asarray(out, dtype=float) c = to2d(c) A_ub = to2d(A_ub) if A_ub is not None else None b_ub = to2d(b_ub) if b_ub is not None else None A_eq = to2d(A_eq) if A_eq is not None else None b_eq = to2d(b_eq) if b_eq is not None else None cv = conv(c, "c") if isinstance(cv, str): return cv c_vec = cv.flatten() if c_vec.size == 0: return "Invalid input: c must contain at least one coefficient." if A_ub is not None and b_ub is None: return "Invalid input: b_ub must be provided when A_ub is specified." if b_ub is not None and A_ub is None: return "Invalid input: A_ub must be provided when b_ub is specified." A_ub_m = conv(A_ub, "A_ub", cols=c_vec.size) if isinstance(A_ub_m, str): return A_ub_m if b_ub is not None: bv = conv(b_ub, "b_ub") if isinstance(bv, str): return bv if A_ub_m is not None and A_ub_m.shape[0] != bv.flatten().size: return "Invalid input: Number of rows in A_ub must equal the length of b_ub." b_ub_v = bv.flatten() else: b_ub_v = None if A_eq is not None and b_eq is None: return "Invalid input: b_eq must be provided when A_eq is specified." if b_eq is not None and A_eq is None: return "Invalid input: A_eq must be provided when b_eq is specified." A_eq_m = conv(A_eq, "A_eq", cols=c_vec.size) if isinstance(A_eq_m, str): return A_eq_m if b_eq is not None: bev = conv(b_eq, "b_eq") if isinstance(bev, str): return bev if A_eq_m is not None and A_eq_m.shape[0] != bev.flatten().size: return "Invalid input: Number of rows in A_eq must equal the length of b_eq." b_eq_v = bev.flatten() else: b_eq_v = None bounds_seq = None if bounds is not None: if not isinstance(bounds, list) or len(bounds) != c_vec.size: return "Invalid input: bounds must supply one [min, max] pair per variable." bb = [] for i, p in enumerate(bounds): if not isinstance(p, list) or len(p) != 2: return "Invalid input: each bounds entry must be a [min, max] pair." lo = float(p[0]) if p[0] is not None else None hi = float(p[1]) if p[1] is not None else None if lo is not None and not np.isfinite(lo): return f"Invalid input: bounds lower value at index {i+1} must be finite or None." if hi is not None and not np.isfinite(hi): return f"Invalid input: bounds upper value at index {i+1} must be finite or None." if lo is not None and hi is not None and lo > hi: return f"Invalid input: lower bound must not exceed upper bound for variable {i+1}." bb.append((lo, hi)) bounds_seq = tuple(bb) solver = linear_prog_method.lower() if isinstance(linear_prog_method, str) else "highs" valid = {"highs", "highs-ds", "highs-ipm", "interior-point", "revised simplex", "simplex"} if solver not in valid: return ("Invalid input: linear_prog_method must be one of 'highs', 'highs-ds', 'highs-ipm', " "'interior-point', 'revised simplex', or 'simplex'.") try: res = linprog(c=c_vec, A_ub=A_ub_m, b_ub=b_ub_v, A_eq=A_eq_m, b_eq=b_eq_v, bounds=bounds_seq, method=solver) except ValueError as e: return f"linear_prog error: {e}" except Exception as e: return f"linear_prog error: {e}" if not getattr(res, "success", False) or res.x is None: msg = getattr(res, "message", "Solver failed.") return f"linear_prog failed: {msg}" return [[float(x) for x in res.x] + [float(res.fun)]]

Example Workbook

Link to Workbook 

Last updated on