// The formula function catalog, wave 52 (W52-T05, contract C3, instruction 5). // // This file owns ONE job: the local function data the suggestion dropdown, the ScreenTip // (`formulaAssist.ts`) and the Formulas ribbon's offline fallback draw from. It is PURE data. // Category names match the server catalogue so fallback never changes the picker's shape. No // React, no DOM, nothing computed at runtime that a later ticket cannot re-derive from a formula's // own text. // // ⛔ SIGNATURE SPACING IS THE OWNER'S OWN EXAMPLE, NOT MICROSOFT'S. Microsoft's SUM reference page // prints `SUM(number1,[number2],...)` with no space after the comma. The owner's instruction 5 // spelled it "SUM(number1, [number 2], ...)" -- WITH a space after every comma. `ST-2/ST-3` in // `AutoWiki/waves/wave52/proto/excel-behaviour-spec.md` says match the owner's spacing exactly. // `buildSignature` below is the ONLY place that assembles a signature string, so every entry gets // the same spacing by construction rather than by sixty hand-typed strings agreeing by luck. // // ⛔ NO EM DASH (U+2014) OR EN DASH (U+2013) IN ANY STRING HERE. `summary` reaches a screen the // moment T06 renders the dropdown row (FA-15). Standing rule 2. `verify_formula_engine.py`'s new // leg asserts this the same way the wave-50 catalogue leg already asserts it for the grid engine. /** One argument in a function's signature. `optional` renders it in `[brackets]` (ST-3). */ export interface FunctionArg { name: string; optional?: boolean; } /** * `SUM(number1, [number2], ...)`. * * Bare = required. `[bracketed]` = optional (display only -- the brackets are never typed or * committed). A trailing `, ...` means the last optional argument repeats (ST-3), up to whatever * cap the function itself enforces; this catalog does not encode that cap, only the shape. */ function buildSignature(name: string, args: FunctionArg[], repeats = false): string { const parts = args.map((a) => (a.optional ? `[${a.name}]` : a.name)); let body = parts.join(", "); if (repeats) body += body ? ", ..." : "..."; return `${name}(${body})`; } export interface FunctionSpec { name: string; category: string; signature: string; summary: string; argNames: string[]; } /** One row per catalog entry: name, its arg list, whether the last optional argument repeats. */ interface FunctionDef { name: string; category: string; args: FunctionArg[]; repeats?: boolean; summary: string; } // ⚠ ORDERING HERE IS DELIBERATELY NOT ALPHABETICAL FOR EVERY PREFIX GROUP (the M group in // particular). `suggest()` in formulaAssist.ts is the thing that must sort these into A-Z order // (FA-8); a catalog that is already alphabetical would let a broken sort pass by accident. const FUNCTION_DEFS: FunctionDef[] = [ { name: "SUMPRODUCT", category: "Math", args: [{ name: "array1" }, { name: "array2", optional: true }], repeats: true, summary: "Adds the products of corresponding values in arrays.", }, { name: "ISTEXT", category: "Information", args: [{ name: "value" }], summary: "Returns true when a value is text.", }, { name: "WEEKNUM", category: "Date", args: [{ name: "date" }, { name: "return_type", optional: true }], summary: "Returns the week number with weeks beginning on Sunday or Monday.", }, { name: "CONCATENATE", category: "Text", args: [{ name: "text1" }, { name: "text2", optional: true }], repeats: true, summary: "Joins text values together.", }, { name: "YEAR", category: "Date", args: [{ name: "date" }], summary: "Returns the year of a date.", }, { name: "MONTH", category: "Date", args: [{ name: "date" }], summary: "Returns the month number of a date.", }, { name: "FILTER", category: "Lookup", args: [{ name: "array" }, { name: "include" }, { name: "if_empty", optional: true }], summary: "Returns the rows or columns that meet a condition.", }, { name: "SORT", category: "Lookup", args: [{ name: "array" }, { name: "sort_index", optional: true }, { name: "sort_order", optional: true }, { name: "by_col", optional: true }], summary: "Returns an array sorted by a row or column.", }, { name: "OFFSET", category: "Lookup", args: [{ name: "reference" }, { name: "rows" }, { name: "cols" }, { name: "height", optional: true }, { name: "width", optional: true }], summary: "Returns a reference shifted by a number of rows and columns.", }, { name: "INDIRECT", category: "Lookup", args: [{ name: "ref_text" }, { name: "a1", optional: true }], summary: "Returns the reference named by a text value.", }, { name: "RAND", category: "Math", args: [], summary: "Returns a random number between zero and one.", }, { name: "SUM", category: "Math", args: [{ name: "number1" }, { name: "number2", optional: true }], repeats: true, summary: "Adds all the numbers in a range of cells.", }, { name: "SUMIF", category: "Math", args: [ { name: "range" }, { name: "criteria" }, { name: "sum_range", optional: true }, ], summary: "Adds the cells specified by a given condition.", }, { name: "MID", category: "Text", args: [{ name: "text" }, { name: "start_num" }, { name: "num_chars" }], summary: "Returns characters from the middle of a text string.", }, { name: "MATCH", category: "Lookup", args: [ { name: "lookup_value" }, { name: "lookup_array" }, { name: "match_type", optional: true }, ], summary: "Returns the relative position of an item in a range.", }, { name: "MIN", category: "Statistical", args: [{ name: "number1" }, { name: "number2", optional: true }], repeats: true, summary: "Returns the smallest number in a set of values.", }, { name: "MAX", category: "Statistical", args: [{ name: "number1" }, { name: "number2", optional: true }], repeats: true, summary: "Returns the largest number in a set of values.", }, { name: "AVERAGE", category: "Statistical", args: [{ name: "number1" }, { name: "number2", optional: true }], repeats: true, summary: "Returns the average of a set of numbers.", }, { name: "AND", category: "Logical", args: [{ name: "logical1" }, { name: "logical2", optional: true }], repeats: true, summary: "Returns TRUE when every argument is true.", }, { name: "OR", category: "Logical", args: [{ name: "logical1" }, { name: "logical2", optional: true }], repeats: true, summary: "Returns TRUE when any argument is true.", }, { name: "NOT", category: "Logical", args: [{ name: "logical" }], summary: "Reverses the logic of its one argument.", }, { name: "COUNT", category: "Statistical", args: [{ name: "value1" }, { name: "value2", optional: true }], repeats: true, summary: "Counts the numbers in a list of values.", }, { name: "COUNTA", category: "Statistical", args: [{ name: "value1" }, { name: "value2", optional: true }], repeats: true, summary: "Counts the non blank values in a list.", }, { name: "COUNTIF", category: "Statistical", args: [{ name: "range" }, { name: "criteria" }], summary: "Counts the cells that meet a given condition.", }, { name: "IF", category: "Logical", args: [ { name: "logical_test" }, { name: "value_if_true" }, { name: "value_if_false", optional: true }, ], summary: "Returns one value if a condition is true and another if false.", }, { name: "IFERROR", category: "Logical", args: [{ name: "value" }, { name: "value_if_error" }], summary: "Returns a fallback value when the first argument is an error.", }, { name: "XLOOKUP", category: "Lookup", args: [ { name: "lookup_value" }, { name: "lookup_array" }, { name: "return_array" }, { name: "if_not_found", optional: true }, { name: "match_mode", optional: true }, ], summary: "Finds a value in one range and returns the matching value from another, with an answer of your own when nothing matches.", }, { name: "VLOOKUP", category: "Lookup", args: [ { name: "lookup_value" }, { name: "table_array" }, { name: "col_index_num" }, { name: "range_lookup", optional: true }, ], summary: "Looks up a value in the first column and returns a value in the same row.", }, { name: "INDEX", category: "Lookup", args: [ { name: "array" }, { name: "row_num" }, { name: "column_num", optional: true }, ], summary: "Returns a value at a given position in a range.", }, { name: "CHOOSE", category: "Lookup", args: [{ name: "index_num" }, { name: "value1" }, { name: "value2", optional: true }], repeats: true, summary: "Returns the value at the position you name in a list.", }, { name: "CONCATENATE", category: "Text", args: [{ name: "text1" }, { name: "text2", optional: true }], repeats: true, summary: "Joins several text values into one text value.", }, // W57-T07: offer only server-backed text functions, with hints built by buildSignature. { name: "TEXTJOIN", category: "Text", args: [ { name: "delimiter" }, { name: "ignore_empty" }, { name: "text1" }, { name: "text2", optional: true }, ], repeats: true, summary: "Joins text values with a delimiter between them, optionally skipping the empty ones.", }, { name: "SUBSTITUTE", category: "Text", args: [ { name: "text" }, { name: "old_text" }, { name: "new_text" }, { name: "instance_num", optional: true }, ], summary: "Replaces old text with new text in a text value.", }, { name: "ROUND", category: "Math", args: [{ name: "number" }, { name: "num_digits" }], summary: "Rounds a number to a given number of digits.", }, { name: "ABS", category: "Math", args: [{ name: "number" }], summary: "Returns the absolute value of a number.", }, { name: "TRIM", category: "Text", args: [{ name: "text" }], summary: "Removes extra spaces from a text value.", }, { name: "LEN", category: "Text", args: [{ name: "text" }], summary: "Returns the number of characters in a text value.", }, { name: "LEFT", category: "Text", args: [{ name: "text" }, { name: "num_chars", optional: true }], summary: "Returns the leftmost characters from a text value.", }, { name: "RIGHT", category: "Text", args: [{ name: "text" }, { name: "num_chars", optional: true }], summary: "Returns the rightmost characters from a text value.", }, // W57-T07: these date functions return ISO dates or counts on both sides of Refresh. { name: "EOMONTH", category: "Date", args: [{ name: "date" }, { name: "months" }], summary: "Returns the last day of the month a number of months away from a date.", }, { name: "WORKDAY", category: "Date", args: [{ name: "start_date" }, { name: "days" }, { name: "holidays", optional: true }], summary: "Returns the date a number of working days away from a date, skipping weekends and any holidays you name.", }, { name: "NETWORKDAYS", category: "Date", args: [{ name: "start_date" }, { name: "end_date" }, { name: "holidays", optional: true }], summary: "Counts the working days between two dates, skipping weekends and any holidays you name.", }, { name: "TODAY", category: "Date", args: [], summary: "Returns the current date.", }, { name: "NOW", category: "Date", args: [], summary: "Returns the current date and time.", }, ]; export const FUNCTIONS: FunctionSpec[] = FUNCTION_DEFS.map((def) => ({ name: def.name, category: def.category, signature: buildSignature(def.name, def.args, def.repeats), summary: def.summary, argNames: def.args.map((a) => a.name), })); /** True when `signature`'s last optional argument repeats (a trailing `, ...` or `...`). ST-3/ST-6. */ export function signatureRepeats(signature: string): boolean { return signature.trimEnd().endsWith("...)"); } /** Look up one catalog entry by name, case insensitive. Returns undefined when unknown. */ export function findFunction(name: string): FunctionSpec | undefined { const upper = String(name || "").toUpperCase(); return FUNCTIONS.find((f) => f.name === upper); }