Spreadsheet | Formulas
The Formulas tab: Function Library (Insert Function, AutoSum, one menu per category), Defined Names (Name Manager, Define Name, Use in Formula) and Calculation (Calculate Now, Show Formulas).
Functions
Section titled “Functions”Around 140 functions across math, statistics, logic, text, lookup, date, information and finance, including:
| Area | Examples |
|---|---|
| Lookup | XLOOKUP, XMATCH, plus the classic lookups |
| Aggregation | SUBTOTAL, AGGREGATE |
| Financial | PMT, PV, FV, NPV, IRR, RATE, NPER, IPMT, PPMT, SLN… |
An unknown function evaluates to #NAME? rather than throwing. Recalculation is dependency-ordered and
incremental, and a circular reference is detected and reported rather than recursing.
Autocomplete and Insert Function
Section titled “Autocomplete and Insert Function”Typing =SU in a cell or the formula bar drops a list of matching functions and defined names; inside a
call, a tip shows its signature. It is FormulaAssist, shared by both hosts.
Insert Function (Formulas ▸ Function Library, Shift+F3) opens a dialog for picking a function; the Name Manager is Ctrl+F3. AutoSum is on both Home and Formulas, as in Excel.
Defined names
Section titled “Defined names”Define Name and the Name Manager create, edit and delete workbook names; the name box above the grid also defines one when you type a new name into it. Use in Formula inserts one.
controller.DefineName("Sales", "Data!$B$2:$B$13"); // =SUM(Sales) works and recalculatesRenaming a sheet rewrites every formula and defined name that pointed at it, and inserting or deleting rows and columns rewrites references everywhere, names included.
Show Formulas
Section titled “Show Formulas”Formulas ▸ Show Formulas (or View ▸ Show, or Ctrl+`) shows each formula in its cell instead of the result. Calculate Now forces a full recalculation.