LProg(A, s, b, c, vector, [epsilon], [BigM])

1. Purpose

Finds one or more solutions for a linear programming problem with m constraints of the form:

(1) Σ_j=1..n(x[j]·a[i,j]) ≤ b[i]
(2) Σ_j=1..n(x[j]·a[i,j]) = b[i]
(3) Σ_j=1..n(x[j]·a[i,j]) ≥ b[i]

and a maximized objective function:

Σ_j=1..n(x[j]·c[j])

2. Arguments

• A — m×n matrix of coefficients a[i,j]
• b — m‑element column vector
• c — n‑element column vector
• s — m‑element column vector specifying constraint types:
  • s[i] = 1 → ≤
  • s[i] = 0 → =
  • s[i] = −1 → ≥
• vector — return mode:
  • 0 → number of optimum vectors found
  • i > 0 → i‑th optimum vector
• epsilon — precision threshold; coefficients with absolute value < epsilon are treated as 0 (default: 1e−8)
• BigM — large artificial‑variable coefficient used in the Big‑M method; if omitted, an appropriate Big‑M value is chosen automatically

3. Behavior

• All returned x[i] values are non‑negative.
• If the model allows negative variables, each such variable must be represented by a pair of non‑negative variables.
• To minimize the objective function, negate it and use the default maximization.
• If the objective function is unconstrained, LProg returns #N/A!.
• If the constraints are inconsistent and no solution exists, LProg returns #NULL!.

4. Example

Initial constraints:

2·x1 + x2 + 2·x3 ≤ 20
3·x1 − x2 + x3 = 10

Objective function:

3·x1 + x2 − x3 → max

Formula:

=LProg({2, 1, 1; 1, 1, 3}, {1; 0}, {5; 10}, {4; 2; 2}, 1,,)

returns {1; 0; 3}

LProgBin(A, s, b, c, bin, vector, [epsilon], [BigM])

1. Purpose

Finds one or more solutions for a linear programming problem with constraints of the same form as LProg, where some or all variables x[i] are required to be binary (0 or 1).

2. Arguments

• A, b, c, s, epsilon, BigM — as in LProg.
• bin — n‑element column vector defining variable types:
  • bin[i] = 1 → x[i] ∈ {0, 1}
  • bin[i] = 0 → x[i] ≥ 0
• vector — return mode:
  • 0 → number of optimum vectors found
  • i > 0 → i‑th optimum vector

3. Behavior

• All returned x[i] values are non‑negative; binary variables are restricted to 0 or 1.
• To minimize the objective function, negate it and use the maximization procedure.
• If the objective function is unconstrained, LProgBin returns #N/A!.
• If the constraints are inconsistent and no solution exists, LProgBin returns #NULL!.

4. Example

Initial constraints:

2.5·x1 + 1.5·x2 + x3 ≤ 15
x1 + 1.5·x2 + 3·x3 = 10

Objective function:

x1 + x2 + 2·x3 → max

Formula:

=LProgBin({2.5, 1.5, 1; 1, 1.5, 3}, {1; 0}, {15; 10}, {1; 1; 2}, {1; 0; 1}, 1,,)

returns {1; 4; 1}

LProgInt(A, s, b, c, int, vector, [epsilon], [BigM])

1. Purpose

Finds one or more solutions for a linear programming problem with constraints of the same form as LProg, where some or all variables x[i] are required to be integers.

2. Arguments

• A, b, c, s, epsilon, BigM — as in LProg.
• int — n‑element column vector defining variable types:
  • int[i] = 1 → x[i] must be integer
  • int[i] = 0 → x[i] ≥ 0
• vector — return mode:
  • 0 → number of optimum vectors found
  • i > 0 → i‑th optimum vector

3. Behavior

• All returned x[i] values are non‑negative; selected variables must be integers.
• To minimize the objective function, negate it and use the maximization procedure.
• If the objective function is unconstrained, LProgInt returns #N/A!.
• If the constraints are inconsistent and no solution exists, LProgInt returns #NULL!.

