Basic Functions

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.

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

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

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