Logical Functions

The previous lesson measured how many cells of a range are counted, and it used a single threshold in every count. The tariff does not have a single threshold: which tier a record falls into is decided by a nested condition that looks at the 10, 25, and 40 m³ boundaries in turn. At every layer a comparison operator and an order are chosen; both are the decision of the person writing, and neither is visible on the sheet.

This lesson distributes the same 60 records into tiers with three different nested conditions, then converts the tier into an amount. Few records stand on a boundary, but they carry the whole weight of the comparison operator.

• BO10. The tariff applies to billed consumption, and billed consumption is rounded to whole m³. Rounding is a decision; it is also what creates the records that sit exactly on a boundary.
• BO11. The tariff table stays on the sheet: J is the tier number, K the upper boundary, L the unit rate multiplier. The boundaries and multipliers are fictional. The same boundary numbers also appear in the parameter block of the earlier lessons; the cost of this duplication is measured below.
• BO12. The ifelse function evaluates the condition and returns one of two branches. When nested, the conditions are tried from left to right and the first branch that holds wins. That makes the order a decision.

Records Exactly on a Boundary

The block below builds the sheet. The generator is called in the same order as in the earlier lessons; what is new is the F column and the tariff table.

// tariff.mjs — spreadsheet MODEL: the model from 01, extended with logical functions.
// The tariff table stays on the sheet; tier boundaries and multipliers are read from cells.
export const SEED = 20260602, ROWS = 60;
const rng = (t) => { let s = t >>> 0;
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };

const REF = /(\$?)([A-Z])(\$?)(\d+)/g;
export const shift = (f, dS, dR) => f.replace(REF, (_, a, s, b, r) =>
  (a ? "$" + s : String.fromCharCode(s.charCodeAt(0) + dS)) + (b ? "$" + r : String(+r + dR)));

// The formulas of this lesson have no ranges; the model resolves only cell references and function calls.
// "ifelse": the JavaScript keyword "if" cannot be a function parameter name.
const FUNCS = {
  ifelse: (k, d, y) => (k ? d : y),       // the condition is evaluated first, the first branch that holds wins
  round: (x) => Math.round(x),
  min: (a, b) => Math.min(a, b),
  max: (a, b) => Math.max(a, b),
};

export function evaluate(H, name) {
  const v = H[name];
  if (v === undefined) return 0;
  if (typeof v !== "string" || v[0] !== "=") return v;
  const expr = v.slice(1).replace(REF, (m) => JSON.stringify(evaluate(H, m.replace(/\$/g, ""))));
  return Function(...Object.keys(FUNCS), `return (${expr});`)(...Object.values(FUNCS));
}

export function buildSheet() {
  const r = rng(SEED), H = {};
  H.A1 = "subscriber"; H.C1 = "consumption"; H.F1 = "billed";
  for (let i = 0; i < ROWS; i++) {
    const s = i + 2;
    H["A" + s] = "AB-" + String(1041 + i * 7).padStart(4, "0");
    r();                                    // the region column from 01: the generator runs in the same order
    H["C" + s] = Math.round((2 + r() * r() * 78) * 10) / 10;
    H["F" + s] = "=round(C" + s + ")";      // billed consumption is rounded to whole m3
  }
  // The parameter block from 01 stays as it is: unit rate H2, fixed charge H3, boundaries H6:H8.
  ["unit rate", "fixed charge", "meter service", "loss share", "tier 1", "tier 2",
    "tier 3", "discount"].forEach((e, j) => { H["G" + (j + 2)] = e; });
  [8.4, 25, 3.5, 1.2, 10, 25, 40, 0.9].forEach((v, j) => { H["H" + (j + 2)] = v; });
  // Tariff table: J tier, K upper boundary (m3), L unit rate multiplier.
  H.J1 = "tier"; H.K1 = "upper bound"; H.L1 = "multiplier";
  [1, 2, 3, 4].forEach((b, j) => { H["J" + (j + 2)] = b; });
  [10, 25, 40, ""].forEach((k, j) => { H["K" + (j + 2)] = k; });
  [1, 1.4, 1.9, 2.6].forEach((c, j) => { H["L" + (j + 2)] = c; });
  return H;
}