4. Example

Initial constraints:

2.5·x1 + 1.5·x2 + x3 ≤ 15
x1 + 1.5·x2 + 3·x3 = 10

Objective function:

x1 + x2 + 2·x3 → max

Formula:

=LProgInt({2.5, 1.5, 1; 1, 1.5, 3}, {1; 0}, {15; 10}, {1; 1; 2}, {1; 0; 1}, 1,,)

returns {4; 0; 2}

QProg(A, s, b, p, C, vector, [epsilon])

1. Purpose

Finds one or more solutions for a quadratic programming problem with m constraints:

(1) Σ_j=1..n(x[j]·a[i,j]) ≤ b[i]
(2) Σ_j=1..n(x[j]·a[i,j]) = b[i]
(3) Σ_j=1..n(x[j]·a[i,j]) ≥ b[i]

and a maximized objective function:

pᵀ·x − xᵀ·C·x

where p is an n‑element column vector and C is a positive‑definite n×n matrix representing a quadratic form.

2. Arguments

• A — m×n matrix of coefficients a[i,j]
• b — m‑element column vector
• p — n‑element column vector
• C — n×n positive‑definite matrix
• s — m‑element column vector specifying constraint types:
  • s[i] = 1 → ≤
  • s[i] = 0 → =
  • s[i] = −1 → ≥
• vector — return mode:
  • 0 → number of optimum vectors found
  • i > 0 → i‑th optimum vector
• epsilon — precision threshold (default: 1e−8)

3. Behavior

• All x[i] are non‑negative.
• If negative variables are needed, each must be represented by a pair of non‑negative variables.
• To minimize the objective function, change its sign and use the maximization procedure.
• If the objective function is unconstrained, QProg returns #N/A!.
• If the constraints are inconsistent and no solution exists, QProg returns #NULL!.

4. Example

Constraints:

x1 + 2·x2 ≤ 10
x1 + x2 ≤ 9
x1 ≥ 0, x2 ≥ 0

Objective function:

f(x1, x2) = 10·x1 + 25·x2 − 10·x1² − x2² − 4·x1·x2 → max

Data:

A = {1, 2; 1, 1}
s = {1; 1}
b = {10; 9}
p = {10; 25}
C = {10, 2; 2, 1}

Formula:

=QProg({1, 2; 1, 1}, {1; 1}, {10; 9}, {10; 25}, {10, 2; 2, 1}, 1,)

returns {0; 5}

minMC(f(x), x, v, from, to, points)

1. Purpose

Performs n‑dimensional minimization of a given function using the Monte‑Carlo method. The function generates a number of random points in n dimensions and returns the point for which f(x) is smallest. This method can be used to obtain an initial vector for minSimplex().

2. Arguments

• f(x) — reference to a cell containing a numeric formula with n variables.
• x — reference to a one‑column vector of n cells (numbers or numeric formulas) representing the variables in f(x).
• v — one‑column vector indicating how to use each variable:
  • v[i] = 0 → i‑th variable has a fixed value and is not modified
  • v[i] = 1 → i‑th variable is modified
• from, to — search limits for all x[i].
• points — number of sample n‑dimensional points to generate.

3. Notes and Errors

• Since points are generated randomly for each calculation, results change after each update.
• If f(x) is not a reference to a cell containing a valid numeric formula, or x is not a reference to n non‑empty numeric cells, minMC returns #REF!.

4. Example (RC notation)

The r2c4 cell contains:
=exp((r3c4-1)*r3c4 + (r4c3-2)*r4c3 + (r5c3-3)*r5c3) - 10*exp(-((r3c4-3)*(r3c4-3) + (r4c4-3)*(r4c4-3) + (r5c4-3)*(r5c4-3)))

The r3c4:r5c4 range contains any numbers.
=minMC(r2c4, r3c4:r5c4, {1;1;1}, -5, 5, 10000) returns {0.675, 2.725, 2.960}

