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
where is the objective coefficient vector and 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 whenA_ubis 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 whenA_eqis provided.bounds(2D list of float or None, optional): List of[lower, upper]bounds for each variable. UseNonefor 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:
| c | A_ub | b_ub | A_eq | b_eq | bounds | method | |||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| 3 | 5 | -1 | -2 | -8 | 0 | None | highs-ipm | ||||
| -2 | -1 | -8 | 0 | None |
Excel formula:
=LINEAR_PROG({3,5}, {-1,-2; -2,-1}, {-8; -8}, , , {0,; 0,}, "highs-ipm")Expected output:
| x₁ | x₂ | Objective |
|---|---|---|
| 2.667 | 2.667 | 21.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:
| c | A_ub | b_ub | |
|---|---|---|---|
| -1 | -1 | 1 | 2 |
| 10 |
Excel formula:
=LINEAR_PROG({-1,-1}, {1,2}, 10)Expected output:
| x₁ | x₂ | Objective |
|---|---|---|
| 10.000 | 0.000 | -10.000 |
Example 3: Single Variable
Minimizing a single variable objective with a constraint.
Inputs:
| c | A_ub | b_ub |
|---|---|---|
| 2 | -1 | -4 |
Excel formula:
=LINEAR_PROG(2, -1, -4)Expected output:
| x₁ | Objective |
|---|---|
| 4.000 | 8.000 |
Example 4: Two Constraints
Minimizing with two bound constraints.
Inputs:
| c | A_ub | b_ub | |||
|---|---|---|---|---|---|
| 1 | 1 | 1 | 0 | 5 | |
| 0 | 1 | 3 |
Excel formula:
=LINEAR_PROG({1,1}, {1,0; 0,1}, {5; 3})Expected output:
| x₁ | x₂ | Objective |
|---|---|---|
| 0.000 | 0.000 | 0.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)]]