--- title: 'Analysis with Spreadsheets' source: 'https://academia.sh/en/courses/analysis-with-spreadsheets' language: en updated: '2026-10-07T04:31:09+00:00' license: 'CC BY-SA 4.0' lessons: 11 outcomes: - 'Transforms data with formulas and functions and writes down how much each transformation shifts the number' - 'Produces multidimensional summaries with pivot tables and shows that the breakdown decision changes the ranking' - 'Determines the limit of the spreadsheet by counting how many decisions can be read back' --- # Analysis with Spreadsheets Measuring whether the person who does a calculation a second time will find the same number: the two numbers relative and absolute references produce when copied, a counting function giving three different results for a blank cell, a zero and a number written as text, how many records a boundary comparison assigns to the wrong tier in tariff brackets, the record that fails to match because of an invisible space or a difference in letter case, a day-counting decision giving two different durations, the lookup that breaks when a column is inserted, the deduplication criterion changing the number of remaining rows, filling and exclusion decisions giving three values for the same average, the ranking changing when the breakdown changes, how many times larger the axis origin makes a difference look, and how many decisions can be read back from the spreadsheet. ## Basic Operations Counting how much every cell-level decision moves the number and whether it can be read back from the sheet: the difference relative and absolute references produce when copied and how many cells a wrong reference spreads to, a count function giving three different results for a blank cell, a zero and a number written as text, the records a boundary comparison sends to the wrong tier in nested conditions, the record blocked from matching by an invisible space and a difference in letter case, a day-counting decision giving two durations, the vertical lookup that breaks when a column is inserted and the match-index pair that does not. ### Worksheet Structure Source: https://academia.sh/en/courses/analysis-with-spreadsheets/worksheet-structure A single amount formula is copied to 60 rows, and when the reference decision changes the total drops from 13,427.16 fee units to 3,354.98: the relative reference spreads wrongly to 59 cells, 52 of them zero and 7 a wrong number that reads like an amount. The third version, written with an embedded literal, gives the same total, but when the unit rate rises from 8.4 to 9.1 it follows in 0 cells instead of 60. Of the 8 parameters on the sheet, 6 enter no formula. The Introduction to Data Analytics course closed with a question: nine lessons showed that decisions move the number, but every decision was made by hand and none of them is written down anywhere; will a person who does the same calculation a second time find the same number? The spreadsheet is the first answer to that question. The calculation no longer lives in one person's head but in a cell, and a cell can be recalculated. This course measures how much of the answer it holds. The measure has two parts, and in every lesson two numbers sit side by side. The first carries over from the previous course: the number and the decision that produced it. The second is this course's own measure: **decision recoverability** — in how many steps the second person looking at the sheet can read that decision back, and how many decisions cannot be read back at all. This lesson sets up both on a single formula. - **BO1.** The worksheet here is a **model**: cells are a map, formulas are a short string, ranges are a slice function. The model does not imitate any application; both the copy rule and the evaluation rule are written out explicitly below. - **BO2.** The dataset is the **regional measurement network** of the Introduction to Data Analytics course: subscriber records, meter readings, field visits, tariff tiers. Both the network and the records are **fictional**; the sheet here is a field list of 60 subscriptions and is generated from its own seed. - **BO3.** A cell name is a column letter plus a row number. A reference is shifted as far as it is copied if it is **relative**, and does not move if it is **absolute** (marked with `$`). In a mixed reference only the marked part stays fixed. - **BO4.** A blank cell is read as `0` in a numeric context in this model. This is a decision and it affects the result directly; the next lesson separately measures whether a blank cell is counted. - **BO5.** A decision counts as **readable back** if the second person can find, by reading the formula text of the cell, where the decision is held and what its value is. A constant number embedded inside a formula is not readable back — the number is visible, but where it came from is not. - **BO6.** The charge is a **fee unit**; the scale is the model's own scale. #### Sheet, Cell, Range The block below builds the model. It has three parts: the cell map, the copy rule applied by the `shift` function, and the evaluation applied by the `evaluate` function. Evaluation is recursive — one formula can refer to another formula. ```js // sheet.mjs — spreadsheet MODEL: cells are a map, a formula is a string, a range is a slice // function. The sheet and the records are fictional; no application's behavior is imitated. export const SEED = 20260602, ROWS = 60; const rng = (t) => { let s = t >>> 0; return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; }; const REGION = ["north", "south", "east", "west", "central"]; export const REF = /(\$?)([A-Z])(\$?)(\d+)/g; const parse = (a) => ({ s: a.charCodeAt(a[0] === "$" ? 1 : 0) - 65, r: +a.replace(/\D/g, "") }); const cellName = (s, r) => String.fromCharCode(65 + s) + r; // Copy rule: a relative reference shifts as far as it is copied, an absolute reference ($) does not. 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))); const FUNCS = { sum: (d) => d.reduce((a, b) => a + b, 0) }; const RANGE = /(\$?[A-Z]\$?\d+):(\$?[A-Z]\$?\d+)/g; const expand = (a, b) => { const x = parse(a), y = parse(b), l = []; for (let s = x.s; s <= y.s; s++) for (let r = x.r; r <= y.r; r++) l.push(cellName(s, r)); return l; }; export function evaluate(H, name) { const v = H[name]; if (v === undefined) return 0; // a blank cell is read as 0 in this model if (typeof v !== "string" || v[0] !== "=") return v; const expr = v.slice(1) .replace(RANGE, (_, a, b) => "[" + expand(a, b).map((c) => evaluate(H, c)).join(",") + "]") .replace(REF, (m) => String(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.B1 = "region"; H.C1 = "consumption"; H.E1 = "amount"; for (let i = 0; i < ROWS; i++) { const s = i + 2; H["A" + s] = "AB-" + String(1041 + i * 7).padStart(4, "0"); H["B" + s] = REGION[Math.floor(r() * 5)]; H["C" + s] = Math.round((2 + r() * r() * 78) * 10) / 10; } // Parameter block: label on the left, value on the right. Tariff tiers are 10/25/40 m3. H.G1 = "parameter"; H.H1 = "value"; ["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; }); return H; } if (import.meta.url === `file://${process.argv[1]}`) { const H = buildSheet(); const filled = Object.keys(H).length; console.log(`seed ${SEED}: ${ROWS} subscriber rows, ${filled} filled cells`); console.log("parameter block H2:H9 = " + expand("H2", "H9").map((c) => H[c]).join(", ") + "\n"); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const y = g([8, 12, 13]); console.log(y(["cell", "subscriber", "consumption"])); for (const s of [2, 3, 4, 60, 61]) console.log(y(["C" + s, H["A" + s], H["C" + s].toFixed(1)])); console.log(`\nC2:C61 total ${evaluate({ ...H, X1: "=sum(C2:C61)" }, "X1").toFixed(1)} m3`); console.log("shift('=C2*$H$2+H3', 0, 1) -> " + shift("=C2*$H$2+H3", 0, 1)); } ``` ``` seed 20260602: 60 subscriber rows, 202 filled cells parameter block H2:H9 = 8.4, 25, 3.5, 1.2, 10, 25, 40, 0.9 cell subscriber consumption C2 AB-1041 9.8 C3 AB-1048 71.6 C4 AB-1055 6.7 C60 AB-1447 3.0 C61 AB-1454 21.2 C2:C61 total 1419.9 m3 shift('=C2*$H$2+H3', 0, 1) -> =C3*$H$2+H4 ``` The last line holds the whole lesson. When the same formula was copied one row down, `$H$2` stayed in place and `H3` became `H4`. Copying is a text operation: the formula inside the cell does not stay the same but is written out shifted. The person writing decides which reference will shift, and that decision is made with a single mark. #### The Same Calculation, Three Reference Decisions The calculation is one: the amount is consumption times the unit rate plus the fixed charge. All three versions of it look defensible. The first refers to the parameters absolutely, the second refers to them relatively, and the third writes the numbers into the formula. ```js // copy.mjs — the same calculation is written with three reference decisions and copied to 60 rows. import { buildSheet, evaluate, shift, ROWS } from "./sheet.mjs"; const DECISIONS = [ { name: "A: absolute ($H$2, $H$3)", f: "=C2*$H$2+$H$3" }, { name: "B: relative (H2, H3)", f: "=C2*H2+H3" }, { name: "C: embedded literal", f: "=C2*8.4+25" }, ]; // A formula is written to E2 and copied down to E61: each copy is shifted by the row difference. const fill = (H, f) => { const K = { ...H }; for (let i = 0; i < ROWS; i++) K["E" + (i + 2)] = shift(f, 0, i); return K; }; const column = (K) => Array.from({ length: ROWS }, (_, i) => evaluate(K, "E" + (i + 2))); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const H = buildSheet(); const D = DECISIONS.map((k) => column(fill(H, k.f))); const y = g([8, 13, 12, 12, 12]); console.log(y(["cell", "consumption", "A amount", "B amount", "C amount"])); for (const s of [2, 3, 5, 9, 10, 30, 61]) { const i = s - 2; console.log(y(["E" + s, H["C" + s].toFixed(1), D[0][i].toFixed(2), D[1][i].toFixed(2), D[2][i].toFixed(2)])); } const t = (d) => d.reduce((a, b) => a + b, 0); // Floating-point comparison is done with a threshold: amounts are not distinguished below a cent, // so two values closer than 0.005 fee units count as "the same". const EPSILON = 0.005; const same = (a, b) => Math.abs(a - b) < EPSILON; const z = g([26, 12, 8, 10, 8]); console.log("\n" + z(["decision", "total", "wrong", "zero", "visible"])); DECISIONS.forEach((k, a) => { const wrong = D[a].filter((v, i) => !same(v, D[0][i])).length; const zero = D[a].filter((v) => same(v, 0)).length; console.log(z([k.name, t(D[a]).toFixed(2), wrong, zero, wrong - zero])); }); console.log(`\nA single formula was written to E2 and copied to ${ROWS - 1} cells.`); ``` ``` cell consumption A amount B amount C amount E2 9.8 107.32 107.32 107.32 E3 71.6 626.44 1793.50 626.44 E5 26.8 250.12 42.16 250.12 E9 5.5 71.20 4.95 71.20 E10 49.1 437.44 0.00 437.44 E30 25.4 238.36 0.00 238.36 E61 21.2 203.08 0.00 203.08 decision total wrong zero visible A: absolute ($H$2, $H$3) 13427.16 0 0 0 B: relative (H2, H3) 3354.98 59 52 7 C: embedded literal 13427.16 0 0 0 A single formula was written to E2 and copied to 59 cells. ``` The number to report is 13,427.16 fee units. Decision B produced 3,354.98 from the same calculation; the difference is 10,072.18, three quarters of the correct total. A single mark moved that much. The spread columns say something more important. The formula written is **one**; the wrong cell is also a single cell. But copying multiplied that cell into 59 cells, and all 59 produced a wrong value. Of these, 52 are zero — they stand out. The remaining 7 are not zero: cell `E5` reads 42.16 instead of 250.12, because the shifting reference multiplied the consumption by the loss share rather than the unit rate, and added the boundary of the first tariff tier instead of the fixed charge. 42.16 reads like the bill of a low-consumption subscriber and cannot be told apart from the neighboring rows. **The visibility of an error is measured not by its size but by what it resembles.** Decision C gave the same total as A: 13,427.16. The two columns are equal cell by cell. A comparison of results cannot tell these two versions apart. #### Who Follows When a Parameter Changes What tells them apart is not the result but how the sheet reacts to change. The unit rate is raised from 8.4 to 9.1 by updating a single cell. ```js // decision.mjs — how many cells follow when a parameter changes, and how many decisions the sheet reads back. import { buildSheet, evaluate, shift, REF, ROWS } from "./sheet.mjs"; const FORMULA = { A: "=C2*$H$2+$H$3", B: "=C2*H2+H3", C: "=C2*8.4+25" }; const fill = (H, f) => { const K = { ...H }; for (let i = 0; i < ROWS; i++) K["E" + (i + 2)] = shift(f, 0, i); K.E62 = "=sum(E2:E61)"; return K; }; const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const H = buildSheet(), RAISE = { ...H, H2: 9.1 }; // unit rate 8.4 -> 9.1 const y = g([10, 12, 13, 12, 11]); console.log(y(["decision", "total", "after raise", "difference", "followers"])); for (const [name, f] of Object.entries(FORMULA)) { const K = fill(H, f), Z = fill(RAISE, f); const before = Array.from({ length: ROWS }, (_, i) => evaluate(K, "E" + (i + 2))); const after = Array.from({ length: ROWS }, (_, i) => evaluate(Z, "E" + (i + 2))); // The threshold is 0.005 fee units: a difference below it cannot be told apart by rounding. const moved = before.filter((v, i) => Math.abs(v - after[i]) >= 0.005).length; console.log(y([name, evaluate(K, "E62").toFixed(2), evaluate(Z, "E62").toFixed(2), (evaluate(Z, "E62") - evaluate(K, "E62")).toFixed(2), moved])); } // Sheet scan: references and embedded literals are extracted from every formula text. const scan = (K) => { const formulas = Object.entries(K).filter(([, v]) => typeof v === "string" && v[0] === "="); const referenced = new Set(), literal = new Map(); for (const [, v] of formulas) { for (const m of v.matchAll(REF)) referenced.add(m[0].replace(/\$/g, "")); for (const s of v.replace(REF, "").match(/\d+(\.\d+)?/g) ?? []) literal.set(s, (literal.get(s) ?? 0) + 1); } const param = Array.from({ length: 8 }, (_, j) => "H" + (j + 2)); return { formulas: formulas.length, used: param.filter((c) => referenced.has(c)), dead: param.filter((c) => !referenced.has(c)), literal }; }; const z = g([10, 10, 28, 6, 18]); console.log("\n" + z(["decision", "formulas", "used", "dead", "embedded literal"])); for (const [name, f] of Object.entries(FORMULA)) { const t = scan(fill(H, f)); console.log(z([name, t.formulas, t.used.join(","), t.dead.length, [...t.literal].map(([s, n]) => `${s}x${n}`).join(" ")])); } const t = scan(fill(H, FORMULA.A)); console.log(`\nThe parameter block holds 8 values, the formulas reference ${t.used.length} of them; ` + `${t.dead.length} (${t.dead.join(", ")}) sit on the sheet but enter no ` + `calculation.`); ``` ``` decision total after raise difference followers A 13427.16 14421.09 993.93 60 B 3354.98 3361.84 6.86 1 C 13427.16 13427.16 0.00 0 decision formulas used dead embedded literal A 61 H2,H3 6 B 61 H2,H3,H4,H5,H6,H7,H8,H9 0 C 61 8 8.4x60 25x60 The parameter block holds 8 values, the formulas reference 2 of them; 6 (H4, H5, H6, H7, H8, H9) sit on the sheet but enter no calculation. ``` The second number of this lesson is in the last column. In decision A, changing one cell moved **60** cells; in decision C, **0** cells. The C sheet still shows 13,427.16 after the raise, and that number is now wrong — yet nothing on the sheet marks it, because nothing has changed. The scan columns state where the difference lies. In A, two decisions can be read back from the formula text: whoever reads `$H$2` goes to cell `H2`, reads the `G2` label next to it, and finds the unit rate. **Two steps.** In C the same decision repeats as 8.4 in 60 separate cells; a number is visible, its source is not. If the unit rate is to change, all 60 formulas have to be read one by one, and which 8.4 is the unit rate and which is something else can only be guessed. **There is no read-back step.** The last line says a third thing. The sheet holds 8 parameters and the formulas reference only 2 of them; 6 parameters, the tariff tiers included, enter no calculation. They are the residue of decisions once made on the sheet. Whoever looks at it assumes all of them are in use. **A number standing on the sheet is not a number in use.** #### The Two Measures of a Decision This lesson produced two numbers, and a table cannot be delivered without reporting both. The first is the amount total: 13,427.16 fee units, and 3,354.98 when the reference decision changes. The second is recoverability: in decision A the raise reached 60 cells, in decision C 0 cells; in A the parameter decision is read back in two steps, in C it cannot be read at all. Without the second number the first is incomplete, because the same 13,427.16 came out of two different sheets, and those two sheets are not the same sheet. The course's rule starts here: **a spreadsheet is a calculation tool, not a record-keeping tool; a decision embedded in a cell is an unwritten decision.** #### Summary - A worksheet is a map of cells; a formula is a string, and when copied it is written out shifted, not as it was. A relative reference shifts, an absolute reference does not. - When the same calculation was written with three reference decisions, the total moved between 13,427.16 and 3,354.98; the difference is three quarters of the correct total. - A single wrong reference spread by copying to 59 cells; 52 of them give zero and stand out, 7 read like an amount. The visibility of an error is measured not by its size but by what it resembles. - A comparison of results does not separate the two versions: the sheet written with an embedded literal gives the same total, but when the unit rate changes it follows in 0 cells instead of 60. - The recoverability of a decision is measured in steps: two steps with an absolute reference, none with an embedded literal. Of the 8 parameters on the sheet, 6 enter no formula, and whoever looks at it cannot see that. #### Next Step This lesson counted which cells a calculation spread to, but it did not ask which cells the calculation **counted**. When `sum(E2:E61)` is written, the range holds 60 cells; how many of them are counted? Some of the meter readings are blank, some are zero, and some are text that looks like a number, and the three resemble each other inside a cell. The next lesson applies three counting functions to the same column and prints three different results; it then measures which decision the denominator of the same column's mean depends on. ### Basic Functions Source: https://academia.sh/en/courses/analysis-with-spreadsheets/basic-functions Four counting functions are applied to a single 60-cell range and four different numbers come out: 53 filled cells, 39 numbers, 29 values above zero, 7 blank cells. The total of the same column is 708.5 m³; because 14 numbers written as text are silently skipped, the real total is 967.2 m³, a difference of 26.7%. The denominator of the mean swings between 11.81 and 24.43 across four defensible decisions, a factor of 2.07; in 1 of six formulas the denominator is not visible at all, in 3 the decision is an embedded literal, and in only 1 is there a reference to a parameter cell. The previous lesson counted which cells a calculation spread to, but it did not ask which cells the calculation **counted**. When `sum(E2:E61)` is written, the range holds 60 cells; how many of them are summed? The length of a range is one number, the cells counted in the range are another, and the two are not equal. This lesson adds a column to the same sheet: the meter reading. In the reading column three different kinds of "nothing" sit side by side. When four counting functions are applied to the same range, four different numbers come out; then one of those numbers becomes the denominator of a mean, and the reported value swings by more than a factor of two. - **BO7.** Range functions see the **raw** content of a cell: a blank cell is `null`, text stays text. Reading a blank cell as `0` in a numeric context (BO4) continues; the two rules live together in the same model and are written as separate functions in the code below. - **BO8.** The three kinds of nothing in the reading column: a **blank cell** — no reading could be taken; a **zero** — the meter did not turn, so a real measurement; a **number stored as text** — a string that came from an export with a comma as the decimal separator. The column is fictional and is generated from its own seed. - **BO9.** A criterion is a **string** (such as `">0"`) and the model applies it with the `crit` function. Whether a cell is a number or text is decided by its **type**, not by how it looks. #### Three Different Kinds of Nothing in a Range The block below extends the model of the previous lesson. The sheet generator is the same and is called in the same order, so the consumption column does not change. The reading column is generated from a separate sequence. ```js // counting.mjs — spreadsheet MODEL: the model from 01, extended with range functions. // A range function sees the RAW content of a cell: a blank cell is null, text stays text. 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; const parse = (a) => ({ s: a.charCodeAt(a[0] === "$" ? 1 : 0) - 65, r: +a.replace(/\D/g, "") }); const cellName = (s, r) => String.fromCharCode(65 + s) + r; const RANGE = /(\$?[A-Z]\$?\d+):(\$?[A-Z]\$?\d+)/g; const expand = (a, b) => { const x = parse(a), y = parse(b), l = []; for (let s = x.s; s <= y.s; s++) for (let r = x.r; r <= y.r; r++) l.push(cellName(s, r)); return l; }; // Criterion string: a threshold such as ">0" or a plain value. A text cell stays text. const crit = (o, v) => { const m = /^([<>]=?|=)?(.*)$/.exec(o), s = +m[2]; if (typeof v !== "number") return m[1] === undefined && v === m[2]; return m[1] === ">" ? v > s : m[1] === "<" ? v < s : m[1] === ">=" ? v >= s : m[1] === "<=" ? v <= s : v === s; }; const countFilled = (d) => d.filter((v) => v !== null && v !== "").length; const numbers = (d) => d.filter((v) => typeof v === "number"); const FUNCS = { sum: (d) => numbers(d).reduce((a, b) => a + b, 0), // text and blank cells are skipped count: countFilled, // filled cell: text is counted too countnumbers: (d) => numbers(d).length, // only cells of number type countblank: (d) => d.length - countFilled(d), average: (d) => numbers(d).reduce((a, b) => a + b, 0) / numbers(d).length, countif: (d, o) => d.filter((v) => crit(o, v)).length, sumif: (d, o, t) => t.filter((_, i) => crit(o, d[i])) .filter((v) => typeof v === "number").reduce((a, b) => a + b, 0), }; export function raw(H, name) { const v = H[name]; if (v === undefined) return null; // a blank cell appears as null in a range return typeof v === "string" && v[0] === "=" ? evaluate(H, name) : v; } export function evaluate(H, name) { const v = H[name]; if (v === undefined) return 0; // in a numeric context a blank cell is 0 (BO4) if (typeof v !== "string" || v[0] !== "=") return v; const expr = v.slice(1) .replace(RANGE, (_, a, b) => "[" + expand(a, b).map((c) => JSON.stringify(raw(H, c))).join(",") + "]") .replace(REF, (m) => JSON.stringify(evaluate(H, m.replace(/\$/g, "")))); return Function(...Object.keys(FUNCS), `return (${expr});`)(...Object.values(FUNCS)); } // This is the sheet from 01: the A/B/C columns are generated from the same seed in the same order, // so the consumption values (the C2:C61 total of 1419.9 m3) do not change. The reading column D is // a separate sequence. export function buildSheet() { const r = rng(SEED), H = {}; H.A1 = "subscriber"; H.C1 = "consumption"; H.D1 = "reading"; 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; } // Reading column: three different kinds of "nothing" sit side by side. const o = rng(SEED + 1); for (let i = 0; i < ROWS; i++) { const s = i + 2, t = H["C" + s], p = o(); if (p < 0.14) continue; // blank cell: no reading could be taken if (p < 0.27) H["D" + s] = 0; // the meter did not turn: a real zero else if (p < 0.44) H["D" + s] = String(t).replace(".", ","); // number stored as text else H["D" + s] = t; } // The parameter block is the same as in 01: the tariff tiers are in 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; }); return H; } if (import.meta.url === `file://${process.argv[1]}`) { const H = buildSheet(); const kind = (v) => v === undefined ? "blank" : typeof v === "number" ? "number" : "text"; 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, 10, 8]); console.log(y(["cell", "consumption", "reading", "type"])); for (const s of [2, 3, 5, 9, 61]) console.log(y(["D" + s, H["C" + s].toFixed(1), H["D" + s] === undefined ? "" : H["D" + s], kind(H["D" + s])])); const d = Array.from({ length: ROWS }, (_, i) => H["D" + (i + 2)]); const n = (f) => d.filter(f).length; console.log(`\nD2:D61 range length ${ROWS}; blank ${n((v) => v === undefined)}, ` + `zero ${n((v) => v === 0)}, text ${n((v) => typeof v === "string")}, ` + `numbers above zero ${n((v) => typeof v === "number" && v > 0)}`); } ``` ``` cell consumption reading type D2 9.8 9,8 text D3 71.6 0 number D5 26.8 26.8 number D9 5.5 blank D61 21.2 21.2 number D2:D61 range length 60; blank 7, zero 10, text 14, numbers above zero 29 ``` The last column is a convenience of the model; in a real sheet the type is not written anywhere. `9,8` shows in cell `D2` and `26.8` in cell `D5`, and both read like a reading. The difference between `D9` and `D3` is subtler: one is blank, the other is zero. Yet `D3` is a measurement — the meter did not turn — while `D9` is the absence of a measurement. #### The Same Range, Four Counting Functions "How many readings are there this period" has no single answer. The block below gives the same range to four different counting functions, then measures what the text cells do to the sum. ```js // functions.mjs — the same range is given to four counting functions, four different results come back. import { buildSheet, evaluate, ROWS } from "./counting.mjs"; const H = buildSheet(); const FORMULAS = [ ["=count(D2:D61)", "filled cells (text included)"], ["=countnumbers(D2:D61)", "cells of number type"], ["=countif(D2:D61,\">0\")", "numbers above zero"], ["=countblank(D2:D61)", "blank cells"], ]; const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const y = g([28, 8, 30]); console.log(`D2:D61 contains ${ROWS} cells.\n`); console.log(y(["formula", "result", "what it counted"])); for (const [f, what] of FORMULAS) console.log(y([f, evaluate({ ...H, X1: f }, "X1"), what])); // Numbers stored as text do not enter the sum. What would happen if the same column were converted to numbers. const T = { ...H }; for (let i = 0; i < ROWS; i++) { const c = "D" + (i + 2), v = T[c]; if (typeof v === "string") T[c] = +v.replace(",", "."); } const rawSum = evaluate({ ...H, X1: "=sum(D2:D61)" }, "X1"); const converted = evaluate({ ...T, X1: "=sum(D2:D61)" }, "X1"); console.log(`\nsum(D2:D61) is ${rawSum.toFixed(1)} m3 on the raw column, ${converted.toFixed(1)} m3 ` + `once the text is converted to numbers; difference ${(converted - rawSum).toFixed(1)} m3 ` + `(${(100 * (converted - rawSum) / converted).toFixed(1)}%).`); console.log(`In 01 the C2:C61 total was 1419.9 m3; the reading column of the same rows ` + `shows ${rawSum.toFixed(1)} m3.`); ``` ``` D2:D61 contains 60 cells. formula result what it counted =count(D2:D61) 53 filled cells (text included) =countnumbers(D2:D61) 39 cells of number type =countif(D2:D61,">0") 29 numbers above zero =countblank(D2:D61) 7 blank cells sum(D2:D61) is 708.5 m3 on the raw column, 967.2 m3 once the text is converted to numbers; difference 258.7 m3 (26.7%). In 01 the C2:C61 total was 1419.9 m3; the reading column of the same rows shows 708.5 m3. ``` All four are correct and each answers a different question. 53 is the number of cells with something written in them. 39 is the number of cells of number type. 29 is the number of cells that hold a reading above zero. 7 gives the subscribers who were never read. The value that goes into the report as "the number of readings this period" is one of these four, and which one depends on **the definition of the question**, not on the name of the function. The sum row does something quieter. The sum function skips text cells and does not say so; it gives no error and produces no warning, it leaves 14 values out of the calculation. The real total of the column is 967.2 m³, while the sheet reads 708.5 m³. The difference is 258.7 m³, which is 26.7% of the real total. An error that was displayed would be seen; **a skip is not seen.** The previous lesson found the consumption total of the same rows to be 1419.9 m³. In every row where it is filled, the reading column carries the **same** value as consumption; so the difference between 708.5 and 1419.9 is not a measurement difference but a **counting** difference, and no cell says so. #### Where the Denominator of the Mean Stands The sum depended on one decision; the mean depends on two, because its denominator is chosen separately. The block below divides the same numerator by four denominators, then compares two versions of a threshold and scans six formulas by where the decision stands. ```js // denominator.mjs — the same mean with four denominators; and whether the criterion and denominator decisions can be read back. import { buildSheet, evaluate } from "./counting.mjs"; const H = buildSheet(); const read = (f) => evaluate({ ...H, X1: f }, "X1"); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const MEANS = [ ["=average(D2:D61)", "number-type cells"], ["=sum(D2:D61)/count(D2:D61)", "filled cells"], ["=sum(D2:D61)/countif(D2:D61,\">0\")", "greater than zero"], ["=sum(D2:D61)/60", "range length"], ]; const y = g([42, 10, 13, 20]), numerator = read("=sum(D2:D61)"); console.log(y(["formula", "mean", "denominator", "what it is"])); for (const [f, what] of MEANS) console.log(y([f, read(f).toFixed(2), Math.round(numerator / read(f)), what])); const v = MEANS.map(([f]) => read(f)); console.log(`\nSmallest ${Math.min(...v).toFixed(2)}, largest ${Math.max(...v).toFixed(2)} m3; ` + `ratio ${(Math.max(...v) / Math.min(...v)).toFixed(2)} times.`); // The same conditional sum written two ways: the threshold embedded in the criterion string, or taken from a parameter cell. const A = "=sumif(C2:C61,\">25\",C2:C61)"; const B = "=sumif(C2:C61,\">\"+H7,C2:C61)"; const Z = { ...H, H7: 30 }; // second tier 25 -> 30 m3 const z = g([44, 12, 14, 10]); console.log("\n" + z(["formula", "total", "threshold 30", "follows"])); for (const f of [A, B]) { const before = evaluate({ ...H, X1: f }, "X1"), after = evaluate({ ...Z, X1: f }, "X1"); // The threshold is 0.05 m3: readings are held to one decimal, a difference below that cannot be told apart. console.log(z([f, before.toFixed(1), after.toFixed(1), Math.abs(before - after) >= 0.05 ? 1 : 0])); } // Scan: each formula text is read for constants and references outside the range names. const SCAN = [...MEANS.map(([f]) => f), A, B]; const s = g([44, 8, 10, 11]); console.log("\n" + s(["formula", "hidden", "constant", "reference"])); const N = { hidden: 0, constant: 0, reference: 0 }; for (const f of SCAN) { const rest = f.replace(/[A-Z]\d+:[A-Z]\d+/g, ""); // range names are not counted const hidden = f.includes("=average("); // the denominator is not in the formula text const reference = (rest.match(/[A-Z]\d+/g) ?? []).length; const constant = (rest.replace(/[A-Z]\d+/g, "").match(/\d+(\.\d+)?/g) ?? []).length; N.hidden += +hidden; N.constant += +!!constant; N.reference += +!!reference; console.log(s([f, hidden ? "yes" : "no", constant, reference])); } console.log(`\n${SCAN.length} formulas: the denominator is not visible in ${N.hidden}, a constant ` + `is embedded in the decision in ${N.constant}, there is a reference to a parameter cell in ` + `${N.reference}, and the decision is a function name in ` + `${SCAN.length - N.hidden - N.constant - N.reference}.`); ``` ``` formula mean denominator what it is =average(D2:D61) 18.17 39 number-type cells =sum(D2:D61)/count(D2:D61) 13.37 53 filled cells =sum(D2:D61)/countif(D2:D61,">0") 24.43 29 greater than zero =sum(D2:D61)/60 11.81 60 range length Smallest 11.81, largest 24.43 m3; ratio 2.07 times. formula total threshold 30 follows =sumif(C2:C61,">25",C2:C61) 1010.6 1010.6 0 =sumif(C2:C61,">"+H7,C2:C61) 1010.6 846.3 1 formula hidden constant reference =average(D2:D61) yes 0 0 =sum(D2:D61)/count(D2:D61) no 0 0 =sum(D2:D61)/countif(D2:D61,">0") no 1 0 =sum(D2:D61)/60 no 1 0 =sumif(C2:C61,">25",C2:C61) no 1 0 =sumif(C2:C61,">"+H7,C2:C61) no 0 1 6 formulas: the denominator is not visible in 1, a constant is embedded in the decision in 3, there is a reference to a parameter cell in 1, and the decision is a function name in 1. ``` The mean reading swings between 11.81 m³ and 24.43 m³: a factor of 2.07. The numerator never changed, only the denominator was chosen, and all four values are defensible. Which one goes into the report depends on whose mean is being asked for: that of the subscribers who were read, or that of all registered subscribers. The second number is in the third table. The average function does not write the denominator into the formula text at all: whoever opens the sheet sees the value 18.17 and the name of the function, and cannot see the number 39. This decision **cannot be read back**. Writing the same number as `sum(D2:D61)/countnumbers(D2:D61)` does not change the result, but it moves the denominator into the formula text. **Of two versions that produce the same number, one hides the decision and the other writes it.** The conditional sum table repeats the same distinction on a threshold. Both formulas give 1010.6 m³; the `">25"` embedded in the criterion string cannot be told apart from `">"+H7`, which refers to a parameter cell, by the result. When the second tier is raised to 30 m³ the first stays at 1010.6 and the second drops to 846.3. The scan split the six formulas into four groups: in 1 the decision is not visible at all, in 3 there is an embedded literal, in 1 there is a reference to a parameter cell, and in 1 the decision is a **function name itself**. The last is the cheapest form of recoverability: a formula that writes `countnumbers` says by its name what it counts. #### Summary - The length of a range and the number of cells counted are different things: on a 60-cell range four counting functions gave 53, 39, 29, and 7; all four are correct, and each answers a different question. - A blank cell, a zero, and a number stored as text look alike in a cell, but the three are different things; a zero is a measurement, a blank cell is the absence of one. - The sum function skips text cells and does not say so: the column shows 708.5 m³ while the real total is 967.2 m³, a difference of 26.7%. An error is seen; a skip is not. - Four denominators for the same numerator swung the mean between 11.81 and 24.43: a factor of 2.07. The denominator of the mean is a part of the definition, not of the calculation. - Recoverability depends on how the formula is written: the average function never writes the denominator, an embedded threshold does not write its source, and a function name says the decision by its own name. Only 1 of the six formulas refers to a parameter cell. #### Next Step This lesson measured how many cells of a range are counted, but always with a single threshold: `">0"` or `">25"`. The tariff does not have a single threshold. Which tier a record falls into is decided by a nested condition that looks at all three limits of 10, 25, and 40 m³ at once, and a comparison operator is chosen at every layer. The next lesson distributes the same 60 records into tiers twice — once with `<`, once with `<=` — and counts how many of the records sitting exactly on a boundary change tier. ### Logical Functions Source: https://academia.sh/en/courses/analysis-with-spreadsheets/logical-functions The same nested condition is applied to the 10/25/40 m³ tariff tiers in three versions: when the boundary comparison is `<` instead of `<=`, 4 records change tier; when the order of the conditions is broken, 20 records fall into the wrong tier and the first tier empties completely. The boundary decision moves the amount total from 26,219.52 to 26,698.32 fee units, that is 1.83%, but it moves a single bill by 35.5%. Of the three versions that produce the same total, only one has the tier written in a cell; in the others the decision sits inside a 3-layer condition and in no cell. 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. ```js // 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. ```js // 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. ```js // 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. ### Text Functions Source: https://academia.sh/en/courses/analysis-with-spreadsheets/text-functions 48 field visit records are matched to a 60-subscriber list on the subscriber code, and in raw form only 8 of them match. Trimming raises that to 24, trimming plus uppercase conversion to 32, and removing every kind of whitespace to 48; the consumption reported for the visits swings between 232.1 m³ and 1204.8 m³ across these decisions. A fifth decision that rebuilds the code yields the same 48 while silently attaching a record that arrives with a different prefix to the wrong subscriber. A locale-insensitive case conversion misses 15 of the technician names, and of three versions that produce the same result, only one leaves the 40 correction decisions readable back cell by cell. Until now every comparison was numeric. The other half of the sheet is text, and in text equality is not as obvious as it is with numbers: `AB-1041` and `AB-1041 ` look the same on screen, do not look the same length, and do not come out equal when compared. This lesson matches the field visit records to the subscriber list on the subscriber code and counts how many records **fail to join**. The measure is read in two places. The first is the number of matched records and the consumption value that goes into the report along with it. The second is where the cleaning decision sits: in a helper column, inside the matching formula, or nowhere at all — because a cell corrected by hand shows the right result and destroys the decision. - **BO13.** The subscriber codes in the visits table arrive in six forms: clean, trailing space, leading space, lowercase, trailing **non-breaking space**, inner space. The corruptions are fictional and are distributed in order with their own seed. The type of corruption is **not written** on the sheet; the `kind` function below can classify it only by inspection. - **BO14.** The `trim` function removes only the **normal space**; the non-breaking space and the inner space remain untouched. `strip` removes every kind of whitespace. The difference between the two is the most expensive decision of this lesson. - **BO15.** `upper` is a locale-insensitive case conversion; `upperTr` applies the Turkish rule for the letter i. The crew names are fictional and half of them were entered in lowercase. #### Identical to the Eye, Different in Comparison The block below builds two tables: on the left the 60-subscriber list, on the right the 48 visit records. In the output, spaces are turned into visible characters — a normal space becomes an underscore, a non-breaking space becomes a tilde. ```js // text.mjs — spreadsheet MODEL: the model from 01, extended with text functions. // The subscriber codes in the field visits table arrive corrupted; the corruptions are fictional. export const SEED = 20260602, ROWS = 60, VISITS = 48; const rng = (t) => { let s = t >>> 0; return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; }; export const NBSP = "\u00a0"; // invisible space: identical to a normal space in a cell const REF = /(\$?)([A-Z])(\$?)(\d+)/g; export const shift = (f, dC, dR) => f.replace(REF, (_, a, s, b, r) => (a ? "$" + s : String.fromCharCode(s.charCodeAt(0) + dC)) + (b ? "$" + r : String(+r + dR))); // Text functions. upper is locale-insensitive; upperTr applies the Turkish i/I rule. const FUNCS = { trim: (s) => String(s).replace(/^ +| +$/g, ""), // only the normal space is trimmed upper: (s) => String(s).toUpperCase(), upperTr: (s) => String(s).replace(/i/g, "İ").replace(/ı/g, "I").toUpperCase(), strip: (s) => String(s).replace(/\s/g, ""), // \s covers the invisible space too concat: (...p) => p.map(String).join(""), right: (s, n) => String(s).slice(-n), // last n characters: the numeric part of the code length: (s) => String(s).length, }; export function value(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(value(H, m.replace(/\$/g, "")))); return Function(...Object.keys(FUNCS), `return (${expr});`)(...Object.values(FUNCS)); } // Six types of corruption; they cannot be told apart by eye on the sheet. export const DEFECTS = [["clean", (k) => k], ["trailing space", (k) => k + " "], ["leading space", (k) => " " + k], ["lowercase", (k) => k.toLowerCase()], ["non-breaking space", (k) => k + NBSP], ["inner space", (k) => k.replace("-", "- ")]]; export const CREW = ["İsmet", "İlknur", "Irmak", "Ilgaz", "Sedef"]; export function sheet() { const r = rng(SEED), H = {}; H.A1 = "subscriber"; H.C1 = "consumption"; H.P1 = "visit code"; H.Q1 = "technician"; 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: rng runs in the same order H["C" + s] = Math.round((2 + r() * r() * 78) * 10) / 10; } // Visits table: VISITS distinct subscribers, each type of corruption applied in turn. const z = rng(SEED + 2); const order = Array.from({ length: ROWS }, (_, i) => i); for (let i = ROWS - 1; i > 0; i--) { const j = Math.floor(z() * (i + 1)); [order[i], order[j]] = [order[j], order[i]]; } for (let i = 0; i < VISITS; i++) { const s = i + 2, k = H["A" + (2 + order[i])]; H["P" + s] = DEFECTS[i % DEFECTS.length][1](k); // Half were entered in lowercase; the lowercase conversion follows the Turkish rule. const t = CREW[Math.floor(z() * CREW.length)]; H["Q" + s] = z() < 0.5 ? t.replace(/İ/g, "i").replace(/I/g, "ı").toLowerCase() : t; } return H; } // The type of corruption is not written on the sheet; it can be classified only by inspection. export const kind = (k) => k !== k.replace(/^ +| +$/g, "") ? "trimmable space" : k.includes(NBSP) ? "non-breaking space" : k.includes("- ") ? "inner space" : k !== k.toUpperCase() ? "lowercase" : "clean"; if (import.meta.url === `file://${process.argv[1]}`) { const H = sheet(); const show = (s) => String(s).replace(new RegExp(NBSP, "g"), "~").replace(/ /g, "_"); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const y = g([8, 14, 8, 20]); console.log(y(["cell", "visit code", "length", "defect"])); for (const s of [2, 3, 4, 5, 6, 7]) console.log(y(["P" + s, show(H["P" + s]), value({ ...H, X1: "=length(P" + s + ")" }, "X1"), kind(H["P" + s])])); const P = Array.from({ length: VISITS }, (_, i) => H["P" + (i + 2)]); const K = new Set(Array.from({ length: ROWS }, (_, i) => H["A" + (i + 2)])); const tally = {}; for (const k of P) tally[kind(k)] = (tally[kind(k)] ?? 0) + 1; console.log(`\n${VISITS} visit records — ` + Object.entries(tally).map(([a, n]) => `${a} ${n}`).join(", ")); console.log(`Records whose code is found exactly as in the subscriber table: ${P.filter((k) => K.has(k)).length}`); } ``` ``` cell visit code length defect P2 AB-1286 7 clean P3 AB-1083_ 8 trimmable space P4 _AB-1167 8 trimmable space P5 ab-1090 7 lowercase P6 AB-1097~ 8 non-breaking space P7 AB-_1048 8 inner space 48 visit records — clean 8, trimmable space 16, lowercase 8, non-breaking space 8, inner space 8 Records whose code is found exactly as in the subscriber table: 8 ``` The length column is the lesson's only visible evidence. `P3` and `P6` are eight characters long and in both the eighth character is a space; but one is trimmed and the other is not. On the sheet both look like a seven-character code. **What a text cell shows is not what it contains.** In raw form only 8 of the 48 records are found in the subscriber list. The remaining 40 records do not belong to vanished subscribers; all of them belong to subscribers who are in the list, and none of them match. #### Four Cleaning Decisions Cleaning is not a single operation but a sequence of decisions, and each decision brings back a group of records. The block below applies the four in a helper `R` column and prints, at each stage, the number of matched records together with the consumption that would go into the report. ```js // matching.mjs — four cleaning decisions, four match counts, and four reported consumption values. import { sheet, value, shift, ROWS, VISITS } from "./text.mjs"; const DECISIONS = [ ["raw", "=P2"], ["trim", "=trim(P2)"], ["trim + uppercase", "=upper(trim(P2))"], ["every space + uppercase", "=upper(strip(P2))"], ["rebuild", '=concat("AB-",right(strip(P2),4))'], ]; const H = sheet(); const CONSUMPTION = new Map(Array.from({ length: ROWS }, (_, i) => [H["A" + (i + 2)], H["C" + (i + 2)]])); const keys = (f) => { const K = { ...H }; for (let i = 0; i < VISITS; i++) K["R" + (i + 2)] = shift(f, 0, i); return Array.from({ length: VISITS }, (_, i) => value(K, "R" + (i + 2))); }; const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const y = g([26, 10, 12, 18, 12]); console.log(y(["R column formula", "matched", "unmatched", "consumption (m3)", "recovered"])); let previous = 0; for (const [name, f] of DECISIONS) { const R = keys(f), m = R.filter((k) => CONSUMPTION.has(k)); const t = m.reduce((a, k) => a + CONSUMPTION.get(k), 0); console.log(y([name, m.length, VISITS - m.length, t.toFixed(1), m.length - previous])); previous = m.length; } const total = Array.from({ length: ROWS }, (_, i) => H["C" + (i + 2)]).reduce((a, b) => a + b, 0); console.log(`\nTotal consumption of the 60 subscribers is ${total.toFixed(1)} m3; visited consumption is ` + `${keys(DECISIONS[3][1]).reduce((a, k) => a + (CONSUMPTION.get(k) ?? 0), 0).toFixed(1)} m3.`); // The rebuild does not read the code, it rewrites it: a record with another prefix matches too. const foreign = { ...H, P2: "XY-1041" }; for (const [name, f] of DECISIONS.slice(3)) { const K = { ...foreign, R2: f }; console.log(`${name}: XY-1041 -> ${value(K, "R2")} (${CONSUMPTION.has(value(K, "R2")) ? "matched" : "unmatched"})`); } ``` ``` R column formula matched unmatched consumption (m3) recovered raw 8 40 232.1 8 trim 24 24 527.6 16 trim + uppercase 32 16 673.7 8 every space + uppercase 48 0 1204.8 16 rebuild 48 0 1204.8 0 Total consumption of the 60 subscribers is 1419.9 m3; visited consumption is 1204.8 m3. every space + uppercase: XY-1041 -> XY-1041 (unmatched) rebuild: XY-1041 -> AB-1041 (matched) ``` The number that goes into the report is in the fourth column of this table, and it has four values: 232.1, 527.6, 673.7, and 1204.8 m³. There is more than a fivefold difference between the smallest and the largest, and all four came out of the same data, the same question, on the same day. The sentence "the total consumption of the visited subscribers" does not by itself say which of these four numbers is meant. Trimming alone brought back 16 records, and stopping there is easy: 24 matches look plausible, and the remaining 24 records can be explained away as "subscribers who are not in the list." Yet all 24 of those records are in the list; 8 carry a non-breaking space, 8 carry an inner space, and both survive trimming. **Whether a cleaning is sufficient cannot be told by looking at the cleaned count.** The fifth row gives the same count by a third route, and it is the most dangerous. The rebuild does not read the code, it **rewrites** it: it takes the last four characters and adds a fixed prefix in front. The last two rows show the price of that. An `XY-1041` record that arrives with another prefix does not match under the fourth decision; under the fifth it silently becomes `AB-1041` and is attached to an unrelated subscriber. The match count stays the same, and the error is invisible. #### Case Conversion and Where the Decision Sits Case conversion is not a single operation either. The block below folds the technician names with two conversions and compares them with the crew list, then scans three versions to see where the cleaning decision sits on the sheet. ```js // decision.mjs — the language dependence of case conversion, and where the cleaning decision sits on the sheet. import { sheet, value, shift, CREW, VISITS, NBSP } from "./text.mjs"; const H = sheet(); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const column = (f, n) => { const K = { ...H }; for (let i = 0; i < n; i++) K["S" + (i + 2)] = shift(f, 0, i); return Array.from({ length: n }, (_, i) => value(K, "S" + (i + 2))); }; // The crew list is folded with the same function; the match is the equality of two folded texts. const y = g([34, 10, 12, 22]); console.log(y(["S column formula", "matched", "unmatched", "failing names"])); for (const [name, f] of [["locale-insensitive", "=upper(Q2)"], ["Turkish rule", "=upperTr(Q2)"]]) { const S = column(f, VISITS); const list = CREW.map((t) => value({ X1: "=" + f.slice(1).replace("Q2", JSON.stringify(t)) }, "X1")); const m = S.filter((v) => list.includes(v)).length; const miss = [...new Set(S.filter((v) => !list.includes(v)))].join(" "); console.log(y([name + " (" + f + ")", m, VISITS - m, miss || "-"])); } // Three versions that produce the same result: the decision in a helper column, in a formula, or nowhere. const VERSIONS = [ ["helper column", 'R column =upper(strip(P2))', VISITS, true], ["embedded", "inside the matching formula", 0, true], ["manual fix", "written over the P column", 0, false], ]; const P = Array.from({ length: VISITS }, (_, i) => H["P" + (i + 2)]); const broken = P.filter((k) => k !== k.replace(/^ +| +$/g, "") || k.includes(NBSP) || k.includes("- ") || k !== k.toUpperCase()).length; const z = g([16, 30, 8, 10, 12]); console.log("\n" + z(["version", "where the decision is", "cells", "raw data", "read-back"])); for (const [name, place, cells, raw] of VERSIONS) console.log(z([name, place, cells, raw ? "kept" : "erased", cells ? broken : 0])); console.log(`\n${VISITS} visit records, ${broken} of them broken. In the helper column these ${broken} ` + `decisions are read one by one; in the manual fix none is read and the raw code does not remain either.`); ``` ``` S column formula matched unmatched failing names locale-insensitive (=upper(Q2)) 33 15 ISMET ILKNUR Turkish rule (=upperTr(Q2)) 48 0 - version where the decision is cells raw data read-back helper column R column =upper(strip(P2)) 48 kept 40 embedded inside the matching formula 0 kept 0 manual fix written over the P column 0 erased 0 48 visit records, 40 of them broken. In the helper column these 40 decisions are read one by one; in the manual fix none is read and the raw code does not remain either. ``` The first table states that case conversion is not a text operation but a **language** operation. The locale-insensitive conversion matched 33 of the 48 names; the 15 that failed belong to two names. The first letter of the names "ismet" and "ilknur," entered in lowercase, is converted to an undotted I under the locale-insensitive conversion and does not match the dotted form in the list. Under the conversion that applies the Turkish rule, all 48 names match. The function is called "convert to uppercase" in both; they do not do the same job. The second table gives the lesson's second number. All three versions have the same match result: 48 records match. The difference is in the last three columns. In the helper column version the decision sits in 48 cells; the cleaned code is visible, the raw code stays in place, and the question "what was done" for each of the 40 records that arrived broken is answered by looking at the cell. In the formula-embedded version the result is the same, but the cleaned code is visible nowhere on the sheet; which visit was attached to which subscriber can be guessed only by reading the formula text. The third row is the most expensive one and the most common one. When the codes are corrected by hand and written over the `P` column, the sheet looks flawless: every code is clean, every match holds, and there is no helper column. The number of decisions that can be read back is **zero**, and the raw data no longer exists. Someone who looks a second time cannot see that 40 records were corrected, cannot know which rule was applied, and cannot check whether the correction was right. **An operation that fixes the result, if it destroys the decision, destroys the data too.** #### Summary - What a text cell shows is not what it contains: two codes that look the same can have different lengths and do not come out equal in a comparison. - Of the 48 visit records, 8 matched in raw form; trimming raised that to 24, case conversion to 32, and removing every kind of whitespace to 48. The reported consumption swung between 232.1 and 1204.8 m³. - Ordinary trimming removes only the normal space; the 16 records carrying a non-breaking space or an inner space survive trimming and keep failing to match. - Whether a cleaning was enough cannot be told by looking at the cleaned count; it can be told only by examining the unmatched records one by one. - Case conversion depends on the language: the locale-insensitive conversion failed to match 15 of the 48 names, and the conversion that applies the Turkish rule matched them all. - Of three versions that produce the same match, only one lets the 40 correction decisions be read cell by cell; in the manual fix both the decision and the raw data are lost. #### Next Step Now that the matching is in place, the dates of the visit records can be used as well. The next step asks a question that looks simple: how many days passed between the opening and the closing of a fault record. A date in a cell is a number, and the displayed format is not that number itself; the difference between two dates is a subtraction, but whether the endpoints are included is a decision. The next lesson computes the mean resolution time of the same record set with two endpoint decisions, measures what "a month later" means at month end, and counts how many of the dates written as text can be read as two different days. ### Date Functions Source: https://academia.sh/en/courses/analysis-with-spreadsheets/date-functions The mean resolution time of the same 54 support requests comes out as 6.33, 7.33, and 5.20 days under three day-counting decisions; with the endpoints excluded, 6 requests appear to have taken zero days, and in the workday count two requests that took two days on the calendar show zero workdays. What one month later means at month end diverges for 6 of the 60 subscribers: the clamp rule collapses three subscribers onto the same day and produces three periods of 28, 29, and 30 days, while the overflow rule gives every one of them 31 days but shifts the February reading into March. When written as text, 19 of the 54 opening dates can be read as two different days, and the largest deviation is 323 days. How many days passed between the opening and the closing of a fault record looks like a simple subtraction. It is not: subtraction gives the **interval** between the two endpoints, while the question being asked is usually the **number of days covered**, and the two differ by one day. This lesson establishes what a date is in a cell, computes the mean resolution time of the same request set under three decisions, and shows that the phrase "one month later" yields two different dates at month end. - **BO16.** A date in a cell is a **number**: the count of days from a starting day; in this model serial 1 is January 1, 2026. The format is only an **appearance**. - **BO17.** The support requests and reading days are fictional. The reading day is distributed between the 1st and the 31st of the month; this is what creates the month-end question. - **BO18.** A weekend is two days, and no holidays are defined. `workdays` counts the weekdays with both endpoints included; if a holiday list is added, the count changes again. #### What Sits in the Cell Is a Number The block below builds the calendar conversion, the reading days, and the support requests. ```js // calendar.mjs — spreadsheet MODEL: a date in a cell is a NUMBER, the format is only an appearance. // Serial number 1 = January 1, 2026. The support requests and reading days are fictional. export const SEED = 20260602, REQUESTS = 54, ROWS = 60; const rng = (t) => { let s = t >>> 0; return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; }; // The calendar conversion covers 2026-2027; neither year is a leap year, both are 365 days. const MONTHS = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]; const BEFORE = MONTHS.map((_, i) => MONTHS.slice(0, i).reduce((a, b) => a + b, 0)); export const serial = (y, m, d) => (y - 2026) * 365 + BEFORE[m - 1] + d; // serial 1 = January 1, 2026 export const toDate = (s) => { let k = s - 1, y = 2026, m = 0; while (k >= 365) { k -= 365; y++; } // a day that crosses the month boundary spills into the next month while (k >= MONTHS[m]) k -= MONTHS[m++]; return [y, m + 1, k + 1]; }; const REF = /(\$?)([A-Z])(\$?)(\d+)/g; export const shift = (f, dC, dR) => f.replace(REF, (_, a, s, b, r) => (a ? "$" + s : String.fromCharCode(s.charCodeAt(0) + dC)) + (b ? "$" + r : String(+r + dR))); const two = (n) => String(n).padStart(2, "0"); const FUNCS = { date: (y, m, d) => serial(y, m, d), month: (s) => toDate(s)[1], day: (s) => toDate(s)[2], format: (s) => { const [y, m, d] = toDate(s); return `${two(d)}.${two(m)}.${y}`; }, monthEnd: (s) => { const [y, m] = toDate(s); return serial(m === 12 ? y + 1 : y, m === 12 ? 1 : m + 1, 1) - 1; }, // one before the first day of the next month weekday: (s) => ((serial(2026, 1, 1) + s - 1 + 3) % 7) + 1, // January 1, 2026 is a Thursday min: (a, b) => Math.min(a, b), workdays: (a, b) => { let n = 0; for (let s = a; s <= b; s++) if (FUNCS.weekday(s) <= 5) n++; return n; }, }; export function value(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(value(H, m.replace(/\$/g, "")))); return Function(...Object.keys(FUNCS), `return (${expr});`)(...Object.values(FUNCS)); } export function sheet() { const H = {}, r = rng(SEED); H.A1 = "subscriber"; H.C1 = "consumption"; H.T1 = "opened"; H.U1 = "closed"; H.V1 = "reading day"; 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: rng runs in the same order H["C" + s] = Math.round((2 + r() * r() * 78) * 10) / 10; H["V" + s] = 1 + (i * 7) % 31; // the reading day falls between the 1st and the 31st } // Support requests: opened in the first half of 2026, duration 0-20 days. const z = rng(SEED + 3); for (let i = 0; i < REQUESTS; i++) { const s = i + 2, b = serial(2026, 1, 1) + Math.floor(z() * 180); H["T" + s] = b; H["U" + s] = b + Math.floor(z() * z() * 21); } return H; } if (import.meta.url === `file://${process.argv[1]}`) { const H = sheet(); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const y = g([8, 10, 16, 10]); const o = (f) => value({ ...H, X1: f }, "X1"); console.log(y(["cell", "opened", "format(opened)", "closed"])); for (const s of [2, 4, 5, 55]) console.log(y(["T" + s, H["T" + s], o(`=format(T${s})`), H["U" + s]])); const F = Array.from({ length: REQUESTS }, (_, i) => H["U" + (i + 2)] - H["T" + (i + 2)]); console.log(`\n${REQUESTS} support requests. Closed the same day (difference 0): ` + `${F.filter((v) => v === 0).length}. Raw difference total ${F.reduce((a, b) => a + b, 0)} days.`); console.log(`January 1, 2026 serial ${serial(2026, 1, 1)}, January 31, 2026 serial ${serial(2026, 1, 31)}, ` + `February 28, 2026 serial ${serial(2026, 2, 28)}; last day of February 2026 is ` + `${o("=format(monthEnd(date(2026,2,1)))")}.`); } ``` ``` cell opened format(opened) closed T2 51 20.02.2026 57 T4 72 13.03.2026 89 T5 148 28.05.2026 159 T55 69 10.03.2026 69 54 support requests. Closed the same day (difference 0): 6. Raw difference total 342 days. January 1, 2026 serial 1, January 31, 2026 serial 31, February 28, 2026 serial 59; last day of February 2026 is 28.02.2026. ``` What sits in cell `T2` is not `20.02.2026` but `51`; the second and third columns are two readings of the same cell. **The date a cell shows is an interpretation of the number inside it.** The last line sets up the month-end question: the last day of February 2026 is the 28th, and meters read on the 29th, 30th, or 31st of the month have no February counterpart. #### Three Day-Counting Decisions The same 54 requests are measured with three formulas: the first excludes the endpoints, the second counts both, the third counts only weekdays. ```js // duration.mjs — the same request set under three day-counting decisions: endpoints excluded, endpoints included, workdays. import { sheet, value, shift, REQUESTS } from "./calendar.mjs"; const DECISIONS = [ ["endpoints excluded", "=U2-T2"], ["endpoints included", "=U2-T2+1"], ["workdays (endpoints included)", "=workdays(T2,U2)"], ]; const H = sheet(); const column = (f) => { const K = { ...H }; for (let i = 0; i < REQUESTS; i++) K["W" + (i + 2)] = shift(f, 0, i); return Array.from({ length: REQUESTS }, (_, i) => value(K, "W" + (i + 2))); }; const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const D = DECISIONS.map(([, f]) => column(f)); const y = g([30, 12, 12, 10]); console.log(y(["W column formula", "total days", "mean", "zero"])); DECISIONS.forEach(([name], i) => { const d = D[i], t = d.reduce((a, b) => a + b, 0); console.log(y([name, t, (t / REQUESTS).toFixed(2), d.filter((v) => v === 0).length])); }); console.log(`\n${REQUESTS} requests. Mean resolution time ` + DECISIONS.map(([name], i) => `${(D[i].reduce((a, b) => a + b, 0) / REQUESTS).toFixed(2)} (${name})`) .join(", ") + " days."); // The requests that come out zero under any decision: zero is not a duration, it is a counting result. const z = g([8, 14, 14, 12, 12, 10]); console.log("\n" + z(["cell", "opened", "closed", "excluded", "included", "workdays"])); const o = (f) => value({ ...H, X1: f }, "X1"); for (let i = 0; i < REQUESTS; i++) if (D[0][i] === 0 || D[2][i] === 0) console.log(z(["W" + (i + 2), o(`=format(T${i + 2})`), o(`=format(U${i + 2})`), D[0][i], D[1][i], D[2][i]])); ``` ``` W column formula total days mean zero endpoints excluded 342 6.33 6 endpoints included 396 7.33 0 workdays (endpoints included) 281 5.20 2 54 requests. Mean resolution time 6.33 (endpoints excluded), 7.33 (endpoints included), 5.20 (workdays (endpoints included)) days. cell opened closed excluded included workdays W14 10.06.2026 10.06.2026 0 1 1 W17 14.05.2026 14.05.2026 0 1 1 W26 19.01.2026 19.01.2026 0 1 1 W28 19.05.2026 19.05.2026 0 1 1 W49 06.03.2026 07.03.2026 1 2 0 W50 13.02.2026 14.02.2026 1 2 0 W51 06.05.2026 06.05.2026 0 1 1 W55 10.03.2026 10.03.2026 0 1 1 ``` The number to report is the mean resolution time, and it has three values: 6.33, 7.33, and 5.20 days. The difference between the largest and the smallest is 41% of the smallest value. All three are defensible; the phrase "mean resolution time" does not say which one is meant. The zero column shows where the decision breaks. In the endpoints-excluded count, 6 requests appear to have taken **zero days**; yet those requests were closed on the day they were opened, that is, they took one day. A report that reads the zeros as "resolved instantly" writes the result of the counting decision, not of the data. The last two rows of the lower table give an extreme in the opposite direction: `W49` and `W50` took two days on the calendar, and because they fall entirely on a weekend, they show **zero** workdays. **The same record carries three different durations under three counting rules.** #### When Is One Month Later "One month" is not a fixed number of days. The block below computes the February reading under two rules, counts the ambiguity of dates converted to text, and scans the day-counting decision across four versions. ```js // month.mjs — "one month later" under two rules; and where the day-counting decision sits on the sheet. import { sheet, value, shift, serial, REQUESTS, ROWS } from "./calendar.mjs"; const H = sheet(); const ev = (K, f) => value({ ...K, X9: f }, "X9"); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); // The January reading is day V of the month; the February reading is "one month later". Two rules give two dates. const RULES = { clamp: "=date(2026,2,min(V2,day(monthEnd(date(2026,2,1)))))", overflow: "=date(2026,2,V2)", }; const column = (K, c, n, f) => { const M = { ...K }; for (let i = 0; i < n; i++) M[c + (i + 2)] = shift(f, 0, i); return Array.from({ length: n }, (_, i) => value(M, c + (i + 2))); }; const JAN = Array.from({ length: ROWS }, (_, i) => serial(2026, 1, H["V" + (i + 2)])); const S = Object.fromEntries(Object.entries(RULES).map(([a, f]) => [a, column(H, "Y", ROWS, f)])); const y = g([10, 8, 14, 10, 14, 10]); console.log(y(["cell", "reading", "clamp", "period", "overflow", "period"])); for (const s of [2, 6, 15, 24]) { const i = s - 2; console.log(y(["V" + s, H["V" + s], ev(H, `=format(${S.clamp[i]})`), S.clamp[i] - JAN[i], ev(H, `=format(${S.overflow[i]})`), S.overflow[i] - JAN[i]])); } const differ = S.clamp.filter((s, i) => s !== S.overflow[i]).length; const escaped = S.overflow.filter((s) => ev(H, `=month(${s})`) !== 2).length; console.log(`\nOf ${ROWS} subscribers, the two rules give different dates for ${differ} ` + `(reading day 29, 30, or 31). Under the overflow rule, the "February reading" of ${escaped} ` + `subscribers does not fall in February.`); // A date entered as text: if day and month can swap places, the cell is read as two different days. const swapped = Array.from({ length: REQUESTS }, (_, i) => [H["T" + (i + 2)], ev(H, `=month(T${i + 2})`), ev(H, `=day(T${i + 2})`)]) .filter(([, m, d]) => d <= 12 && d !== m) // a day above 12 cannot swap places .map(([s, m, d]) => Math.abs(serial(2026, d, m) - s)); console.log(`Of ${REQUESTS} opening dates, ${swapped.length} can be read as two different days when ` + `entered as "DD.MM.YYYY" text; the largest deviation is ${Math.max(...swapped)} days.`); // Read-back scan: where the day-counting decision sits in the formula text. const P = { ...H, X1: "ends included (1/0)", X2: 1 }; // label on the left, value on the right const VERSIONS = [["=U2-T2", "none"], ["=U2-T2+1", "embedded literal"], ["=U2-T2+$X$2", "parameter cell"], ["=workdays(T2,U2)", "function name"]]; const z = g([18, 24, 10, 10, 10]), sum = (d) => d.reduce((x, v) => x + v, 0); console.log("\n" + z(["formula", "where the decision is", "total", "X2=0", "follow"])); for (const [f, place] of VERSIONS) { const a = column(P, "W", REQUESTS, f), b = column({ ...P, X2: 0 }, "W", REQUESTS, f); console.log(z([f, place, sum(a), sum(b), a.filter((v, i) => v !== b[i]).length])); } ``` ``` cell reading clamp period overflow period V2 1 01.02.2026 31 01.02.2026 31 V6 29 28.02.2026 30 01.03.2026 31 V15 30 28.02.2026 29 02.03.2026 31 V24 31 28.02.2026 28 03.03.2026 31 Of 60 subscribers, the two rules give different dates for 6 (reading day 29, 30, or 31). Under the overflow rule, the "February reading" of 6 subscribers does not fall in February. Of 54 opening dates, 19 can be read as two different days when entered as "DD.MM.YYYY" text; the largest deviation is 323 days. formula where the decision is total X2=0 follow =U2-T2 none 342 342 0 =U2-T2+1 embedded literal 396 396 0 =U2-T2+$X$2 parameter cell 396 342 54 =workdays(T2,U2) function name 281 281 0 ``` The first table puts the cost of the two rules side by side. Under the clamp rule, the three subscribers read on the 29th, 30th, and 31st of the month are gathered onto February 28 and their periods become 30, 29, and 28 days: the periods of three meters read on the same date have three different lengths, and no column on the sheet says so. Under the overflow rule the period is 31 days, but the February reading slides to March 1, 2, and 3: **the February reading of 6 subscribers is not in February.** The text-date row measures a third ambiguity. In 19 of the 54 openings the day number is less than 12; when such a date is written as `DD.MM.YYYY` text, month and day can swap places and the cell is read as two different days — the largest deviation is 323 days. **A date converted to text is a decision that cannot be read back.** The last table is the lesson's second number. The first two versions do not hold the decision on the sheet: `=U2-T2` does not say that the endpoints are excluded, and `=U2-T2+1` shows a `1` but does not say what that `1` is. In the third, the decision is in cell `X2`, can be read in two steps by its label, and when it is changed 54 cells follow; in the fourth, the decision is in the name of the function. The results of the first two are 342 and 396; both are correct, both are without a source. #### Summary - A date in a cell is a number; the format is an interpretation of that number and does not change its content. - The mean resolution time of the same 54 requests came out as 6.33, 7.33, and 5.20 days under three counting decisions; the difference is 41% of the smallest value. - Zero is not a duration, it is a counting result: in the endpoints-excluded count 6 requests, and in the workday count 2 requests that fall on a weekend, showed zero. - "One month later" gives two different dates at month end: the clamp rule produced three periods of 28, 29, and 30 days, and the overflow rule gave 31 days but shifted the February reading of 6 subscribers into March. - The day-counting decision can be read back only when it is written into a parameter cell or a function name; an embedded `+1` shows the number, not the decision. The 19 dates converted to text cannot be read back at all. #### Next Step Until now every formula looked at the same row. Yet the previous lesson assumed something: that the code in the visit record is **found** in the subscriber table and that the consumption against it is **fetched**. What does that job is a lookup function, and the lookup can be written in two different forms, by the column number or by the header name it is given. The two forms give the same result, until a column is inserted in the middle of the table. The next lesson counts, when a column is inserted, how many lookups break, how many return an error, and how many return a wrong value without an error. ### Lookup Functions Source: https://academia.sh/en/courses/analysis-with-spreadsheets/lookup-functions The same lookup is built in four versions, and when a column is inserted in the middle of the table only one of them breaks: the vertical lookup with an embedded column number returns the wrong value in 48 of 48 records, raises an error in none, and shows a total of 1084.6 m³ instead of 1204.8 m³. Had the inserted column carried an unreasonable number, the same breakage would have shown up as 201081.0 m³. When the table is re-sorted by region, approximate match silently changes 34 records, and the only thing that carries that decision is a zero in the formula. The previous two lessons assumed something: that the code in the visit record is found in the subscriber table and that the consumption against it is fetched. What does that job is a lookup function, and the lookup answers two questions separately: **which row**, **which column**. The second is answered either by a number or by a header name, and that answer is the whole lesson. The same lookup is written in four forms, and all four produce the same number; then a column is inserted. - **BO19.** When a column is inserted, **cell references shift**, but **number constants in the formula text do not**. This is the rule at the center of the model, and it is written in the `insertColumn` function. - **BO20.** A single-row or single-column range is expanded into a flat array, a rectangular range into an array of rows. `vlookup` searches for its key in the **first column** of the range. - **BO21.** The visit codes are taken to have gone through the cleaning decision of the previous lesson; the only thing measured here is the lookup. #### Two Versions of the Lookup The block below builds the five-column subscriber table, the visit list, and two versions of the lookup. ```js // lookup.mjs — spreadsheet MODEL: lookup functions and column insertion. // When a column is inserted, CELL REFERENCES shift, NUMBER CONSTANTS in the formula do not. export const SEED = 20260602, ROWS = 60, VISITS = 48; const rng = (t) => { let s = t >>> 0; return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; }; const REGION = ["north", "south", "east", "west", "central"]; const REF = /(\$?)([A-Z])(\$?)(\d+)/g; const parse = (a) => ({ c: a.charCodeAt(a[0] === "$" ? 1 : 0) - 65, r: +a.replace(/\D/g, "") }); const cellName = (c, r) => String.fromCharCode(65 + c) + r; const RANGE = /(\$?[A-Z]\$?\d+):(\$?[A-Z]\$?\d+)/g; // A single-row or single-column range is expanded into a flat array, a rectangular range into an array of rows. const expand = (a, b, H) => { const x = parse(a), y = parse(b), out = []; const flat = x.c === y.c || x.r === y.r; for (let r = x.r; r <= y.r; r++) { const row = []; for (let c = x.c; c <= y.c; c++) row.push(value(H, cellName(c, r))); if (flat) out.push(...row); else out.push(row); } return out; }; export const shift = (f, dC, dR) => f.replace(REF, (_, a, s, b, r) => (a ? "$" + s : String.fromCharCode(s.charCodeAt(0) + dC)) + (b ? "$" + r : String(+r + dR))); const find = (k, d, kind) => { if (kind === 0) return d.indexOf(k) + 1; // exact match: 0 if absent let i = 0; // approximate match: the last one not greater than k for (let j = 0; j < d.length; j++) if (d[j] <= k) i = j + 1; return i; }; const FUNCS = { match: (k, d, kind) => find(k, d, kind), index: (d, s, c) => (c === undefined ? d[s - 1] : d[s - 1][c - 1]), vlookup: (k, T, n, kind) => { const i = find(k, T.map((r) => r[0]), kind); return i ? T[i - 1][n - 1] : "#N/A"; }, }; export function value(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(RANGE, (_, a, b) => JSON.stringify(expand(a, b, H))) .replace(REF, (m) => JSON.stringify(value(H, m.replace(/\$/g, "")))); return Function(...Object.keys(FUNCS), `return (${expr});`)(...Object.values(FUNCS)); } // Column insertion: the cells at and to the right of the position move one column, and the references in // formulas move with them. The NUMBER CONSTANTS in the formula text do not change — this whole lesson rests on that difference. export function insertColumn(H, at, header, make) { const k = at.charCodeAt(0) - 65, Y = {}; const move = (f) => f.replace(REF, (_, d1, s, d2, r) => { const c = s.charCodeAt(0) - 65; return (d1 ? "$" : "") + cellName(c >= k ? c + 1 : c, "") + (d2 ? "$" : "") + r; }); for (const [name, v] of Object.entries(H)) { const c = name.charCodeAt(0) - 65, r = name.slice(1); Y[cellName(c >= k ? c + 1 : c, r)] = typeof v === "string" && v[0] === "=" ? "=" + move(v.slice(1)) : v; } Y[at + "1"] = header; for (let i = 0; i < ROWS; i++) Y[at + (i + 2)] = make(i); return Y; } export const VERSIONS = [ ["vlookup, column no. 3", "=vlookup(P2,$A$2:$E$61,3,0)"], ["vlookup, by header", '=vlookup(P2,$A$2:$E$61,match("consumption",$A$1:$E$1,0),0)'], ["match-index, by column", "=index($C$2:$C$61,match(P2,$A$2:$A$61,0))"], ["match-index, by header", '=index($A$2:$E$61,match(P2,$A$2:$A$61,0),match("consumption",$A$1:$E$1,0))'], ]; export function sheet() { const r = rng(SEED), H = {}; H.A1 = "subscriber"; H.B1 = "region"; H.C1 = "consumption"; H.D1 = "reading"; H.E1 = "amount"; for (let i = 0; i < ROWS; i++) { const s = i + 2; H["A" + s] = "AB-" + String(1041 + i * 7).padStart(4, "0"); H["B" + s] = REGION[Math.floor(r() * 5)]; const t = Math.round((2 + r() * r() * 78) * 10) / 10; H["C" + s] = t; H["D" + s] = Math.round(t); H["E" + s] = Math.round((t * 8.4 + 25) * 100) / 100; } // Visits table: the codes are cleaned (previous lesson), each record belongs to one subscriber. const z = rng(SEED + 2); const order = Array.from({ length: ROWS }, (_, i) => i); for (let i = ROWS - 1; i > 0; i--) { const j = Math.floor(z() * (i + 1)); [order[i], order[j]] = [order[j], order[i]]; } H.P1 = "visit code"; for (let i = 0; i < VISITS; i++) H["P" + (i + 2)] = H["A" + (2 + order[i])]; return H; } if (import.meta.url === `file://${process.argv[1]}`) { const H = sheet(); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const y = g([8, 12, 10, 12, 12]); console.log(y(["cell", "subscriber", "region", "consumption", "amount"])); for (const s of [2, 3, 61]) console.log(y(["A" + s, H["A" + s], H["B" + s], H["C" + s], H["E" + s]])); console.log(`\nHeaders A1:E1 = ${[..."ABCDE"].map((c) => H[c + "1"]).join(", ")}; ` + `"consumption" is column 3. Visit code P2 = ${H.P2}.`); for (const [name, f] of VERSIONS.slice(0, 2)) console.log(`${name}\n ${f}\n -> ${value({ ...H, W2: f }, "W2")} m3`); } ``` ``` cell subscriber region consumption amount A2 AB-1041 south 9.8 107.32 A3 AB-1048 east 71.6 626.44 A61 AB-1454 east 21.2 203.08 Headers A1:E1 = subscriber, region, consumption, reading, amount; "consumption" is column 3. Visit code P2 = AB-1286. vlookup, column no. 3 =vlookup(P2,$A$2:$E$61,3,0) -> 28.9 m3 vlookup, by header =vlookup(P2,$A$2:$E$61,match("consumption",$A$1:$E$1,0),0) -> 28.9 m3 ``` Both versions give 28.9 m³ and both use the same function. The only difference is how the "consumption" column is pointed to. In the first, that column is a number: `3`. Where the number came from is not written in the formula; the person who wrote it counted the headers and put the number they counted into the formula. In the second, the column is a name and is searched for again in the header row every time. At this point the distinction looks like a difference of style; a check that compares results cannot tell the two apart. What tells them apart is how the sheet **reacts to change**. #### A Column in the Middle of the Table The block below builds the four versions, then inserts a "previous period" column between `B` and `C`; its values are of the same magnitude as the existing consumption. ```js // breakage.mjs — the same lookup in four versions; what happens when a column is inserted in the middle of the table. import { VERSIONS, sheet, value, shift, insertColumn, VISITS } from "./lookup.mjs"; const H = sheet(); const fill = (K, f) => { const M = { ...K }; for (let i = 0; i < VISITS; i++) M["W" + (i + 2)] = shift(f, 0, i); return M; }; // A "previous period" column is inserted between B and C: its values are of the same magnitude as consumption. const PREVIOUS = (i) => Math.round(H["C" + (i + 2)] * 9) / 10; const read = (M, s) => Array.from({ length: VISITS }, (_, i) => value(M, s + (i + 2))); const count = (d) => d.filter((v) => typeof v === "number").reduce((a, b) => a + b, 0); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const y = g([26, 14, 14, 10, 8, 10]); console.log(y(["version", "total before", "total after", "changed", "errors", "silent"])); for (const [name, f] of VERSIONS) { const A = read(fill(H, f), "W"); const E = insertColumn(fill(H, f), "C", "previous period", PREVIOUS); const B = read(E, "X"); // the W column became X after the insertion const changed = B.filter((v, i) => v !== A[i]).length; const errors = B.filter((v) => typeof v !== "number").length; console.log(y([name, count(A).toFixed(1), count(B).toFixed(1), changed, errors, changed - errors])); } // The same breakage becomes visible when the inserted column carries an unreasonable number. console.log(""); for (const [name, make] of [["previous period", PREVIOUS], ["meter no", (i) => 4100 + i * 3]]) { const B = read(insertColumn(fill(H, VERSIONS[0][1]), "C", name, make), "X"); console.log(`If the inserted column is "${name}", the broken lookup totals ${count(B).toFixed(1)} m3, ` + `first value ${B[0]}.`); } ``` ``` version total before total after changed errors silent vlookup, column no. 3 1204.8 1084.6 48 0 48 vlookup, by header 1204.8 1204.8 0 0 0 match-index, by column 1204.8 1204.8 0 0 0 match-index, by header 1204.8 1204.8 0 0 0 If the inserted column is "previous period", the broken lookup totals 1084.6 m3, first value 26. If the inserted column is "meter no", the broken lookup totals 201081.0 m3, first value 4205. ``` One of the four versions broke: **48** of 48 records changed, and none of them raised an error. The broken version still returns a number, and the number is still of the size of a consumption. The report shows 1084.6 instead of 1204.8 m³; the difference is 10%, small enough for a check total to approve. The cause of the break is not a function but a **constant**: the second version, which reads the column number from the header, uses the same function and did not break. **Which position a column is in is not a fact but a moment**; when a column is inserted, the moment passes and the number stays. The last two lines measure the visibility of the error. Had the inserted column carried meter numbers, the same broken formula would have produced 201081.0 m³ and been caught; when it carries previous-period consumption, it produces 1084.6 m³ and is not caught. **The visibility of an error is measured not by its size but by what it resembles.** #### When the Order Changes The second constant of the lookup is the last argument: exact match or approximate. The block below measures this decision under two sort orders, then scans the four versions. ```js // sort.mjs — approximate match depends on sort order; and reading the decision back from the formula. import { VERSIONS, sheet, value, shift, ROWS, VISITS } from "./lookup.mjs"; const H = sheet(); // The table is re-sorted by region: rows swap places, range names do not change. const sorted = (K) => { const s = Array.from({ length: ROWS }, (_, i) => i + 2) .sort((a, b) => (K["B" + a] < K["B" + b] ? -1 : K["B" + a] > K["B" + b] ? 1 : K["A" + a] < K["A" + b] ? -1 : 1)); const Y = { ...K }; s.forEach((old, j) => { for (const c of "ABCDE") Y[c + (j + 2)] = K[c + old]; }); return Y; }; const fill = (K, f) => { const M = { ...K }; for (let i = 0; i < VISITS; i++) M["W" + (i + 2)] = shift(f, 0, i); return M; }; const read = (M) => Array.from({ length: VISITS }, (_, i) => value(M, "W" + (i + 2))); const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j]) : String(v).padEnd(B[0]))).join(""); const S = sorted(H); const CORRECT = read(fill(H, "=vlookup(P2,$A$2:$E$61,3,0)")); const y = g([20, 16, 10, 10, 8]); console.log(y(["table sort order", "last argument", "total", "wrong", "errors"])); for (const [name, K] of [["by subscriber code", H], ["by region", S]]) for (const [kind, last] of [["0 exact match", "0"], ["1 approximate", "1"]]) { const d = read(fill(K, `=vlookup(P2,$A$2:$E$61,3,${last})`)); const t = d.filter((v) => typeof v === "number").reduce((a, b) => a + b, 0); console.log(y([name, kind, t.toFixed(1), d.filter((v, i) => v !== CORRECT[i]).length, d.filter((v) => typeof v !== "number").length])); } // Read-back scan: the embedded numbers and header names in the formula text are counted. const place = (f) => /"[a-z]+"/.test(f) ? "header name" : /index\(\$[A-Z]\$\d+:\$[A-Z]\$\d+,/.test(f) ? "cell range" : "embedded number"; const z = g([26, 16, 10, 14]); console.log("\n" + z(["version", "column decision", "constants", "match type"])); for (const [name, f] of VERSIONS) { const rest = f.replace(/\$?[A-Z]\$?\d+/g, ""); // range and cell names are not counted const consts = (rest.match(/\d+/g) ?? []); console.log(z([name, place(f), consts.length, consts.at(-1) + " (embedded)"])); } ``` ``` table sort order last argument total wrong errors by subscriber code 0 exact match 1204.8 0 0 by subscriber code 1 approximate 1204.8 0 0 by region 0 exact match 1204.8 0 0 by region 1 approximate 890.9 34 0 version column decision constants match type vlookup, column no. 3 embedded number 2 0 (embedded) vlookup, by header header name 2 0 (embedded) match-index, by column cell range 1 0 (embedded) match-index, by header header name 2 0 (embedded) ``` The first two rows of the first table set a trap: in a table sorted by subscriber code, exact match and approximate match give **the same** result, so the person choosing sees no difference. The third and fourth rows turn the sort order to region — an ordinary job for someone who wants to look at the regional breakdown. Exact match does not budge; approximate match returns a different row in **34** of 48 records, the total drops to 890.9 m³, and again **there is no error.** The second table applies the course's measure to the lesson. The column decision can sit in three places: in an embedded number, in a cell range, in a header name — and the only version that broke is the one whose decision is an embedded number. But the last column says something more disturbing: **in all four, the match type is an embedded literal.** If that `0` were a `1`, no error would appear, and on the day the table was re-sorted 34 records would silently change. The rule maintained since the first lesson of the course is completed here: **a decision embedded in a cell is an unwritten decision.** The column number and the match type are both decisions, and both sit disguised as a number. #### Summary - A lookup answers two questions separately — which row, which column; the second answer can be a number or a header name, and the two produce the same result. - When a column is inserted, cell references shift and number constants do not: only one of four versions broke, returned a wrong value in 48 of 48 records, and raised no error at all. - The total became 1084.6 instead of 1204.8 m³; the same breakage would have shown up as 201081.0 m³ with a different column. What breaks is not the function but the embedded column number. - Approximate match depends on sort order and does not reveal this as long as the table is sorted: after sorting by region, 34 records silently changed. - In all four versions the match type is an embedded number; the course's rule is completed here — a decision embedded in a cell is an unwritten decision. #### Next Step Over six lessons this topic measured cell-level decisions: reference type, counting criterion, condition boundary, cleaning rule, day-counting rule, lookup form. In each lesson a number was placed next to the decision that produced it and the number of steps in which the decision could be read back was counted. But all of these measurements looked at **individual cells**; none produced a number that looks at the whole table. A more basic question was never asked either: **is this table clean.** Throughout six lessons it was assumed that the 60 rows were 60 distinct subscribers and that each row appeared once. The next topic takes up both gaps together: it measures how many rows remain when the deduplication key changes and how many values the missing-value decisions assign to the same mean, then builds the summaries that look at the whole table. ## Summary and Cleanup Measuring which decisions produce the numbers that look at the whole table: how many rows remain and how many records disappear when the deduplication key changes, the three values that imputation and exclusion give for the same mean and the separation of an blank cell from a zero, a pivot table that returns two different winners when the breakdown changes, how many charts do not fit their scale and how many times larger the axis start makes a difference look, and how many of the decisions made in the course can be read back from the table. ### Removing Duplicate Records Source: https://academia.sh/en/courses/analysis-with-spreadsheets/removing-duplicate-records Five deduplication keys applied to the same 2,037-row sheet leave five different counts: 2,005, 1,400, 1,877, 1,965 and 1,877 again. When the key is the subscriber number alone, 637 rows are deleted and the total falls from 42,645.6 m³ to 29,828.2 m³, because most of the deleted rows are second-period readings rather than duplicates; the text trimming decision alone moves 88 rows. Keeping the last entry instead of the first under the same key leaves the row count fixed at 1,877 but pulls the totals 200.1 m³ apart. Once the operation is done, the deleted sheet gives back zero of four decisions, the annotated sheet two in two steps, and the traced sheet four in one step. The previous topic established how to calculate single cells correctly: reference type, conditional sum, boundary comparison, text trimming, day counting and lookup. Their shared limit was this: every formula looked at one cell, and no number that looks at the whole table was ever produced. Whether the data itself was clean was never asked either. This lesson performs the first operation that looks at the whole table: removing duplicate records. The operation looks like a single command, but it contains at least three decisions. Which columns must match for two rows to count as the same. Whether invisible whitespace and letter case are ignored when text is compared. Which of the two rows counted as the same is the one that stays. All three move the reported number, and all three are unreadable from the table once the operation is done. These are the lesson's two numbers: the **remaining row count** and **how many decisions can be read back**. - **SC1.** The spreadsheet is **modeled** with `node`: a cell lives in a map, a formula is a small evaluator that carries its own text, and a range is a slice function. The dataset is also **fictional** — the 1,400 subscribers of the previous course, expanded into field visit records. - **SC2.** Duplicates arise from two separate causes: the same subscriber and period pair being entered a second time, and a subscriber being read in two periods. The second is not a duplicate; if the key does not tell these two apart, the delete operation deletes data. - **SC3.** The meter number is typed by hand during the field visit, so it may carry a leading space and lowercase letters. Text comparison depends on the trimming and letter case decision. - **SC4.** The recoverability of a decision is measured by scanning the sheet: if the decision is written in a cell formula it is read in **one step**, if it sits in a separate note cell it takes **two steps** — first the note is found, then the column it belongs to is matched — and if it appears in no cell it is **unreadable**. #### How Many Rows Remain When the Key Changes ```js // sheet.mjs — the spreadsheet is a MODEL: cells live in a map, a formula is a // small evaluator, a range is a slice function. No application is imitated; // tasks are named by what they do. The dataset is fictional. export const SEED = 20260218, SUBSCRIBERS = 1400; const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507); x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; }; const rng = (t) => { let s = mix(t); return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; }; const pick = (u, w) => { let t = 0; for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; }; export const REGION = [["north", "NR", 0.28, 21], ["south", "SH", 0.22, 17], ["east", "ET", 0.18, 26], ["west", "WT", 0.14, 14], ["central", "CT", 0.18, 23]] .map(([name, code, share, base]) => ({ name, code, share, base })); // Spreadsheet model. A cell name is letter+number; a value is either a plain // value or a formula. A formula carries its own text: the decision is written there. const LETTER = (j) => String.fromCharCode(65 + j); export const formula = (text, f) => ({ kind: "formula", text, f }); export class Table { constructor(header, rows) { this.header = header; this.cells = new Map(); this.n = rows.length; header.forEach((b, j) => this.cells.set(`${LETTER(j)}1`, b)); rows.forEach((s, i) => header.forEach((b, j) => this.cells.set(`${LETTER(j)}${i + 2}`, s[b]))); } read(name) { const v = this.cells.get(name); return v && v.kind === "formula" ? v.f(this) : v; } write(name, v) { this.cells.set(name, v); return this; } letter(b) { return LETTER(this.header.indexOf(b)); } range(a) { const [s, b] = a.split(":"); const j = s.charCodeAt(0) - 65, i0 = +s.slice(1), i1 = +b.slice(1); return Array.from({ length: i1 - i0 + 1 }, (_, k) => this.read(`${LETTER(j)}${i0 + k}`)); } column(b) { return this.range(`${this.letter(b)}2:${this.letter(b)}${this.n + 1}`); } rows() { return Array.from({ length: this.n }, (_, i) => Object.fromEntries(this.header.map((b) => [b, this.read(`${this.letter(b)}${i + 2}`)]))); } } // Field visit records: one or two periods per subscriber, and some of them // written twice because of manual entry. const ROWS = []; for (let i = 0; i < SUBSCRIBERS; i++) { const r = rng(SEED + i); const b = REGION[pick(r(), REGION.map((x) => x.share))]; const base = Math.max(1.2, b.base * Math.exp((r() + r() + r() - 1.5) * 0.62)); const meter = `${b.code}-${String(1000 + i).padStart(4, "0")}`; const periods = r() < 0.34 ? ["2026-01", "2026-02"] : ["2026-01"]; for (const d of periods) { const t = Math.round(base * (d === "2026-02" ? 0.94 : 1) * 100) / 100; ROWS.push({ subscriber_no: 10001 + i, meter_no: meter, region: b.name, period: d, usage_m3: t }); // Double entry: the same subscriber and period is written a second time. This // time the meter number may be entered with a space or in lowercase; the // reading may have been corrected. if (r() < 0.09) { const form = pick(r(), [0.34, 0.28, 0.38]); const correction = r() < 0.45; ROWS.push({ subscriber_no: 10001 + i, meter_no: form === 0 ? ` ${meter}` : form === 1 ? meter.toLowerCase() : meter, region: b.name, period: d, usage_m3: correction ? Math.round(t * 1.12 * 100) / 100 : t }); } } } export const SHEET = new Table( ["subscriber_no", "meter_no", "region", "period", "usage_m3"], ROWS); // Deduplication key: generates a key string from a row. When the key changes, // a different number of rows remains from the same sheet. export const CRITERIA = [ ["C1 full row", (s) => Object.values(s).join("|")], ["C2 subscriber_no", (s) => `${s.subscriber_no}`], ["C3 subscriber_no + period", (s) => `${s.subscriber_no}|${s.period}`], ["C4 meter_no + period", (s) => `${s.meter_no}|${s.period}`], ["C5 meter_no (trimmed) + period", (s) => `${s.meter_no.trim().toUpperCase()}|${s.period}`], ]; export const dedupe = (rows, key, keep = "first") => { const g = new Map(); for (const s of rows) { const a = key(s); if (!g.has(a) || keep === "last") g.set(a, s); } return [...g.values()]; }; export const sum = (a) => a.reduce((x, y) => x + y, 0); if (import.meta.url === `file://${process.argv[1]}`) { const rows = SHEET.rows(); console.log(`model (seed ${SEED}): ${SUBSCRIBERS} subscribers, sheet has ${SHEET.n} rows; ` + `raw total ${sum(SHEET.column("usage_m3")).toFixed(1)} m3`); const y = (h) => String(h[0]).padEnd(32) + h.slice(1).map((v) => String(v).padStart(11)).join(""); console.log("\n" + y(["deduplication key", "remaining", "deleted", "total m3"])); const kept = {}; for (const [name, f] of CRITERIA) { const l = dedupe(rows, f); kept[name.slice(0, 2)] = l; console.log(y([name, l.length, SHEET.n - l.length, sum(l.map((s) => s.usage_m3)).toFixed(1)])); } console.log(`\nthe loosest and the strictest criterion differ by ` + `${kept.C1.length - kept.C2.length} rows; the total shifts by ` + `${(sum(kept.C1.map((s) => s.usage_m3)) - sum(kept.C2.map((s) => s.usage_m3))).toFixed(1)} m3`); } ``` ``` model (seed 20260218): 1400 subscribers, sheet has 2037 rows; raw total 42645.6 m3 deduplication key remaining deleted total m3 C1 full row 2005 32 41949.2 C2 subscriber_no 1400 637 29828.2 C3 subscriber_no + period 1877 160 39147.7 C4 meter_no + period 1965 72 40912.2 C5 meter_no (trimmed) + period 1877 160 39147.7 the loosest and the strictest criterion differ by 605 rows; the total shifts by 12121.0 m3 ``` Five keys gave five numbers, and none of them produced an error. **C1 full row** is the key that looks safest and does the least work: it deletes only 32 of the 2,037 rows, because when a reading was corrected in a second entry the two rows are not identical. Here the caution leaves 128 rows of the problem exactly where they were. **C2 subscriber_no** gives the cleanest result — 1,400 rows, exactly one row per subscriber — and brings the reported total down from 42,645.6 m³ to 29,828.2 m³. Most of the 637 deleted rows are not duplicates but the second period's readings. Someone who looks at the sheet later sees 1,400 rows, confirms that the number is unique per subscriber, and **cannot see what is missing**: a wrong key does not produce an error message, it produces a tidy-looking table. The remaining three numbers measure the text decision. C4 compares the raw meter number and leaves 1,965 rows; C5 applies the same key with text that is trimmed and brought to one letter case, and falls to 1,877. The **88 rows** in between are the price of a single decision. That C3 and C5 give the same number does not show that the keys are equivalent: in this set they coincide because the meter number is unique per subscriber, and they would part ways once a household's second meter is added. #### Same Key, Two Keep Decisions ```js // keys.mjs — the same sheet, this time two questions: which row is kept in a // duplicate group, and whether the decision can be read back from the sheet. import { SHEET, CRITERIA, Table, formula, dedupe, sum } from "./sheet.mjs"; const rows = SHEET.rows(); const y = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j]) : String(v).padEnd(w[0]))).join(""); // The same key, two keep decisions: the first entry or the last entry in the group. const y1 = y([32, 10, 11, 12]); console.log("same criterion, two keep decisions"); console.log(y1(["criterion", "keeps", "remaining", "total m3"])); for (const [name, f] of CRITERIA.slice(2, 4)) { for (const keep of ["first", "last"]) { const l = dedupe(rows, f, keep); console.log(y1([name, keep, l.length, sum(l.map((s) => s.usage_m3)).toFixed(1)])); } } const i3 = dedupe(rows, CRITERIA[2][1], "first"), s3 = dedupe(rows, CRITERIA[2][1], "last"); console.log(`for C3 both decisions leave ${i3.length} rows, and the total differs by ` + `${(sum(s3.map((s) => s.usage_m3)) - sum(i3.map((s) => s.usage_m3))).toFixed(1)} m3`); // The trail of a single subscriber: which key swallows which row. const sample = rows.filter((s) => s.subscriber_no === 10029); const y2 = y([12, 10, 8, 6, 6, 6, 6, 6]); console.log(`\nsubscriber 10029 holds ${sample.length} rows in the sheet`); console.log(y2(["meter_no", "period", "m3", ...CRITERIA.map((o) => o[0].slice(0, 2))])); for (const s of sample) { console.log(y2([`"${s.meter_no}"`, s.period, s.usage_m3, ...CRITERIA.map(([, f]) => dedupe(sample, f).includes(s) ? "kept" : "-")])); } // Recoverability of a decision: one step if it is written in a formula, two // steps if it sits in a separate note cell, unreadable if it is in no cell. const CODE = ["D1 key columns", "D2 text trimming", "D3 retained row", "D4 deleted count"]; const readBack = (t) => CODE.map((k) => { const v = [...t.cells.values()], n = k.slice(0, 2); const f = v.some((x) => x && x.kind === "formula" && x.text.includes(n)); const note = v.some((x) => typeof x === "string" && x.startsWith("decision ") && x.includes(n)); return f ? 1 : note ? 2 : null; }); const deleted = new Table(SHEET.header, i3); const annotated = new Table(SHEET.header, i3) .write("G1", "decision D1 and D3: duplicates were removed by subscriber and period, first entry kept"); const key = (s) => `${s.subscriber_no}|${s.period}`; const seen = new Map(); const traced = new Table([...SHEET.header, "unique_key", "group_order"], rows.map((s) => { const a = key(s); seen.set(a, (seen.get(a) ?? 0) + 1); const order = seen.get(a); return { ...s, unique_key: formula("D1 subscriber_no and period are joined; D2 meter_no " + "is trimmed and brought to one letter case", () => `${s.subscriber_no}|${s.period}|${s.meter_no.trim().toUpperCase()}`), group_order: formula("D3 the first entry in a group is kept, the later ones are flagged", () => order) }; })); traced.write("H2", formula("D4 a row whose group_order is above one is a duplicate", (t) => t.column("group_order").filter((v) => v > 1).length)); const y3 = y([22, 15, 17, 14]); console.log("\ncan the decision be read back from the sheet"); console.log(y3(["decision", "deleted sheet", "annotated sheet", "traced sheet"])); const result = [deleted, annotated, traced].map(readBack); CODE.forEach((k, j) => console.log(y3([k, ...result.map((s) => (s[j] ? `${s[j]} ${s[j] === 1 ? "step" : "steps"}` : "unreadable"))]))); console.log(["deleted", "annotated", "traced"].map((name, j) => `${name} ${result[j].filter(Boolean).length}/${CODE.length}`).join(", ") + " decisions can be read back"); console.log(`traced sheet holds ${traced.n} rows and ${traced.read("H2")} flagged rows; ` + `deleted sheet holds ${deleted.n} rows and does not say where ${SHEET.n - deleted.n} ` + `of the rows went`); ``` ``` same criterion, two keep decisions criterion keeps remaining total m3 C3 subscriber_no + period first 1877 39147.7 C3 subscriber_no + period last 1877 39347.8 C4 meter_no + period first 1965 40912.2 C4 meter_no + period last 1965 41023.3 for C3 both decisions leave 1877 rows, and the total differs by 200.1 m3 subscriber 10029 holds 3 rows in the sheet meter_no period m3 C1 C2 C3 C4 C5 "NR-1028" 2026-01 12.74 kept kept kept kept kept "nr-1028" 2026-01 14.27 kept - - kept - "NR-1028" 2026-02 11.97 kept - kept kept kept can the decision be read back from the sheet decision deleted sheet annotated sheet traced sheet D1 key columns unreadable 2 steps 1 step D2 text trimming unreadable unreadable 1 step D3 retained row unreadable 2 steps 1 step D4 deleted count unreadable unreadable 1 step deleted 0/4, annotated 2/4, traced 4/4 decisions can be read back traced sheet holds 2037 rows and 160 flagged rows; deleted sheet holds 1877 rows and does not say where 160 of the rows went ``` The first table shows the course's measure plainly. Under key C3, the decision of which row is kept **does not change the row count at all** — 1,877 rows remain either way — but the totals pull 200.1 m³ apart. Someone looking at the sheet can verify the row count and find the right number; there is nothing to verify which keep decision produced the total. **Row count is not an audit measure.** The second table traces the three rows of a single subscriber. Subscriber 10029's January reading was entered twice, and in the second entry the meter number is in lowercase and the reading has drifted from 12.74 to 14.27; the third row is the February reading. Key C2 deletes two of the three rows, and of the deleted ones one is a real duplicate and one is **the February reading**. C4 deletes none, because the lowercase code counts as a different text. C3 and C5 do the right thing and leave two rows. Five keys give three different results for the same record, and what each one did can be seen only while the rows are still there. The choice between 12.74 and 14.27 is a separate decision and has nothing to do with the key: the second entry may be a correction, or it may be a second erroneous entry. The data does not say which is the correct reading; the decision has to rest on information outside the sheet. #### Where the Decision Lives The third table is the lesson's second number. The **deleted sheet** holds 1,877 tidy rows and carries **zero** of the four decisions. The table looks right, because nothing has been left that would make the table look wrong. The **annotated sheet** gives back two of the four decisions, in two steps. The person who wrote the note wrote down the key columns and the keep decision but not the text trimming — they counted that as cleaning, not as a decision. Yet the measured difference is exactly there: trimming moves 88 rows, the keep decision zero rows. Notes kept by hand miss **the quietest decision**. The **traced sheet** keeps all 2,037 rows, flags 160 of them and gives all four decisions in one step; because the decisions sit in cells as formulas, a second person who opens the sheet asks nobody in order to read them. The cost is in two places: the sheet does not shrink, and every later summary operation has to take the flag column into account. This cost is the price of the course's rule — **a spreadsheet is a calculation tool, not a record-keeping tool**; a record can be bought only with an extra column. #### Summary - The deduplication key is a key generated from the row; on the same 2,037-row sheet five keys leave 2,005, 1,400, 1,877, 1,965 and 1,877 rows. - A key based on the subscriber number alone produces the cleanest table and deletes the second period instead of the duplicates: the total falls from 42,645.6 m³ to 29,828.2 m³ and no warning appears. - The text trimming and letter case decision alone moves 88 rows. - Keeping the last entry instead of the first under the same key holds the row count fixed at 1,877 but pulls the totals 200.1 m³ apart; row count is not an audit measure. - Once the operation is done, of the four decisions the deleted sheet gives back zero, the annotated sheet two in two steps, and the traced sheet four in one step. #### Next Step Deduplication assumed that all the remaining rows were filled in. Yet in some of the 1,877 rows the consumption cell is blank, in some it says zero, and the spreadsheet separates these two in some operations and not in others. The mean of the same column gives one number when blank cells are excluded, another when blanks are counted as zero, and a third when they are imputed with the column mean. The next lesson calculates these three decisions side by side and measures whether, once the imputation is done, it is still known which cell was blank to begin with. ### Dealing with Missing Data Source: https://academia.sh/en/courses/analysis-with-spreadsheets/dealing-with-missing-data In the reading column of the same 1,877-row sheet 116 cells are blank and 112 cells are zero, and 55 of those zeros are not real zero consumption but an unread visit; the 2,719.2 m³ that corresponds to the blank cells is inside the reported total. The mean of the same column comes out as 19.23, 18.04, 20.54 and 19.23 m³ again under four decisions: 2.49 m³ between the lowest and the highest, while imputation leaves the mean unmoved and raises the total from 33,866.8 to 36,097.6 m³. The blank-cell rate varies between regions from 2.0% to 17.3%, and when blanks are counted as zero the mean of one region falls by 3.94 m³. Once the operation is done, the imputed sheet gives back zero of four decisions, the annotated sheet one in two steps, and the traced sheet four in one step. The previous lesson reduced the sheet to 1,877 rows and assumed that all the remaining rows were filled in. The assumption does not hold. In the reading column some cells are blank, some say zero, and the spreadsheet does not count the two the same way in every operation: the sum function skips the blank cell and adds the zero; the mean function drops the blank cell from both the dividend and the divisor, and the zero only from the dividend. This lesson calculates the mean of that column. The calculation is a single line, and at least three decisions stand in front of it: whether a blank cell is excluded, counted as zero, or imputed; whether the cells that say zero are real zero consumption; and which value the imputation uses. All three move the mean, and all three are unreadable from the sheet once the operation is done. These are the lesson's two numbers: **the mean the decision gives** and **how many decisions can be read back**. - **SC5.** The sheet is the deduplicated 1,877 rows of the previous lesson, but this time the reading column stands **as it was on the field form**. The spreadsheet is again **modeled** with `node` and the dataset is **fictional**. - **SC6.** Missing data shows up in three forms: the visit produced no reading and the cell is **blank**; the meter is sealed and the consumption is **really zero**; the reading could not be taken but **zero was written** on the form. - **SC7.** The sum and mean functions skip the blank cell; the exclusion decision therefore **does not move the total, it moves the mean**. - **SC8.** The recoverability measure continues: if the decision is written in a cell formula it is read in **one step**, if in a separate note cell in **two steps**, and if it is in no cell it is **unreadable**. #### The Same Column, Four Means ```js // missing.mjs — the spreadsheet is a MODEL: a cell lives in a map, a blank cell // carries no value, a formula carries its own text. No application is imitated. // The dataset is fictional: the deduplicated 1877-row sheet of the previous // lesson, this time with the reading column as it was on the field form. export const SEED = 20260218, SUBSCRIBERS = 1400; const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507); x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; }; const rng = (t) => { let s = mix(t); return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; }; const pick = (u, w) => { let t = 0; for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; }; // Last field: the rate at which a visit produces no reading. Regions differ in this rate. export const REGION = [["north", 0.28, 21, 0.06], ["south", 0.22, 17, 0.03], ["east", 0.18, 26, 0.27], ["west", 0.14, 14, 0.03], ["central", 0.18, 23, 0.08]] .map(([name, share, base, missing]) => ({ name, share, base, missing })); const LETTER = (j) => String.fromCharCode(65 + j); export const formula = (text, f) => ({ kind: "formula", text, f }); export class Table { constructor(header, rows) { this.header = header; this.cells = new Map(); this.n = rows.length; header.forEach((b, j) => this.cells.set(`${LETTER(j)}1`, b)); rows.forEach((s, i) => header.forEach((b, j) => this.cells.set(`${LETTER(j)}${i + 2}`, s[b]))); } read(name) { const v = this.cells.get(name); return v && v.kind === "formula" ? v.f(this) : v; } write(name, v) { this.cells.set(name, v); return this; } column(b) { const j = this.header.indexOf(b); return Array.from({ length: this.n }, (_, i) => this.read(`${LETTER(j)}${i + 2}`)); } } // The sum and mean functions skip the blank cell: the mean takes both the dividend // and the divisor from the filled cells only. Zero is a filled cell. export const numbers = (a) => a.filter((v) => typeof v === "number"); export const sum = (a) => numbers(a).reduce((x, y) => x + y, 0); export const mean = (a) => { const d = numbers(a); return d.length ? sum(d) / d.length : 0; }; // The unique sheet of the previous lesson: one or two periods per subscriber, // already deduplicated by the subscriber+period key. The visit result comes from // a separate generator stream; the readings do not change. unreachable -> cell is // blank. sealed -> real zero consumption. form_zero -> reading could not be taken but // zero was written on the form. export const ROWS = []; for (let i = 0; i < SUBSCRIBERS; i++) { const r = rng(SEED + i); const b = REGION[pick(r(), REGION.map((x) => x.share))]; const base = Math.max(1.2, b.base * Math.exp((r() + r() + r() - 1.5) * 0.62)); for (const d of r() < 0.34 ? ["2026-01", "2026-02"] : ["2026-01"]) { const q = rng(SEED + 7000 + ROWS.length), u = q(); const status = u < b.missing ? (q() < 0.72 ? "unreachable" : "form_zero") : u < b.missing + 0.035 ? "sealed" : "read"; const actual = Math.round(base * (d === "2026-02" ? 0.94 : 1) * 100) / 100; ROWS.push({ subscriber_no: 10001 + i, region: b.name, period: d, visit_result: status, reading_m3: status === "unreachable" ? null : status === "read" ? actual : 0, actual_m3: actual }); } } // The same column, four exclusion and imputation methods. Each method changes the // set of rows entering the calculation and the divisor. export const columnMean = mean(ROWS.map((s) => s.reading_m3)); export const METHODS = [ ["M1 blank excluded, zero counted", (l) => numbers(l.map((s) => s.reading_m3))], ["M2 blank counted as zero", (l) => l.map((s) => s.reading_m3 ?? 0)], ["M3 blank and zero excluded", (l) => numbers(l.map((s) => s.reading_m3)).filter((v) => v > 0)], ["M4 blank imputed with column mean", (l) => l.map((s) => s.reading_m3 ?? columnMean)], ]; export const THRESHOLD = 0.005; // half of the two reported decimals export const y = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j]) : String(v).padEnd(w[0]))).join(""); if (import.meta.url === `file://${process.argv[1]}`) { const withResult = (d) => ROWS.filter((s) => s.visit_result === d); console.log(`model (seed ${SEED}): ${ROWS.length} rows, reported total ` + `${sum(ROWS.map((s) => s.actual_m3)).toFixed(1)} m3; ${withResult("unreachable").length} blank ` + `cells, ${withResult("sealed").length + withResult("form_zero").length} cells hold a zero`); console.log(`of the zeros ${withResult("sealed").length} are real zero consumption, ` + `${withResult("form_zero").length} are unread visits; the ` + `${sum(withResult("unreachable").map((s) => s.actual_m3)).toFixed(1)} m3 carried by the blank cells is ` + `inside the total but rests on no reading`); const y1 = y([42, 9, 11, 12]); console.log("\n" + y1(["same column, same question, four methods", "rows", "mean", "total m3"])); const o = {}; for (const [name, f] of METHODS) { const v = f(ROWS); o[name.slice(0, 2)] = mean(v); console.log(y1([name, v.length, mean(v).toFixed(2), sum(v).toFixed(1)])); } const [lo, hi] = [Math.min(...Object.values(o)), Math.max(...Object.values(o))]; console.log(`\nlowest ${lo.toFixed(2)}, highest ${hi.toFixed(2)}, difference ` + `${(hi - lo).toFixed(2)} m3; M1 and M4 give ` + `${Math.abs(o.M1 - o.M4) < THRESHOLD ? "the same" : "a different"} mean ` + `(threshold ${THRESHOLD} m3, half of the two reported decimals)`); } ``` ``` model (seed 20260218): 1877 rows, reported total 39147.7 m3; 116 blank cells, 112 cells hold a zero of the zeros 57 are real zero consumption, 55 are unread visits; the 2719.2 m3 carried by the blank cells is inside the total but rests on no reading same column, same question, four methods rows mean total m3 M1 blank excluded, zero counted 1761 19.23 33866.8 M2 blank counted as zero 1877 18.04 33866.8 M3 blank and zero excluded 1649 20.54 33866.8 M4 blank imputed with column mean 1877 19.23 36097.6 lowest 18.04, highest 20.54, difference 2.49 m3; M1 and M4 give the same mean (threshold 0.005 m3, half of the two reported decimals) ``` The sheet has 1,877 rows and a reported total of 39,147.7 m³; both come from the previous lesson. What is new is this: in 116 cells there is no reading at all, and the **2,719.2 m³** that corresponds to them is inside that total. In the column 112 cells say zero, but only 57 of them are real zero consumption; the remaining **55 are a visit where no reading could be taken**. In the sheet there is no difference between the two: a blank cell gives itself away, while a missing value written as zero **presents itself as a measurement**. The numbers of the four decisions confirm this. The mean runs between 18.04 and 20.54; the **2.49 m³** in between is about 14% of the lower value. M1 counts the zeros as measurements and excludes the blanks; M2 counts the blanks as zero too, the divisor rises from 1,761 to 1,877, and the mean falls; M3 trusts no zero and gives the highest value with 1,649 rows. The total is **fixed at 33,866.8 m³** in the first three decisions: exclusion does not move the total at all, because the sum function was skipping the blank cell anyway. The fourth row does not give what the previous lesson would lead one to expect: imputing with the column mean does not produce a third mean, it gives **the same as M1** — adding a value equal to the mean does not change the mean. The comparison is made with a threshold of 0.005 m³ — the number is reported with two decimals, and floating-point values are not compared with an equality operator. The real warning is here: while it preserves the mean, imputation **raises the total to 36,097.6 m³** and adds 116 invented measurements to the sheet. That the mean does not change proves not that imputation is harmless but that the mean does not show the imputation. #### What Remains After the Imputation ```js // imputation.mjs — the same sheet, three questions: how much the imputation decision // moves the region breakdown, whether imputation leaves a trace in the sheet, and // whether the decision can be read back from the sheet. import { ROWS, REGION, Table, formula, numbers, mean, columnMean, THRESHOLD, y } from "./missing.mjs"; // Because missingness is not spread evenly across regions, the same decision does // not move every region by the same amount: the region with the most blank cells // moves the most. const regions = REGION.map((b) => { const l = ROWS.filter((s) => s.region === b.name); return { name: b.name, m1: mean(l.map((s) => s.reading_m3)), m2: mean(l.map((s) => s.reading_m3 ?? 0)), rate: 100 * l.filter((s) => s.reading_m3 === null).length / l.length }; }).sort((a, b) => b.rate - a.rate); const [top, bottom] = [regions[0], regions[regions.length - 1]]; console.log(`blank-cell rate is ${bottom.rate.toFixed(1)}% in the ${bottom.name} region and ` + `${top.rate.toFixed(1)}% in the ${top.name} region; if blank cells are counted as zero the mean ` + `of the ${top.name} region falls from ${top.m1.toFixed(2)} m3 to ${top.m2.toFixed(2)} m3, ` + `and that of the ${bottom.name} region from ${bottom.m1.toFixed(2)} m3 to ${bottom.m2.toFixed(2)} m3`); // Does the imputation leave a trace in the sheet: the cells carrying the imputed value // are counted. Floating-point comparison is done with a threshold; the threshold is // half of the two decimals. const blank = ROWS.filter((s) => s.reading_m3 === null).length; const traceMean = ROWS.filter((s) => Math.abs((s.reading_m3 ?? columnMean) - columnMean) < THRESHOLD).length; const traceZero = ROWS.filter((s) => (s.reading_m3 ?? 0) === 0).length; console.log(`the ${blank} cells imputed with the column mean show up in ${traceMean} cells of ` + `the sheet, detection ${(100 * blank / traceMean).toFixed(0)}%; had the same cells been imputed ` + `with zero, detection among ${traceZero} zero cells would have been ` + `${(100 * blank / traceZero).toFixed(0)}%`); // Recoverability of a decision: one step if it is written in a formula, two steps // if it sits in a separate note cell, unreadable if it is in no cell. const CODE = ["D1 which cell was blank", "D2 imputation method", "D3 meaning of zero", "D4 rows used in the calculation"]; const readBack = (t) => CODE.map((k) => { const v = [...t.cells.values()], n = k.slice(0, 2); const f = v.some((x) => x && x.kind === "formula" && x.text.includes(n)); const note = v.some((x) => typeof x === "string" && x.startsWith("decision ") && x.includes(n)); return f ? 1 : note ? 2 : null; }); const imputedValue = Math.round(columnMean * 100) / 100; const HEADER = ["subscriber_no", "region", "period", "reading_m3"]; const cleaned = ROWS.map(({ subscriber_no, region, period, reading_m3 }) => ({ subscriber_no, region, period, reading_m3: reading_m3 ?? imputedValue })); const imputedSheet = new Table(HEADER, cleaned); const annotated = new Table(HEADER, cleaned) .write("F1", "decision D2: blank cells were imputed with the column mean"); const traced = new Table([...HEADER, "visit_result", "calc_m3"], ROWS.map((s) => ({ subscriber_no: s.subscriber_no, region: s.region, period: s.period, reading_m3: s.reading_m3, visit_result: s.visit_result, calc_m3: formula("D1 if visit_result is unreachable the reading cell is blank; D2 a blank " + "cell is imputed with the column mean; D3 zero is real consumption on a sealed meter and an " + "unread visit under form_zero", () => s.reading_m3 ?? imputedValue) }))); traced.write("H2", formula("D4 the rows entering the calculation are counted from the filled " + "reading cells", (t) => numbers(t.column("reading_m3")).length)); const y3 = y([33, 14, 17, 14]); console.log("\ncan the decision be read back from the sheet"); console.log(y3(["decision", "imputed sheet", "annotated sheet", "traced sheet"])); const result = [imputedSheet, annotated, traced].map(readBack); CODE.forEach((k, j) => console.log(y3([k, ...result.map((s) => (s[j] ? `${s[j]} ${s[j] === 1 ? "step" : "steps"}` : "unreadable"))]))); console.log(["imputed", "annotated", "traced"].map((name, j) => `${name} ${result[j].filter(Boolean).length}/${CODE.length}`).join(", ") + " decisions can be read back"); ``` ``` blank-cell rate is 2.0% in the west region and 17.3% in the east region; if blank cells are counted as zero the mean of the east region falls from 22.80 m3 to 18.86 m3, and that of the west region from 13.62 m3 to 13.35 m3 the 116 cells imputed with the column mean show up in 116 cells of the sheet, detection 100%; had the same cells been imputed with zero, detection among 228 zero cells would have been 51% can the decision be read back from the sheet decision imputed sheet annotated sheet traced sheet D1 which cell was blank unreadable unreadable 1 step D2 imputation method unreadable 2 steps 1 step D3 meaning of zero unreadable unreadable 1 step D4 rows used in the calculation unreadable unreadable 1 step imputed 0/4, annotated 1/4, traced 4/4 decisions can be read back ``` The first line says where the missingness piles up: the blank-cell rate is 2.0% in the `west` region and 17.3% in the `east` region; the measurement network is failing systematically in one region. This is why the same decision does not move every region by the same amount: when the blanks are counted as zero, the mean of the `east` region falls from 22.80 to **18.86** — 3.94 m³, without a single measurement changing — and that of the `west` region falls by 0.27 m³. A decision that looks small at the column level grows in the breakdown. The second line counts whether the imputation leaves a trace. The 116 cells imputed with the column mean can be told apart exactly, because none of the real readings lands on 19.23; had the same cells been imputed with zero, detection among 228 zeros would have dropped to 51%. The difference comes from **how rarely the imputed value occurs in the data**, not from whether the decision is written down; a guess, however accurate, is not a record. #### Where the Decision Lives The last table is the lesson's second number. The **imputed sheet** shows all 1,877 rows as filled and carries **zero** of the four decisions. It is the same result as the deleted sheet in the previous lesson, but worse: deleting lowers the row count and one can see that something is missing, while imputation does not move the row count at all. The **annotated sheet** gives back only one of the four decisions, the imputation method, in two steps; knowing the method does not say which cells it was applied to. In the previous lesson the note gave two decisions, here **one**: a note kept by hand is written looking at the sheet, and what is not visible in the sheet once the imputation is done is exactly which cell was imputed. The **traced sheet** leaves the raw reading column with its 116 blank cells, carries the visit result in a separate column and produces the calculation in a formula column; all four decisions are read in one step. The sheet holds 1,761 filled readings and the 1,877-row calculation at the same time; the 116 difference between them is no longer a gap, it is **a written decision**. The cost is two extra columns: a spreadsheet is a calculation tool, not a record-keeping tool, and a record is bought with columns. #### Summary - In the reading column 116 cells are blank, and the 2,719.2 m³ that corresponds to them is inside the reported 39,147.7 m³. - Of the 112 cells that say zero, 57 are real zero consumption and 55 are unread visits, and in the sheet the two cannot be told apart. - The mean of the same column comes out as 19.23, 18.04, 20.54 and 19.23 m³ again under four decisions; the total stays fixed at 33,866.8 m³ in the first three decisions, because the sum function skips the blank cell anyway. - Imputation does not change the mean beyond the 0.005 m³ threshold but raises the total to 36,097.6 m³; the region with a blank rate of 17.3% falls by 3.94 m³ when blanks are counted as zero. - Once the operation is done, of the four decisions the imputed sheet gives back zero, the annotated sheet one in two steps, and the traced sheet four in one step. #### Next Step These two lessons corrected the sheet itself; in both, a single column was examined. A report does not want a single column, it wants a breakdown. The pivot table does this work and carries its own decisions: which field becomes the breakdown, whether a sum or a mean is taken, whether the filter is applied before or after the summary, at which level the calculated field is produced. The next lesson builds three breakdowns from the same data, shows that the three give three different "first" places, and counts in how many steps the decisions of the summary can be read. ### Pivot Tables Source: https://academia.sh/en/courses/analysis-with-spreadsheets/pivot-tables Five summary decisions applied to the same 1,761-row sheet make three different regions come out first: north with 10,905.8 m³ in total consumption, east with 22.80 m³ in the mean per row, central with 28.39 m³ in the mean per subscriber. When the region mean is produced from the cells of the cross tabulation the order holds, but the 3.35 m³ gap between south and west shrinks to 0.04 m³. When the same 20 m³ threshold is applied after the summary three regions pass, and when it is applied before the summary all five pass and the value of the east region rises from 22.80 to 29.03 m³. The pivot table gives two of its four decisions in the header row in one step, and never gives the position of the filter or the level of the calculated field. The previous lesson asked for the mean of a single column and found four decisions in front of it. A report does not ask that question; it asks "which region is first". This lesson hands that question to the **pivot table** and counts the pivot table's own decisions. The sheet is the previous lesson's M1 decision carried out: blank readings excluded, zeros counted, 1,761 rows and 33,866.8 m³. From here on the decisions belong to the summary: which field becomes the breakdown, which measure is taken, where the filter is applied, at which level the calculated field is produced. These are the lesson's two numbers: **the first place the decision produces** and **how many decisions can be read back**. - **SC9.** The spreadsheet is again **modeled** with `node` and the dataset is **fictional**. The tariff tier is derived from the reading; the thresholds are 10, 25 and 40 m³ and the comparison is made with less-than. - **SC10.** A pivot table consists of at least two decisions: the **breakdown field** and the **measure function**. Both are defensible, and both produce a different number from the same sheet. - **SC11.** The **grain** of the breakdown is a separate decision: a row is one reading, a subscriber carries one or two readings, and the two measures order the same sheet differently. - **SC12.** The recoverability measure continues and gains a place: the **header row** of the pivot table writes the breakdown field and the measure by name, so those two decisions are read in **one step**. The filter and the level do not show up in the header. #### The Same Sheet, Three Different First Places ```js // pivot.mjs — the spreadsheet is a MODEL: a cell lives in a map, a formula carries its // own text, a pivot table is the result of grouping and aggregation decisions. No // application is imitated. The dataset is fictional: the sheet of the previous // lesson after its M1 decision -- blank readings excluded, zeros counted, 1761 rows. export const SEED = 20260218, SUBSCRIBERS = 1400, TARIFF = [10, 25, 40]; const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507); x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; }; const rng = (t) => { let s = mix(t); return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; }; const pick = (u, w) => { let t = 0; for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; }; export const REGION = [["north", 0.28, 21, 0.06], ["south", 0.22, 17, 0.03], ["east", 0.18, 26, 0.27], ["west", 0.14, 14, 0.03], ["central", 0.18, 23, 0.08]] .map(([name, share, base, missing]) => ({ name, share, base, missing })); const LETTER = (j) => String.fromCharCode(65 + j); export const formula = (text, f) => ({ kind: "formula", text, f }); export class Table { constructor(header, rows) { this.header = header; this.cells = new Map(); this.n = rows.length; header.forEach((b, j) => this.cells.set(`${LETTER(j)}1`, b)); rows.forEach((s, i) => header.forEach((b, j) => this.cells.set(`${LETTER(j)}${i + 2}`, s[b]))); } read(name) { const v = this.cells.get(name); return v && v.kind === "formula" ? v.f(this) : v; } write(name, v) { this.cells.set(name, v); return this; } } export const sum = (a) => a.reduce((x, y) => x + y, 0); export const mean = (a) => (a.length ? sum(a) / a.length : 0); // The sheet of the previous two lessons: 1761 rows with a reading, out of the // deduplicated 1877 rows. The tariff tier is derived from the reading; the thresholds // are 10, 25 and 40 m3 and the comparison is made with less-than at every tier. export const ROWS = []; let index = 0; for (let i = 0; i < SUBSCRIBERS; i++) { const r = rng(SEED + i); const b = REGION[pick(r(), REGION.map((x) => x.share))]; const base = Math.max(1.2, b.base * Math.exp((r() + r() + r() - 1.5) * 0.62)); for (const d of r() < 0.34 ? ["2026-01", "2026-02"] : ["2026-01"]) { const q = rng(SEED + 7000 + index++), u = q(); const status = u < b.missing ? (q() < 0.72 ? "unreachable" : "form_zero") : u < b.missing + 0.035 ? "sealed" : "read"; const actual = Math.round(base * (d === "2026-02" ? 0.94 : 1) * 100) / 100; if (status === "unreachable") continue; const reading = status === "read" ? actual : 0; ROWS.push({ subscriber_no: 10001 + i, region: b.name, period: d, reading_m3: reading, tier: reading < TARIFF[0] ? 1 : reading < TARIFF[1] ? 2 : reading < TARIFF[2] ? 3 : 4 }); } } // Pivot table: one breakdown field, one measure function. Both are decisions. export const summarize = (rows, breakdown, measure) => { const g = new Map(); for (const s of rows) { const k = breakdown(s); if (!g.has(k)) g.set(k, []); g.get(k).push(s); } return [...g].map(([k, l]) => ({ k, n: l.length, v: measure(l) })) .sort((a, b) => b.v - a.v); }; // Measure per subscriber: a subscriber's two period readings are first summed within // themselves. The "grain" of the breakdown becomes the subscriber, not the row, and // that is a decision too. export const perSubscriber = (l) => { const m = new Map(); for (const s of l) m.set(s.subscriber_no, (m.get(s.subscriber_no) ?? 0) + s.reading_m3); return mean([...m.values()]); }; export const BREAKDOWN = [ ["B1 region / total consumption", (l) => sum(l.map((s) => s.reading_m3)), 1], ["B2 region / reading count", (l) => l.length, 0], ["B3 region / subscriber count", (l) => new Set(l.map((s) => s.subscriber_no)).size, 0], ["B4 region / mean per row", (l) => mean(l.map((s) => s.reading_m3)), 2], ["B5 region / mean per subscriber", perSubscriber, 2], ]; export const y = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j]) : String(v).padEnd(w[0]))).join(""); if (import.meta.url === `file://${process.argv[1]}`) { console.log(`model (seed ${SEED}): sheet ${ROWS.length} rows, ` + `${new Set(ROWS.map((s) => s.subscriber_no)).size} subscribers, total ` + `${sum(ROWS.map((s) => s.reading_m3)).toFixed(1)} m3`); const names = REGION.map((b) => b.name), y1 = y([36, 9, 8, 8, 8, 9, 9]); console.log("\n" + y1(["same sheet, five summary decisions", ...names, "first"])); const firsts = new Set(); for (const [name, o, d] of BREAKDOWN) { const t = summarize(ROWS, (s) => s.region, o); firsts.add(t[0].k); const m = Object.fromEntries(t.map((x) => [x.k, x.v])); console.log(y1([name, ...names.map((a) => m[a].toFixed(d)), t[0].k])); } console.log(`\nfive decisions make ${firsts.size} different regions first: ` + `${[...firsts].join(", ")}; not a single reading changed`); } ``` ``` model (seed 20260218): sheet 1761 rows, 1345 subscribers, total 33866.8 m3 same sheet, five summary decisions north south east west central first B1 region / total consumption 10905.8 6023.5 5791.5 3963.8 7182.3 north B2 region / reading count 530 355 254 291 331 north B3 region / subscriber count 400 276 209 207 253 north B4 region / mean per row 20.58 16.97 22.80 13.62 21.70 east B5 region / mean per subscriber 27.26 21.82 27.71 19.15 28.39 central five decisions make 3 different regions first: north, east, central; not a single reading changed ``` All five decisions are correct, all five read the same 1,761 rows, and three of them put a different region first. **B1 total consumption** gives north, 10,905.8 m³; the reason is right below it: north carries 530 readings and 400 subscribers. The total is not a measure of level but a measure of **volume**, and it brings the most crowded region to the front. **B4 mean per row** gives east, 22.80 m³. East holds only 254 readings in the sheet; it is last in the reading count and first in consumption per reading. The sentence "the region where consumption is highest" thus switches between north and east, and both are correct: they answer two different questions. **B5 mean per subscriber** gives a third region, central, with 28.39 m³. The difference here is not in the measure but in the **grain** of the breakdown: B4 counts every row as one unit, B5 first sums each subscriber's period readings within themselves. The central region's 331 readings come from 253 subscribers, the east's 254 readings from 209, and the share of subscribers whose second period was read puts central 0.68 m³ ahead. All three sentences can be written into the report, and all three are free of calculation errors. The difference between them is a single decision, and that decision does not stand next to the number it produced. #### Where the Filter and the Calculated Field Stand ```js // breakdown.mjs — the same sheet, three questions: at which level the calculated field // is produced, whether the filter is applied before or after the summary, and whether // the decisions of the pivot table are read back from the sheet. import { ROWS, REGION, Table, formula, summarize, sum, mean, y } from "./pivot.mjs"; // The calculated field can be produced at two levels: from the raw rows, or from the // cross of region and tariff tier. The second gives equal weight to every tier cell: // the tier cell carrying the fewest readings weighs as much as the one carrying the most. const level = Object.fromEntries(REGION.map((b) => { const l = ROWS.filter((s) => s.region === b.name); return [b.name, [mean(l.map((s) => s.reading_m3)), mean(summarize(l, (s) => s.tier, (g) => mean(g.map((s) => s.reading_m3))).map((h) => h.v))]]; })); const order = (j) => Object.entries(level).sort((a, b) => b[1][j] - a[1][j]).map((x) => x[0]); console.log(`region mean taken from raw rows gives the order ${order(0).join(" > ")}, ` + `taken from the cells of the cross tabulation gives ${order(1).join(" > ")}`); console.log(`the order held, the gaps did not: north rises from ${level.north[0].toFixed(2)} m3 to ` + `${level.north[1].toFixed(2)} m3, and the gap between south and west, ` + `${(level.south[0] - level.west[0]).toFixed(2)} m3, becomes ` + `${(level.south[1] - level.west[1]).toFixed(2)} m3`); // The same filter in two places: before the summary on the rows, after the summary on the summary rows. const THRESHOLD_M3 = 20; const y2 = y([18, 7, 38]); console.log(`\nfilter "mean above ${THRESHOLD_M3} m3": where is it applied`); console.log(y2(["filter position", "count", "regions passing"])); const after = summarize(ROWS, (s) => s.region, (l) => mean(l.map((s) => s.reading_m3))) .filter((x) => x.v > THRESHOLD_M3); const before = summarize(ROWS.filter((s) => s.reading_m3 > THRESHOLD_M3), (s) => s.region, (l) => mean(l.map((s) => s.reading_m3))); console.log(y2(["after summary", after.length, after.map((x) => x.k).join(" ")])); console.log(y2(["before summary", before.length, before.map((x) => x.k).join(" ")])); console.log(`same threshold, same sheet: in one place ${after.length} regions pass, in the other ` + `${before.length}; the value of the ${after[0].k} region goes from ${after[0].v.toFixed(2)} m3 to ` + `${before.find((x) => x.k === after[0].k).v.toFixed(2)} m3`); // Recoverability of a decision: one step if it is written in the header row or in a cell // formula, two steps if it sits in a separate note cell, unreadable if it is in no cell. const CODE = ["D1 breakdown field", "D2 measure function", "D3 filter position", "D4 calculated field level"]; const readBack = (t) => CODE.map((k) => { const n = k.slice(0, 2), v = [...t.cells.values()]; if (t.header.some((b) => b.includes(n))) return 1; if (v.some((x) => x && x.kind === "formula" && x.text.includes(n))) return 1; if (v.some((x) => typeof x === "string" && x.startsWith("decision ") && x.includes(n))) return 2; return null; }); const HEAD = ["D1 breakdown: region", "D2 measure: total consumption_m3", "reading count"]; const records = summarize(ROWS, (s) => s.region, (l) => sum(l.map((s) => s.reading_m3))) .map((x) => ({ [HEAD[0]]: x.k, [HEAD[1]]: Math.round(x.v * 10) / 10, [HEAD[2]]: x.n })); const summarySheet = new Table(HEAD, records); const annotated = new Table(HEAD, records) .write("E1", "decision D3: the filter was applied to the summary rows after the summary"); const traced = new Table([...HEAD, "source", "level"], records.map((x) => ({ ...x, source: formula("D3 the filter is applied to the summary rows after the summary; " + "no row is removed before the summary", () => x[HEAD[2]]), level: formula("D4 the calculated field is produced from the raw rows, not from the summary cells", () => x[HEAD[1]] / x[HEAD[2]]) }))); const y3 = y([30, 15, 17, 14]); console.log("\nare the decisions of the pivot table read back from the sheet"); console.log(y3(["decision", "summary sheet", "annotated sheet", "traced sheet"])); const result = [summarySheet, annotated, traced].map(readBack); CODE.forEach((k, j) => console.log(y3([k, ...result.map((s) => (s[j] ? `${s[j]} ${s[j] === 1 ? "step" : "steps"}` : "unreadable"))]))); console.log(["summary sheet", "annotated", "traced"].map((name, j) => `${name} ${result[j].filter(Boolean).length}/${CODE.length}`).join(", ") + " decisions can be read back"); ``` ``` region mean taken from raw rows gives the order east > central > north > south > west, taken from the cells of the cross tabulation gives east > central > north > south > west the order held, the gaps did not: north rises from 20.58 m3 to 23.27 m3, and the gap between south and west, 3.35 m3, becomes 0.04 m3 filter "mean above 20 m3": where is it applied filter position count regions passing after summary 3 east central north before summary 5 east central north south west same threshold, same sheet: in one place 3 regions pass, in the other 5; the value of the east region goes from 22.80 m3 to 29.03 m3 are the decisions of the pivot table read back from the sheet decision summary sheet annotated sheet traced sheet D1 breakdown field 1 step 1 step 1 step D2 measure function 1 step 1 step 1 step D3 filter position unreadable 2 steps 1 step D4 calculated field level unreadable unreadable 1 step summary sheet 2/4, annotated 3/4, traced 4/4 decisions can be read back ``` The first two lines measure at which level the calculated field is produced. If the region mean is taken from the raw rows, every reading carries one weight; if it is taken from the cross of region and tariff tier, a tier with few people has as much say as a crowded one. The order holds in this dataset but **the gaps do not**: the mean of the north region rises from 20.58 to 23.27 m³, and the gap between south and west falls from 3.35 m³ to 0.04 m³. That the order is preserved is not an assurance: that gap has shrunk by a factor of nearly eighty. The next table puts the same threshold in two different places. When the question "the regions whose mean is above 20 m³" is applied to the summary rows, **three** regions pass; when the same threshold is applied first to the raw rows and the summary is taken afterwards, **all five** pass, because in the second case each region is computed only from its own high readings. The value of the east region goes from 22.80 m³ to 29.03 m³. The threshold is 20 m³ and was chosen because it sits in the middle of the tariff tiers; the comparison looks at whether the threshold is exceeded, not at a floating-point equality. #### Where the Decision Lives The last table is the lesson's second number, and for the first time in the course it does not start from zero. The **summary sheet** gives two of the four decisions in one step, because the header row writes the breakdown field and the measure by name. The pivot table is the first tool that **automatically** puts a part of its own definition next to the number it produces. The two decisions it does not carry are exactly the two that were measured: the position of the filter decides the difference between three regions and five regions, the level of the calculated field brings 3.35 m³ down to 0.04 m³, and neither shows in the header. The **annotated sheet** rises to three by writing the filter, and does not write the level, because the level looks not like a decision but like the calculation itself. The **traced sheet** carries the two decisions in one formula column each and all four are read in one step; the cost is again columns. The part the pivot table carries automatically is the cheapest one: it writes what is easy to name, and does not write what moves the number the most. #### Summary - Five summary decisions applied to the same 1,761-row sheet make three different regions come out first: north with 10,905.8 m³ in total, east with 22.80 m³ in the mean per row, central with 28.39 m³ in the mean per subscriber. - The total is a measure of volume: the east region is last in the reading count with 254 readings and first in consumption per reading. The grain of the breakdown is also a separate decision, and the measure per subscriber puts central 0.68 m³ ahead of east. - When the region mean is produced from the cells of the cross tabulation the order holds, but the gap between south and west shrinks from 3.35 m³ to 0.04 m³. - The same 20 m³ threshold lets three regions pass after the summary and five before it, and the value of the east region rises from 22.80 to 29.03 m³. - The pivot table gives two of its four decisions in the header row in one step; the position of the filter is read in two steps with a note, and the level is read only with a formula. #### Next Step The pivot table produced the number, but what goes into a report is often not the number but the picture of the number. The chart adds two more decisions on top of the summary: which chart type is chosen and where the axis starts. The first is a question of fit; the second is a question of magnification: the same difference looks one way when the axis starts at zero and another when it starts near the smallest value. The next lesson counts how many of a set of charts do not fit their scale and measures how many times larger the same difference looks when the axis start changes. ### Basic Charts Source: https://academia.sh/en/courses/analysis-with-spreadsheets/basic-charts Four of ten chart suggestions can be drawn but do not fit the scale of the field or the summability of the measure: a line over a nominal axis, a line over an ordinal axis, a scatter over a field that is a label, and a pie from means. When the same five region means are drawn with two axis baselines, the actual ratio of 1.67 between east and west gives a bar length ratio of 1.63 when the axis starts at zero and 13.00 when it starts at 13 m³; the same difference looks 8.0 times larger and not a single reading changes. The chart reports to the sheet only the range it draws: of four decisions, one can be read back from the charted sheet, two from the annotated sheet and four from the traced sheet. The previous lesson counted the four decisions of the pivot table and showed that two of them never stand in the sheet. What goes into a report is often not that number but the picture of the number. The chart adds two more decisions on top of the summary: **which type is chosen** and **where the axis starts**. The first is a question of fit, the second a question of magnification. These are the lesson's two numbers: **how many of a set of charts do not fit the scale of the field** and **how many times larger the same difference looks** when the axis baseline changes. Next to them stands the course's second measure: how many of the chart's decisions can be read back from the sheet. - **SC13.** The spreadsheet is again **modeled** with `node` and the dataset is **fictional**. In this model a chart consists of a **range** and a few **settings**, and the settings do not live in the cells of the sheet. - **SC14.** The scale of the fields was determined in the first course of this curriculum and is not repeated here. The only thing counted is **whether the chart type fits that scale**. - **SC15.** Every type asks something of its axis and its measure: the line chart an **ordered and equally spaced** axis, the pie and the stacked bar **slices that form a whole**, the scatter two numeric axes. A mean and a ratio cannot be summed, so they cannot be slices. - **SC16.** The recoverability measure continues: if the decision is written in a cell formula it is read in **one step**, if in a separate note cell in **two steps**, and if it is in no cell it is **unreadable**. #### How Many Charts Do Not Fit Their Scale ```js // chart.mjs — the spreadsheet is a MODEL: a cell lives in a map, a formula carries its // own text, a chart consists of a range and a few settings. No application is // imitated, no tool name appears. The dataset is fictional: the summarized // 1761-row sheet of the previous lesson. export const SEED = 20260218, SUBSCRIBERS = 1400, TARIFF = [10, 25, 40]; const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507); x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; }; const rng = (t) => { let s = mix(t); return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; }; const pick = (u, w) => { let t = 0; for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; }; export const REGION = [["north", 0.28, 21, 0.06], ["south", 0.22, 17, 0.03], ["east", 0.18, 26, 0.27], ["west", 0.14, 14, 0.03], ["central", 0.18, 23, 0.08]] .map(([name, share, base, missing]) => ({ name, share, base, missing })); const LETTER = (j) => String.fromCharCode(65 + j); export const formula = (text, f) => ({ kind: "formula", text, f }); export class Table { constructor(header, rows) { this.header = header; this.cells = new Map(); this.n = rows.length; header.forEach((b, j) => this.cells.set(`${LETTER(j)}1`, b)); rows.forEach((s, i) => header.forEach((b, j) => this.cells.set(`${LETTER(j)}${i + 2}`, s[b]))); } read(name) { const v = this.cells.get(name); return v && v.kind === "formula" ? v.f(this) : v; } write(name, v) { this.cells.set(name, v); return this; } } export const sum = (a) => a.reduce((x, y) => x + y, 0); export const mean = (a) => (a.length ? sum(a) / a.length : 0); export const ROWS = []; let index = 0; for (let i = 0; i < SUBSCRIBERS; i++) { const r = rng(SEED + i); const b = REGION[pick(r(), REGION.map((x) => x.share))]; const base = Math.max(1.2, b.base * Math.exp((r() + r() + r() - 1.5) * 0.62)); for (const d of r() < 0.34 ? ["2026-01", "2026-02"] : ["2026-01"]) { const q = rng(SEED + 7000 + index++), u = q(); const status = u < b.missing ? (q() < 0.72 ? "unreachable" : "form_zero") : u < b.missing + 0.035 ? "sealed" : "read"; if (status === "unreachable") continue; const reading = status === "read" ? Math.round(base * (d === "2026-02" ? 0.94 : 1) * 100) / 100 : 0; ROWS.push({ subscriber_no: 10001 + i, region: b.name, period: d, reading_m3: reading, tier: reading < TARIFF[0] ? 1 : reading < TARIFF[1] ? 2 : reading < TARIFF[2] ? 3 : 4 }); } } // The scales of the fields were determined in the previous course; here only whether // the chart type fits that scale is counted. The order of the scales is the order of coverage. export const SCALE = { nominal: 0, ordinal: 1, interval: 2, ratio: 3 }; export const FIELD = { region: "nominal", subscriber_no: "nominal", tier: "ordinal", period: "interval", reading_m3: "ratio" }; // Every chart type asks something of its axis and its measure. // axisMin / axisMax: if the scale of the axis field is outside this range the type does not fit. // whole: the sum of the slices has to form a whole. export const TYPE = { "bar": { axisMin: 0, axisMax: 3, whole: false }, "line": { axisMin: 2, axisMax: 3, whole: false }, "pie": { axisMin: 0, axisMax: 1, whole: true }, "stacked bar": { axisMin: 0, axisMax: 1, whole: true }, "scatter": { axisMin: 2, axisMax: 3, whole: false }, }; // Suggested charts: axis field, measure, type, and whether the measure can be summed. // A mean and a ratio cannot be summed: their slices do not form a whole when added. export const SUGGESTIONS = [ ["region / total m3", "region", "bar", true], ["region / total m3", "region", "pie", true], ["region / total m3", "region", "line", true], ["region / mean m3", "region", "bar", false], ["region / mean m3", "region", "pie", false], ["tier / subscriber count", "tier", "bar", true], ["tier / subscriber count", "tier", "line", true], ["region x tier / subscribers", "region", "stacked bar", true], ["period / mean m3", "period", "line", false], ["subscriber_no / reading_m3", "subscriber_no", "scatter", false], ]; export const fit = ([, field, type, summable]) => { const o = SCALE[FIELD[field]], t = TYPE[type]; if (o < t.axisMin || o > t.axisMax) return "axis scale"; if (t.whole && !summable) return "slices do not form a whole"; return "fits"; }; export const y = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j]) : String(v).padEnd(w[0]))).join(""); if (import.meta.url === `file://${process.argv[1]}`) { console.log(`model (seed ${SEED}): sheet ${ROWS.length} rows, ` + `total ${sum(ROWS.map((s) => s.reading_m3)).toFixed(1)} m3`); const y1 = y([29, 16, 14, 16, 28]); console.log("\n" + y1(["suggested chart", "axis field", "scale", "type", "result"])); let misfit = 0; for (const o of SUGGESTIONS) { const s = fit(o); if (s !== "fits") misfit++; console.log(y1([o[0], o[1], FIELD[o[1]], o[2], s])); } console.log(`\nof ${SUGGESTIONS.length} chart suggestions ${misfit} can be drawn but do not ` + `fit the scale of the field or the summability of the measure; ` + `${SUGGESTIONS.length - misfit} fit`); } ``` ``` model (seed 20260218): sheet 1761 rows, total 33866.8 m3 suggested chart axis field scale type result region / total m3 region nominal bar fits region / total m3 region nominal pie fits region / total m3 region nominal line axis scale region / mean m3 region nominal bar fits region / mean m3 region nominal pie slices do not form a whole tier / subscriber count tier ordinal bar fits tier / subscriber count tier ordinal line axis scale region x tier / subscribers region nominal stacked bar fits period / mean m3 period interval line fits subscriber_no / reading_m3 subscriber_no nominal scatter axis scale of 10 chart suggestions 4 can be drawn but do not fit the scale of the field or the summability of the measure; 6 fit ``` Every one of the ten suggestions can be drawn, and none of them produces an error. Six fit, **four do not fit**, and the misfit shows up in three different forms. Three charts get stuck on the **axis scale**. The region field is nominal: there is no order among the five regions, so a line joining the points claims that there is an intermediate value between two regions, and there is no such value. The same line would give a completely different curve if the regions were arranged in another order in the sheet. The tier field is ordinal and there is an order, but the distance between tiers is undefined; the slope has no unit. The subscriber number is a label, and when it is put on the horizontal axis of a scatter it shows no trend — the correlation between number and reading is zero; the only thing ordered on the horizontal axis is the order in which the subscribers were registered. The fourth gets stuck not on the axis but on the **measure**. Drawing a pie from region means makes slices out of a measure whose slices cannot be summed: the sum of five means is nothing, and the percentage of every slice is computed against that nothing. A pie drawn from the **total** consumption of the same five regions fits, because totals really do form a whole. Even when the scale is right, the summability of the measure is a separate condition. #### The Same Five Numbers, Two Axes ```js // axis.mjs — the same five numbers, two axis baselines; then whether the decisions of // the chart can be read back from the sheet. The bars are drawn as text, because what // is measured is not the form but the length ratio. import { ROWS, REGION, Table, formula, mean, y } from "./chart.mjs"; const WIDTH = 26; const values = REGION.map((b) => ({ name: b.name, v: mean(ROWS.filter((s) => s.region === b.name).map((s) => s.reading_m3)) })); const lowest = Math.min(...values.map((d) => d.v)), highest = Math.max(...values.map((d) => d.v)); const AXIS = [0, Math.floor(lowest)]; const bar = (v, base) => "#".repeat(Math.max(0, Math.round(WIDTH * (v - base) / (highest - base)))); const y1 = y([9, 8]); for (const base of AXIS) { console.log(`mean per region, if the vertical axis starts at ${base} m3`); for (const d of values) console.log(y1([d.name, d.v.toFixed(2)]) + " " + bar(d.v, base)); console.log(""); } const [a, b] = [values.find((d) => d.name === "east"), values.find((d) => d.name === "west")]; const ratio = (base) => bar(a.v, base).length / Math.max(1, bar(b.v, base).length); console.log(`the actual ratio between east and west is ${(a.v / b.v).toFixed(2)}; the bar ratio ` + `is ${ratio(0).toFixed(2)} when the axis starts at 0 and ` + `${ratio(AXIS[1]).toFixed(2)} when the axis starts at ${AXIS[1]} m3`); console.log(`the same difference looks ${(ratio(AXIS[1]) / ratio(0)).toFixed(1)} times larger and ` + `not a single reading changed`); // Recoverability of a decision: one step if it is written in a cell formula, two steps // if it sits in a separate note cell, unreadable if it is in no cell. The settings of the // chart do not live in the cells of the sheet; they live inside the chart object. const CODE = ["C1 chart type", "C2 axis baseline", "C3 plotted range", "C4 do the slices add up to a whole"]; const readBack = (t) => CODE.map((k) => { const n = k.slice(0, 2), v = [...t.cells.values()]; if (v.some((x) => x && x.kind === "formula" && x.text.includes(n))) return 1; if (v.some((x) => typeof x === "string" && x.startsWith("decision ") && x.includes(n))) return 2; return null; }); const HEAD = ["region", "mean_m3"]; const records = values.map((d) => ({ region: d.name, mean_m3: Math.round(d.v * 100) / 100 })); // The chart reports to the sheet only the range it draws; its type, its axis setting // and whether the slices form a whole are written in no cell. const charted = new Table(HEAD, records) .write("D1", formula("C3 the chart draws the range A2:B6", () => records.length)); const annotated = new Table(HEAD, records) .write("D1", formula("C3 the chart draws the range A2:B6", () => records.length)) .write("D2", `decision C2: the vertical axis was started at ${AXIS[1]} m3`); const traced = new Table(HEAD, records) .write("D1", formula("C3 the chart draws the range A2:B6", () => records.length)) .write("D2", formula("C1 the chart type is bar, because the axis field is nominal", () => "bar")) .write("D3", formula(`C2 the vertical axis starts at ${AXIS[1]} m3; had it started at zero ` + `the bar ratio would have been ${ratio(0).toFixed(2)}`, () => AXIS[1])) .write("D4", formula("C4 the measure is a mean, its slices cannot be summed, a pie cannot be drawn", () => false)); const y2 = y([36, 15, 17, 14]); console.log("\ncan the decisions of the chart be read back from the sheet"); console.log(y2(["decision", "charted sheet", "annotated sheet", "traced sheet"])); const result = [charted, annotated, traced].map(readBack); CODE.forEach((k, j) => console.log(y2([k, ...result.map((s) => (s[j] ? `${s[j]} ${s[j] === 1 ? "step" : "steps"}` : "unreadable"))]))); console.log(["charted", "annotated", "traced"].map((name, j) => `${name} ${result[j].filter(Boolean).length}/${CODE.length}`).join(", ") + " decisions can be read back"); ``` ``` mean per region, if the vertical axis starts at 0 m3 north 20.58 ####################### south 16.97 ################### east 22.80 ########################## west 13.62 ################ central 21.70 ######################### mean per region, if the vertical axis starts at 13 m3 north 20.58 #################### south 16.97 ########### east 22.80 ########################## west 13.62 ## central 21.70 ####################### the actual ratio between east and west is 1.67; the bar ratio is 1.63 when the axis starts at 0 and 13.00 when the axis starts at 13 m3 the same difference looks 8.0 times larger and not a single reading changed can the decisions of the chart be read back from the sheet decision charted sheet annotated sheet traced sheet C1 chart type unreadable unreadable 1 step C2 axis baseline unreadable 2 steps 1 step C3 plotted range 1 step 1 step 1 step C4 do the slices add up to a whole unreadable unreadable 1 step charted 1/4, annotated 2/4, traced 4/4 decisions can be read back ``` The two sets of bars draw the same five numbers. The upper axis starts at zero and the ratio of the bar lengths comes out at 1.63; the actual ratio between east and west is 1.67, and the small difference comes from the bars being rounded to whole characters. The lower axis starts at 13 m³, the whole number below the smallest value, and the ratio of the same two bars becomes **13.00**. The same difference looks **8.0 times** larger and not a single reading has changed. Raising the axis baseline is not a flaw in itself. The means of the five regions are squeezed between 13.62 and 22.80, and on an axis that starts at zero the differences between them become invisible; when the axis is cut they become visible. The price is this: **what is read on a bar chart is the length ratio**, and once the axis does not start at zero that ratio stops belonging to the data. What is read on a line chart is the slope, not the length; there the axis may be cut. The decision rule follows: if length is being read, the axis starts at zero; if it is cut, where it starts is written. #### Where the Decision Lives The last table is the lesson's second number. The **charted sheet** gives only one of the four decisions: the range it draws. The chart says which cells it reads; it does not say what it shows from those cells and how. The type, the axis baseline and whether the slices form a whole live inside the chart object, in no cell of the sheet; if a second person who opens the sheet deletes the chart and draws it again, there is no guarantee that they will get the same picture. The **annotated sheet** rises to two, and the only decision it adds is the axis baseline. There is a reason for this: the axis setting is the only decision that was deliberately changed, the other two were not chosen and stayed at their defaults. **A note kept by hand writes the changed decision and does not write the remaining one** — yet the pie drawn from means is exactly an unwritten decision. The **traced sheet** gives all four in one step, because the chart settings have been carried into the sheet: the axis baseline sits in a cell, the chart reads that cell, and the formula of the cell also writes what the ratio would be had the axis started at zero. The cost is three extra cells. The course's rule comes out the same way here too: a spreadsheet is a calculation tool, not a record-keeping tool; a record is bought with cells. #### Summary - Of ten chart suggestions six fit, four can be drawn but do not fit, and none of them produces an error. - Three misfits are in the axis scale: a line over a nominal axis gives an intermediate value, a line over an ordinal axis gives a slope without a unit, and a scatter over a field that is a label shows the registration order. - The fourth is in the measure: the slices of means cannot be summed, while a pie drawn from the totals of the same regions fits. - The bar ratio of the same five numbers comes out at 1.63 when the axis starts at zero and 13.00 when it starts at 13 m³; the same difference looks 8.0 times larger. - The chart leaves to the sheet only the range it draws: of four decisions, one can be read back from the charted sheet, two from the annotated sheet and four from the traced sheet. #### Next Step Over four lessons every operation carried a decision, and most of the decisions did not stand in the sheet. This is not a user error but the shape of the tool: the spreadsheet writes the calculation into the cell and does not write the decision. The next lesson measures the limit of this shape in three places: at how many rows the sheet gets heavy, whether a person who opens the same sheet a second time finds the same number, and how many cells a single wrong range spreads to. The course closes there and counts the decisions of eleven lessons in a single table. ### Limits of Spreadsheets Source: https://academia.sh/en/courses/analysis-with-spreadsheets/limits-of-spreadsheets Scanning the 32 decisions made across the course's eleven lessons shows that, as commonly written, only 7 can be read back from the sheet: 6 in one step, 1 in two steps, while 25 appear in no cell. Writing down all of the decisions would take 32 cells, about 3.4 parts per thousand of a sheet with 9,385 data cells. As the sheet grows, a range-reading column becomes 626 times more expensive than a fixed-cost column and quadruples when the rows double; meanwhile a one-row range error shows up in two of four summaries on a 60-row sheet and in none of them on a 1,877-row sheet. Two formula texts fill 7,508 cells: that is the work the tool does cheaply. The previous lesson counted the chart's two decisions and showed that three of its four never stay on the sheet. The same result came up in all eleven lessons: the calculation was done correctly, the number was produced, the decision vanished. This lesson reduces that disappearance to a single number and measures the tool's limit in three places: **how many decisions can be read back**, what grows as the sheet grows, and when an error becomes invisible. A limit is not a flaw. In eleven lessons the spreadsheet answered eleven questions without asking for any setup, and the final number of this lesson measures that cheapness too. What is being measured is not that the tool is bad, but **which work it does cheaply and which work it does not do at all**. - **SC23.** This lesson does not build a new dataset. The numbers of the eleven lessons are read from the source lessons; the running section keeps these numbers **as a tally** and counts over it. The sheet model is again built with `node`, and **it is written down that it is a model**. - **SC24.** The tally measures the **common version** each lesson. In the traced versions built in the same lessons, every one of the decisions is read back in one step; the measured difference is a difference in how the sheet is written, not in what the tool is capable of. - **SC25.** Size is **not measured by time**. What is counted is the number of cells evaluated in a single recalculation; this number is independent of hardware and follows from the shape of the sheet. - **SC26.** The threshold for a change to count as **visible** is 1%, because the summaries of this course are reported with two significant digits. #### How Many Decisions Can Be Read Back ```js // wrapup.mjs — tally of the decisions made across the course's eleven lessons. Each row holds // the decisions of one lesson and the number of steps in which each decision is read back // from the sheet AS COMMONLY WRITTEN: 1 step written in a formula or a header row, 2 steps in // a separate note cell, null in no cell at all. The numbers were read from the source lessons. export const LESSONS = [ ["basics/01 worksheet structure", ["reference type", "unit fee source"], [2, null]], ["basics/02 basic functions", ["choice of counting operation", "denominator of the mean", "skipping of text numbers"], [1, null, null]], ["basics/03 logical functions", ["boundary comparison", "order of conditions", "tier thresholds"], [null, null, null]], ["basics/04 text functions", ["trim level", "language of case conversion", "place of correction"], [1, 1, null]], ["basics/05 date functions", ["day-counting rule", "month-end rule", "type of date"], [null, null, null]], ["basics/06 lookup functions", ["type of column number", "match type"], [null, null]], ["summary/01 removing duplicate records", ["criterion columns", "text trimming", "row kept", "deleted count"], [null, null, null, null]], ["summary/02 dealing with missing data", ["which cell was blank", "imputation method", "meaning of zero", "rows entering the calculation"], [null, null, null, null]], ["summary/03 pivot tables", ["breakdown field", "measure function", "place of filter", "level of calculated field"], [1, 1, null, null]], ["summary/04 basic charts", ["chart type", "axis baseline", "plotted range", "do the slices add up to a whole"], [null, null, 1, null]], ]; // The course's sheet: the rows left after deduplication and the columns they carry. export const ROW_COUNT = 1877, COLUMN_COUNT = 5; export const y = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j]) : String(v).padEnd(w[0]))).join(""); if (import.meta.url === `file://${process.argv[1]}`) { const y1 = y([38, 11, 8, 9, 12]); console.log(y1(["lesson", "decisions", "1 step", "2 steps", "unreadable"])); let total = 0, one = 0, two = 0; for (const [name, decisions, steps] of LESSONS) { const a = steps.filter((x) => x === 1).length, b = steps.filter((x) => x === 2).length; total += decisions.length; one += a; two += b; console.log(y1([name, decisions.length, a, b, decisions.length - a - b])); } const readable = one + two; console.log(y1(["TOTAL", total, one, two, total - readable])); console.log(`\nof the first ten of eleven lessons, ${total} decisions were made; as commonly written, ${readable} of them ` + `can be read back from the sheet, ${total - readable} are in no cell`); console.log(`of those read back, ${one} are read in one step and ${two} in two steps; ` + `the share is ${(100 * readable / total).toFixed(0)}%`); // The cost of writing a decision down: one cell per decision. const cells = ROW_COUNT * COLUMN_COUNT; console.log(`writing down all ${total} decisions takes ${total} cells; the sheet's ${ROW_COUNT} rows and ` + `${COLUMN_COUNT} columns hold ${cells} data cells, so the cost of the record is ` + `${(1000 * total / cells).toFixed(1)} parts per thousand of the sheet`); // Where the decision is embedded, by topic. const topic = (p) => LESSONS.filter(([a]) => a.startsWith(p)) .reduce((s, [, k, d]) => [s[0] + k.length, s[1] + d.filter(Boolean).length], [0, 0]); const [bt, st] = [topic("basics"), topic("summary")]; console.log(`in the six lessons working with cell functions ${bt[1]}/${bt[0]} decisions can be read back, ` + `in the four lessons looking at the whole sheet ${st[1]}/${st[0]}`); } ``` ``` lesson decisions 1 step 2 steps unreadable basics/01 worksheet structure 2 0 1 1 basics/02 basic functions 3 1 0 2 basics/03 logical functions 3 0 0 3 basics/04 text functions 3 2 0 1 basics/05 date functions 3 0 0 3 basics/06 lookup functions 2 0 0 2 summary/01 removing duplicate records 4 0 0 4 summary/02 dealing with missing data 4 0 0 4 summary/03 pivot tables 4 2 0 2 summary/04 basic charts 4 1 0 3 TOTAL 32 6 1 25 of the first ten of eleven lessons, 32 decisions were made; as commonly written, 7 of them can be read back from the sheet, 25 are in no cell of those read back, 6 are read in one step and 1 in two steps; the share is 22% writing down all 32 decisions takes 32 cells; the sheet's 1877 rows and 5 columns hold 9385 data cells, so the cost of the record is 3.4 parts per thousand of the sheet in the six lessons working with cell functions 4/16 decisions can be read back, in the four lessons looking at the whole sheet 3/16 ``` Seven. Of the thirty-two decisions, seven can be read back from the sheet and twenty-five stand in no cell; the share is **22%**. Six of the seven that can be read back are read in one step, and there is a reason for that: in those six what carries the decision is not a separate note but the **own name** of the function or the header. A trim function says what it trims, a case conversion says which language's rule it applies, the header of a pivot table writes the breakdown field. **A decision that carries its own name survives.** What loses the remaining twenty-five is a common form: the decision has turned into a number and gone inside a formula. A column number becomes a constant, a boundary comparison an operator, a match type a zero, an imputed value a decimal number. The number does the calculation correctly and does not say which question it answers. The bottom row of the tally breaks an expectation. In the six lessons working with cell functions, 4 of 16 decisions can be read back; in the four lessons looking at the whole sheet, 3 of 16. Moving to the whole table does not fix the situation: it leaves **none** of the deduplication and imputation decisions behind, because both change the sheet and erase themselves. The last row gives the cost. Writing down all thirty-two decisions takes 32 cells. The sheet itself holds 9,385 data cells with 1,877 rows and 5 columns, so the cost of the record is about **3.4 parts per thousand of the sheet**. The reason the decisions go unwritten is not cost. #### What Grows as the Sheet Grows ```js // size.mjs — what grows as the sheet grows. Time is not measured; what is counted is the number of cells // evaluated in a single recalculation and whether a one-row error is visible in the // summary. IT IS A MODEL: a cell lives in a map. import { y } from "./wrapup.mjs"; const LETTER = (j) => String.fromCharCode(65 + j); const VALUE = (i) => 10 + ((i * 7) % 40) * 0.5; // Two kinds of formula. A fixed-cost formula reads only its own row and one parameter // cell. A range-reading formula scans the whole column in every row -- // conditional sums and lookup functions behave this way. const build = (n, ranged) => { const cells = new Map(); let counter = 0; cells.set("P1", 8.4); for (let i = 0; i < n; i++) cells.set(`A${i + 2}`, VALUE(i)); const read = (name) => { counter++; const v = cells.get(name); return typeof v === "function" ? v() : v; }; for (let i = 0; i < n; i++) cells.set(`B${i + 2}`, ranged ? () => { let t = 0; for (let j = 0; j < n; j++) { const v = read(`A${j + 2}`); if (v > 20) t += v; } return t; } : () => read(`A${i + 2}`) * read("P1")); return () => { counter = 0; for (let i = 0; i < n; i++) read(`B${i + 2}`); return counter; }; }; const y1 = y([10, 21, 21, 12]); console.log("cells evaluated in a single recalculation"); console.log(y1(["rows", "fixed-cost column", "range-reading column", "ratio"])); let previous = null; for (const n of [1877, 3754, 7508]) { const [a, b] = [build(n, false)(), build(n, true)()]; console.log(y1([n, a, b, (b / a).toFixed(0)])); if (previous) console.log(` when the rows doubled, the fixed-cost column grew ` + `${(a / previous[0]).toFixed(1)}-fold and the range-reading column grew ` + `${(b / previous[1]).toFixed(1)}-fold`); previous = [a, b]; } console.log(`each of the two columns was written as a single formula text and copied into 7508 cells; ` + `this is what the sheet does cheaply`); // A one-row error: the range leaves out the last row. For a change to count as // visible the threshold is 1%, because summaries are read with two significant digits. const THRESHOLD = 1.0; const SUMMARIES = [["total", (a) => a.reduce((x, z) => x + z, 0)], ["mean", (a) => a.reduce((x, z) => x + z, 0) / a.length], ["largest", (a) => Math.max(...a)], ["count", (a) => a.length]]; const y2 = y([10, 12, 12, 12, 12, 14]); console.log(`\nby how many percent each summary changes if the range is taken one row short (threshold ${THRESHOLD})`); console.log(y2(["rows", ...SUMMARIES.map((o) => o[0]), "visible"])); for (const n of [60, 1877]) { const full = Array.from({ length: n }, (_, i) => VALUE(i)), short = full.slice(0, n - 1); const diff = SUMMARIES.map(([, f]) => 100 * Math.abs(f(full) - f(short)) / f(full)); console.log(y2([n, ...diff.map((v) => v.toFixed(2)), `${diff.filter((v) => v > THRESHOLD).length}/${SUMMARIES.length}`])); } console.log(`the same error is visible in two summaries on the 60-row sheet and in none on the 1877-row sheet: ` + `as the sheet grows, it is not the error that shrinks but its visibility`); ``` ``` cells evaluated in a single recalculation rows fixed-cost column range-reading column ratio 1877 5631 3525006 626 3754 11262 14096270 1252 when the rows doubled, the fixed-cost column grew 2.0-fold and the range-reading column grew 4.0-fold 7508 22524 56377572 2503 when the rows doubled, the fixed-cost column grew 2.0-fold and the range-reading column grew 4.0-fold each of the two columns was written as a single formula text and copied into 7508 cells; this is what the sheet does cheaply by how many percent each summary changes if the range is taken one row short (threshold 1) rows total mean largest count visible 60 1.40 0.27 0.00 1.67 2/4 1877 0.04 0.01 0.00 0.05 0/4 the same error is visible in two summaries on the 60-row sheet and in none on the 1877-row sheet: as the sheet grows, it is not the error that shrinks but its visibility ``` The first table puts the two kinds of formula side by side. A fixed-cost column reads only its own row and one parameter cell in every row; on 1,877 rows a single recalculation calls for 5,631 cell evaluations, and when the rows double the number doubles. A range-reading column — conditional sum, vertical lookup, and match-and-index are of this kind — scans the whole column in every row; on the same sheet it calls for 3,525,006 evaluations, that is **626 times** as many, and when the rows double the number **quadruples**. The limit is here and it has nothing to do with hardware: when the sheet grows tenfold, the fixed-cost column becomes ten times heavier and the range-reading column a hundred times heavier. The last row of the same table shows the tool's other face: each of the two columns was written as **a single formula text** and copied into 7,508 cells. Writing a question once and spreading it over thousands of rows is the work the spreadsheet does cheaply. The second table measures the second face of size. If a range leaves out the last row, on a 60-row sheet the total moves 1.40% and the count 1.67%, and **two** of the four summaries change visibly. The same error on a 1,877-row sheet moves the total 0.04% and is visible in **none** of the four summaries. The error did not shrink, the sheet grew. As the share of each row in the summary falls, checking by eye becomes useless, yet it is the only check left on a sheet where the decisions are not written down. #### Summary - Of the 32 decisions made across the course's eleven lessons, 7 can be read back as commonly written and 25 are in no cell; the share is 22%. - Six of the seven that can be read back are in one step, and in all of them what carries the decision is the name of the function or the header itself; the twenty-five that vanished have turned into a constant or an operator. - Writing down all of the decisions takes 32 cells, 3.4 parts per thousand of the sheet's 9,385 data cells; the reason they go unwritten is not cost. - A range-reading column is 626 times more expensive than a fixed-cost column at 1,877 rows and quadruples when the rows double; the fixed-cost column doubles. - The same one-row range error is visible in two of four summaries on a 60-row sheet and in none on a 1,877-row sheet. - The same sheet fills 7,508 cells with two formula texts; that is the work the tool does cheaply. #### Course Wrap-Up | Lesson | Number | Decision that changes | Difference | Can the decision be read back | |---|---|---|---|---| | Worksheet Structure | 13,427.16 total | reference type | 3,354.98, 59 cells | 1/2, two steps | | Basic Functions | 708.5 m³ total | counting of text numbers | 967.2 m³, 26.7% | 1/3, one step | | Logical Functions | 26,219.52 amount | boundary comparison | 26,698.32, 35.5% | 0/3, unreadable | | Text Functions | 8 matching records | trimming and case conversion | 48 records, 1,204.8 m³ | 2/3, one step | | Date Functions | 6.33 days mean | day-counting rule | 5.20 and 7.33 days | 0/3, unreadable | | Lookup Functions | 1,204.8 m³ total | embedded column number | 1,084.6 m³, 48 records | 0/2, unreadable | | Removing Duplicate Records | 1,877 rows remain | deduplication key | 1,400 rows, 12,121.0 m³ | 0/4, unreadable | | Dealing with Missing Data | 19.23 m³ mean | whether the blank cell is excluded | 18.04 and 20.54 m³ | 0/4, unreadable | | Pivot Tables | north 10,905.8 m³ | measure of the breakdown | east and central first | 2/4, in the header | | Basic Charts | 1.63 bar ratio | axis baseline | 13.00 ratio, 8.0 times | 1/4, range | | Limits of Spreadsheets | 7 decisions readable | where the decision is written | 25 decisions lost | 7/32, 22% | The rule of the course comes out of this table: **a spreadsheet is a calculation tool, not a record-keeping tool; a decision embedded in a cell is a decision that was not written down.** In eleven lessons no calculation was done wrongly and no operation raised an error; what vanished was not the calculation but which question the calculation answered. What makes two people who open the same table find the same number is not the table itself but the definition kept **outside** the table. The work the tool does cheaply can also be written with the same measure: a single formula text spreads over 7,508 cells, while writing down all of the decisions takes about 3.4 parts per thousand of the sheet. Across these eleven lessons the decisions were embedded in cells and most could not be read back. But a more fundamental question was never asked: **where** did this data come from before it reached the table, and what happened to it on the way. All 1,877 rows passed through a field form, a reading device, or the export of another system; at every crossing a field was renamed, a date was reformatted, a code was trimmed. The next course, **Data Collection and Preparation**, opens up what comes before the place where the table starts and counts how many hands the data passed through before it entered the table.