5. Example (A1 notation)

The D2 cell contains:
=exp((D3-1)*D3 + (C4-2)*C4 + (C5-3)*C5) - 10*exp(-((D3-3)*(D3-3) + (D4-3)*(D4-3) + (D5-3)*(D5-3)))

The D3:D5 range contains any numbers.
=minMC(D2, D3:D5, {1; 1; 1}, -5, 5, 10000) returns {0.554969, 2.661842, 3.279377}

minSimplex(f(x), x, v, [start], [epsilon], [max_steps], result)

1. Purpose

Performs n‑dimensional minimization of a given function using the downhill simplex method.

2. Arguments

• f(x) — reference to a cell containing a numeric formula with n variables.
• x — reference to a one‑column vector of n cells (numbers or numeric formulas) representing the variables in f(x). Initial values of x can be obtained with minMC().
• v — one‑column vector indicating how to use each variable:
  • v[i] = 0 → i‑th variable has a fixed value and is not modified
  • v[i] = 1 → i‑th variable will be modified
• start — initial simplex size; if 0 or omitted, default is 1e−5.
• epsilon — precision used to evaluate changes in the function value; if 0 or omitted, default is 1e−25.
• max_steps — maximum number of steps; default is 2000.
• result — return mode:
  • 1 → x vector for the found minimum
  • 2 → minimum f(x) value
  • 3 → number of steps performed

3. Errors

• If f(x) is not a reference to a valid numeric formula or x is not a reference to n non‑empty numeric cells, minSimplex returns #REF!.
• If the procedure is not convergent after max_steps steps, the function returns #NUM!.

4. Example (RC notation)

The r2c4 cell contains:
=exp((r3c4-1)*r3c4 + (r4c3-2)*r4c3 + (r5c3-3)*r5c3) - 10*exp(-((r3c4-3)*(r3c4-3) + (r4c4-3)*(r4c4-3) + (r5c4-3)*(r5c4-3)))

The r3c4:r5c4 range contains:
{0.5; 3; 2}

=minSimplex(r2c4, r3c4:r5c4, {1;1;1},,,,1) returns {0.59358762694774; 3.00000004102304; 2.9999999389697}
=minSimplex(r2c4, r3c4:r5c4, {1;1;1},,,,2) returns 0.75509
=minSimplex(r2c4, r3c4:r5c4, {1;1;1},,,,3) returns 275

5. Example (A1 notation)

The D2 cell contains:
=exp((D3-1)*D3 + (C4-2)*C4 + (C5-3)*C5) - 10*exp(-((D3-3)*(D3-3) + (D4-3)*(D4-3) + (D5-3)*(D5-3)))

The D3:D5 range contains:
{0.5; 3; 2}

=minSimplex(D2, D3:D5, {1;1;1},,,,1) returns {0.59358762694774; 3.00000004102304; 2.9999999389697}
=minSimplex(D2, D3:D5, {1;1;1},,,,2) returns 0.75509
=minSimplex(D2, D3:D5, {1;1;1},,,,3) returns 275

root(f(x), x, x0, x1, [epsilon])

1. Purpose

Finds a root of a one‑dimensional equation f(x) = 0 using a modified secant method. The method is convergent if f(x) is continuous on [x0, x1] and f(x0)·f(x1) < 0.

2. Arguments

• f(x) — reference to a cell containing a numeric formula with one variable.
• x — single cell reference representing the variable.
• x0, x1 — initial search limits such that f(x0)·f(x1) < 0.
• epsilon — precision used to evaluate changes in the function value; if 0 or omitted, default is 1e−15.

3. Errors

• If f(x) is not a reference to a valid numeric formula or x is not a reference to a numeric cell, root returns #REF!.

4. Example

The r2c3 cell contains:
=r3c3*r3c3 + sin(r3c3) - 5

=root(r2c3, r3c3, 0, 3, ) returns 2.02521163744482
=root(r2c3, r3c3, -3, 0, ) returns -2.38467666014657