if (import.meta.url === `file://${process.argv[1]}`) {
  const H = buildSheet();
  const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j])
    : String(v).padEnd(B[0]))).join("");
  const y = g([8, 13, 12, 14]);
  console.log(y(["cell", "consumption", "billed", "on boundary"]));
  const F = Array.from({ length: ROWS }, (_, i) => evaluate(H, "F" + (i + 2)));
  const BOUNDS = [10, 25, 40];
  for (const s of [2, 3, 30, 36, 45, 61]) console.log(y(["F" + s, H["C" + s].toFixed(1),
    evaluate(H, "F" + s), BOUNDS.includes(evaluate(H, "F" + s)) ? "yes" : "no"]));
  const n = (f) => F.filter(f).length;
  console.log(`\nBilled consumption distribution — tier 1 ${n((v) => v <= 10)}, ` +
    `tier 2 ${n((v) => v > 10 && v <= 25)}, tier 3 ${n((v) => v > 25 && v <= 40)}, ` +
    `tier 4 ${n((v) => v > 40)}`);
  console.log(`Records exactly on a boundary: ` +
    BOUNDS.map((v) => `${v} m3: ${n((x) => x === v)}`).join(", ") +
    ` — total ${n((v) => BOUNDS.includes(v))}`);
}

cell      consumption      billed   on boundary
F2                9.8          10           yes
F3               71.6          72            no
F30              25.4          25           yes
F36              25.0          25           yes
F45              39.9          40           yes
F61              21.2          21            no

Billed consumption distribution — tier 1 20, tier 2 18, tier 3 9, tier 4 13
Records exactly on a boundary: 10 m3: 1, 25 m3: 2, 40 m3: 1 — total 4

Four records sit exactly on a boundary, and all four got there by a different road: F2 was rounded up, F30 down, F36 was already 25, F45 up. Without the rounding none of them would be on a boundary; what creates a boundary question is another decision that has nothing to do with the boundary.

The Same Condition, Three Versions

The tier number is assigned with three nested ifelse calls. The first includes the boundary, the second does not, and the third includes it but tries the conditions in the wrong order.

// tier.mjs — the same nested condition in three versions: <=, <, and the version with the order broken.
import { buildSheet, evaluate, shift, ROWS } from "./tariff.mjs";

const CONDITION = {
  A: "=ifelse(F2<=$K$2,$J$2,ifelse(F2<=$K$3,$J$3,ifelse(F2<=$K$4,$J$4,$J$5)))",
  B: "=ifelse(F2<$K$2,$J$2,ifelse(F2<$K$3,$J$3,ifelse(F2<$K$4,$J$4,$J$5)))",
  C: "=ifelse(F2<=$K$3,$J$3,ifelse(F2<=$K$2,$J$2,ifelse(F2<=$K$4,$J$4,$J$5)))",
};
const H = buildSheet();
const column = (f) => { const K = { ...H };
  for (let i = 0; i < ROWS; i++) K["M" + (i + 2)] = shift(f, 0, i);
  return Array.from({ length: ROWS }, (_, i) => evaluate(K, "M" + (i + 2))); };
const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j])
  : String(v).padEnd(B[0]))).join("");

const D = Object.fromEntries(Object.entries(CONDITION).map(([a, f]) => [a, column(f)]));
const y = g([8, 12, 8, 8, 14]);
console.log(y(["cell", "billed", "A <=", "B <", "C unordered"]));
for (const s of [2, 3, 30, 36, 45, 61]) { const i = s - 2;
  console.log(y(["M" + s, evaluate(H, "F" + s), D.A[i], D.B[i], D.C[i]])); }

const z = g([12, 10, 10, 10, 10, 16]);
console.log("\n" + z(["version", "tier 1", "tier 2", "tier 3", "tier 4", "differs from A"]));
for (const [a, s] of Object.entries(D)) console.log(z([a, ...[1, 2, 3, 4].map((b) =>
  s.filter((v) => v === b).length), s.filter((v, i) => v !== D.A[i]).length]));
console.log(`\nNested condition depth ${(CONDITION.A.match(/ifelse\(/g) ?? []).length}; ` +
  `the boundaries are read from cells K2:K4.`);

cell          billed    A <=     B <   C unordered
M2                10       1       2             2
M3                72       4       4             4
M30               25       2       3             2
M36               25       2       3             2
M45               40       3       4             3
M61               21       2       2             2

version         tier 1    tier 2    tier 3    tier 4  differs from A
A                   20        18         9        13               0
B                   19        17        10        14               4
C                    0        38         9        13              20

Nested condition depth 3; the boundaries are read from cells K2:K4.

Version B differs from A in exactly 4 records, and those four are the four records on the boundary; for the remaining 56 records < and <= say the same thing. That the difference looks small is no assurance: the difference is always as large as the number of records on the boundary, and that number depends on the data.

