--- title: 'Worksheet Structure' source: 'https://academia.sh/en/courses/analysis-with-spreadsheets/worksheet-structure' course: 'Analysis with Spreadsheets' language: en updated: '2026-10-07T04:31:09+00:00' license: 'CC BY-SA 4.0' objectives: - 'Distinguishes cell, range, and reference types on a worksheet model' - 'Writes the same calculation with three reference decisions and prints the totals and the wrong-cell counts side by side' - 'Measures how many cells follow when one parameter changes, as whether the decision can be read back from the sheet' --- # 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.