--- title: 'Basic Functions' source: 'https://academia.sh/en/courses/analysis-with-spreadsheets/basic-functions' course: 'Analysis with Spreadsheets' language: en updated: '2026-10-07T04:31:09+00:00' license: 'CC BY-SA 4.0' objectives: - 'Distinguishes a blank cell, a zero, and a number stored as text in a range and shows which function counts each' - 'Computes the total and the mean of the same range with different counting decisions and prints the numbers side by side' - 'Counts whether a threshold and a denominator can be read back from the formula text' --- # 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.