Version C shows a different class of error. The boundaries, the multipliers, and the operators are correct; only the 25 boundary was tried before the 10 boundary. Because the first branch that holds wins, every record below 10 m³ was found “below 25” at the first layer and written to the second tier. The first tier emptied completely: 20 records fell into the wrong tier. There is neither an error value nor a blank cell on the sheet, only a tier number that is never seen.

From Tier to Amount

A tier is not reported on its own; it is converted into an amount. The block below compares three versions of the amount, prints the boundary difference at the bill level, and scans the three versions that produce the same total by where the decision stands.

// amount.mjs — the tier decision is converted to an amount: flat-rate and tiered calculation, two boundary versions.
import { buildSheet, evaluate, shift, ROWS } from "./tariff.mjs";

const MULTIPLIER = (o) => `ifelse(F2${o}$K$2,$L$2,ifelse(F2${o}$K$3,$L$3,ifelse(F2${o}$K$4,$L$4,$L$5)))`;
const AMOUNT = {
  "flat <=": "=$H$2*F2*" + MULTIPLIER("<=") + "+$H$3",
  "flat <": "=$H$2*F2*" + MULTIPLIER("<") + "+$H$3",
  tiered: "=$H$2*(min(F2,$K$2)*$L$2+max(0,min(F2,$K$3)-$K$2)*$L$3" +
    "+max(0,min(F2,$K$4)-$K$3)*$L$4+max(0,F2-$K$4)*$L$5)+$H$3",
};
const H = buildSheet();
const column = (f) => { const K = { ...H };
  for (let i = 0; i < ROWS; i++) K["N" + (i + 2)] = shift(f, 0, i);
  return Array.from({ length: ROWS }, (_, i) => evaluate(K, "N" + (i + 2))); };
const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j])
  : String(v).padEnd(B[0]))).join("");

const D = Object.fromEntries(Object.entries(AMOUNT).map(([a, f]) => [a, column(f)]));
const t = (d) => d.reduce((a, b) => a + b, 0);
// The threshold is 0.005 fee units: amounts are not distinguished below a cent.
const EPSILON = 0.005;
const y = g([12, 12, 12, 12]);
console.log(y(["version", "total", "vs flat <=", "changed"]));
for (const [a, d] of Object.entries(D)) console.log(y([a, t(d).toFixed(2),
  (t(d) - t(D["flat <="])).toFixed(2), d.filter((v, i) => Math.abs(v - D["flat <="][i]) >= EPSILON).length]));

const z = g([10, 12, 12, 12, 10]);
console.log("\n" + z(["cell", "billed", "flat <=", "flat <", "diff %"]));
let largest = 0;
for (let i = 0; i < ROWS; i++) {
  const a = D["flat <="][i], b = D["flat <"][i];
  if (Math.abs(a - b) < EPSILON) continue;
  largest = Math.max(largest, 100 * Math.abs(b - a) / a);
  console.log(z(["N" + (i + 2), evaluate(H, "F" + (i + 2)), a.toFixed(2), b.toFixed(2),
    (100 * (b - a) / a).toFixed(1)]));
}
console.log(`\nThe boundary decision moved the total by ` +
  `${(100 * Math.abs(t(D["flat <"]) - t(D["flat <="])) / t(D["flat <="])).toFixed(2)}%, ` +
  `and moved a single bill by up to ${largest.toFixed(1)}%.`);
console.log(`Tiered calculation is not affected by the boundary version: no condition, only bands.`);

// Read-back scan: three versions produce the same amount, the place of the decision differs.
const MULT = (o, K, L) => `ifelse(F2${o}${K[0]},${L[0]},ifelse(F2${o}${K[1]},${L[1]},` +
  `ifelse(F2${o}${K[2]},${L[2]},${L[3]})))`;
const CELLS = ["$K$2", "$K$3", "$K$4"], MULTS = ["$L$2", "$L$3", "$L$4", "$L$5"];
const VERSIONS = {
  single: { N: "=$H$2*F2*" + MULT("<=", CELLS, MULTS) + "+$H$3" },
  "two columns": { M: "=" + MULT("<=", CELLS, MULTS), N: "=$H$2*F2*M2+$H$3" },
  embedded: { N: "=$H$2*F2*" + MULT("<=", [10, 25, 40], [1, 1.4, 1.9, 2.6]) + "+$H$3" },
};
const assemble = (S, w) => { const K = { ...S };
  for (let i = 0; i < ROWS; i++) for (const [c, f] of Object.entries(w)) K[c + (i + 2)] = shift(f, 0, i);
  return K; };
