Limits of Spreadsheets

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

// 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

// 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

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.