Basic Charts

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

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

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