const Z = { ...H, K3: 30 };                              // second boundary 25 -> 30 m3
const s = g([12, 12, 10, 8, 10]);
console.log("\n" + s(["version", "total", "depth", "steps", "follows"]));
for (const [name, w] of Object.entries(VERSIONS)) {
  const K = assemble(H, w), W = assemble(Z, w);
  const N = Array.from({ length: ROWS }, (_, i) => evaluate(K, "N" + (i + 2)));
  const N2 = Array.from({ length: ROWS }, (_, i) => evaluate(W, "N" + (i + 2)));
  // The cell to read in order to read the tier back: if the amount formula holds the condition, the
  // decision is written in no cell, and the three comparisons are evaluated again.
  const text = Object.values(w).join("");
  console.log(s([name, N.reduce((a, b) => a + b, 0).toFixed(2), (text.match(/ifelse\(/g) ?? []).length,
    /ifelse\(/.test(w.N) ? 0 : 1, N.filter((v, i) => Math.abs(v - N2[i]) >= EPSILON).length]));
}
const both = [10, 25, 40].filter((v) => Object.values(H).filter((x) => x === v).length > 1);
console.log(`\nThe tier boundaries stand in two places on the sheet (H6:H8 and K2:K4): ` +
  `${both.join(", ")} m3. When K3 changes, H7 does not.`);

version            total  vs flat <=     changed
flat <=         26219.52        0.00           0
flat <          26698.32      478.80           4
tiered          19507.92    -6711.60          40

cell            billed     flat <=      flat <    diff %
N2                  10      109.00      142.60      30.8
N30                 25      319.00      424.00      32.9
N36                 25      319.00      424.00      32.9
N45                 40      663.40      898.60      35.5

The boundary decision moved the total by 1.83%, and moved a single bill by up to 35.5%.
Tiered calculation is not affected by the boundary version: no condition, only bands.

version            total     depth   steps   follows
single          26219.52         3       0         6
two columns     26219.52         3       1         6
embedded        26219.52         3       0         0

The tier boundaries stand in two places on the sheet (H6:H8 and K2:K4): 10, 25, 40 m3. When K3 changes, H7 does not.

The first table gives the first number of the lesson: 26,219.52 fee units. When the boundary comparison changes it becomes 26,698.32, a difference of 478.80 — 1.83%, small enough to slip past a control total. The second table shows the same difference at the record level: the bills of four subscribers changed by between 30.8% and 35.5%. A decision that is small in the total is not small in the individual records.

The tiered calculation gives a third number: 19,507.92, which is 6,711.60 less than the flat-rate calculation and differs in 40 of the 60 records. Both are defensible: one charges the whole consumption at the multiplier of the tier reached, the other charges each band at its own multiplier. The tiered version has no condition at all; the bands are cut with min and max, so the boundary operator decision is never asked in that calculation. The cost of a decision depends on what the decision next to it is.

The last table is the second number of the lesson. All three versions produce the same total, and all three have a condition depth of 3. The distinction is in the last two columns: in the two columns version the multiplier is written to cell M, and 1 cell is read to learn the tier of a record; in the others the tier stands in no cell — 0 steps, nowhere to read. When the boundary was raised to 30 m³, 6 amounts followed in the two versions that refer to the cell and 0 in the embedded one; the latter keeps showing 26,219.52, and that is now wrong.

The closing line writes down the contradiction of the sheet: 10, 25, and 40 stand both in the parameter block and in the tariff table. The formulas refer to the tariff table, but a person looking at the sheet can update the parameter block and no calculation will move. Two copies of the same decision are not one decision.

Summary

• In a nested condition the layers are tried from left to right and the first branch that holds wins; the order is a decision. When the order was broken, 20 records fell into the wrong tier and the first tier emptied completely.
• The boundary comparison affects only the records standing on a boundary: writing < instead of <= moved 4 records. What put those four on the boundary is not the boundary but the rounding decision.
• The same decision moved the total by 1.83% but moved four bills by between 30.8% and 35.5%. A check that looks at the total cannot see this difference.
• Of the three versions that produce the same total, only one has the tier written in a cell; in the others the decision is inside the 3-layer condition and there is no place on the sheet to read it.
• When the same boundary stands in two places on the sheet, one can change while the other stays; the second copy does not strengthen the decision, it makes it ambiguous.

Next Step

Up to here every comparison was numeric. The other half of the sheet is text: the subscriber code, the name, the region. In the next step the field visit records are matched with the subscriber records by subscriber code. The codes in the two tables are the same to the eye, but one carries a trailing space that cannot be seen and one is written in uppercase. The next lesson counts how many of these records fail to match, measures how many matches the trim and case conversion decisions bring back, and shows where those decisions stand on the sheet.