@searsuite/sear-domain-formula-engine-ts
Parse and evaluate formulas
FormulaParser turns formula text into an expression tree. FormulaEvaluator evaluates that tree against cell
values and functions supplied by the runtime. DependencyGraph records which cells must be recalculated when an
input changes. These are the TypeScript implementations of the contracts in
@searsuite/sear-domain-formula-engine.
Most Sheet hosts use these through the formula plugin and editor assembly. When replacing the implementation, depend on the contract interfaces rather than importing these classes into a feature plugin.
- classFormulaParser
-
class FormulaParser extends IFormulaParserParses formula source and rewrites structural references.
constructor FormulaParser(): FormulaParserproperty readonly symbol: typeof symbolstaticDI token for resolving FormulaParser.
method extractDependencies(ast: FormulaAST): Dependency[]Extracts all dependencies from an AST.
Traverses the AST and collects all cell/range nodes. The returned Dependency list stays range-level and is not expanded per cell.
method parse(formula: string): FormulaASTParses a formula string into an AST.
ReturnsParsed AST.
formulaFormula string including the leading '=', such as "=A1+B1".
method shiftReferences(ast: FormulaAST, changes: StructureChange[]): FormulaASTShifts references by rewriting row/column references for structural changes.
Uses immutable updates: creates a new AST and never mutates the original. If no reference needs shifting, returns the original AST by reference.
- classFormulaEvaluator
-
class FormulaEvaluator extends IFormulaEvaluatorEvaluates parsed formulas against an explicit host-provided context.
constructor FormulaEvaluator(registry: IFunctionRegistry, aggCache: IAggregationCache): FormulaEvaluatorproperty readonly symbol: typeof symbolstaticDI token for resolving FormulaEvaluator.
method evaluate(ast: FormulaAST, context: EvalContext): FormulaValueEvaluates one AST.
Recursively traverses each AST node and executes the matching operation.
method evaluateBatch(asts: { ast: FormulaAST; key: number }[], context: EvalContext): EvalResult[]Batch evaluation.
The TS implementation evaluates one AST at a time. A WASM implementation can process all ASTs on the Rust side in one pass.
method registerFunction(fn: FormulaFunction): voidRegisters a custom function.
- classDependencyGraph
-
class DependencyGraph extends IDependencyGraphTracks formula dependencies, dirty propagation, evaluation readiness, and cycles.
constructor DependencyGraph(): DependencyGraphproperty readonly symbol: typeof symbolstaticDI token for resolving DependencyGraph.
memberpendingCount pendingCount: unknownNumber of dirty formulas still waiting for evaluation.
membersize size: unknownTotal number of formula cells currently in the graph.
method clear(): voidClears all data.
method enforceCommandBoundary(isCommandExecuting: (): boolean): voidBinds mutations to the owning editor command dispatch after initialization.
method getDependencies(formulaKey: number): readonly Dependency[]Gets the range-level dependencies owned by one formula cell.
Returns defensive copies so callers can inspect dependency edges without mutating the graph's internal forward-dependency store.
method getDependents(key: number): number[]Gets every formula that depends on one cell.
Merges results from three lookup paths:
- cellRevDeps (cell + small-range expansion) — O(1)
- columnRanges (column-grouped intervals) — O(log N + K)
- overflowRanges (very wide ranges) — O(M)
method getFormulaKeys(): readonly number[]Gets a defensive snapshot of formula cells currently registered in the graph.
method getLastCycleKeys(): number[]Returns the last cycle keys.
method getNextReadyBatch(viewport: Set<number>, maxCount: number): number[]Gets currently dirty formulas that can be evaluated immediately.
Two-pass scan: viewport first, then off-screen formulas. Viewport priority ensures visible formulas update first.
method isDirty(key: number): booleanReports whether one formula cell is currently dirty.
method markClean(key: number): voidMarks one formula clean.
Unlocks downstream dependencies by decrementing dirtyDepCount for every dirty formula that depends on key.
method markDirty(changedKeys: number[]): voidMarks all affected formulas dirty.
Propagates by BFS from changedKeys along reverse-dependency edges. Only marks; does not sort or calculate.
method onStructureChanged(changes: StructureChange[]): voidNotifies the graph about structural changes.
For row/column insertions and deletions, updates range/cell coordinates in every formula's deps and rebuilds reverse indexes.
method removeCell(formulaKey: number): voidRemoves one formula cell.
method updateCell(formulaKey: number, depsList: Dependency[]): voidRegisters or updates dependencies for one formula cell.
- classFormulaExecutionBudget
-
class FormulaExecutionBudget extends FormulaEvaluationBudgetMutable, deterministic guard against unbounded formula work.
- classFormulaFormatter
-
Formats the shared FormulaAST back to formula source text.
- classFormulaLexicalScope
-
class FormulaLexicalScope extends FormulaEvaluationScopeCase-insensitive lexical scope for LET bindings.
- interfaceParsedCellRef
-
Parsed A1 cell reference.
- interfaceWorkbookFormulaFormatOptions
-
Workbook name resolution used while formatting formula source.
- functioncellKey
-
cellKey(row: number, col: number): numberEncodes zero-based coordinates as one numeric cell key.
- functionfromCellKey
-
fromCellKey(key: number): [number, number]Decodes a numeric cell key to zero-based coordinates.
- functionparseCellRef
-
parseCellRef(ref: string): ParsedCellRefParses an A1-notation cell reference.
- functiontoCellRef
-
toCellRef(row: number, col: number, absRow?: boolean, absCol?: boolean): stringConverts zero-based coordinates to A1 notation.
Register functions
The function entries below are FormulaFunction values, grouped by purpose: lookup, date and time, math,
finance, text, and other families. FunctionRegistry registers the built-ins and resolves names without case
sensitivity. For example, a formula containing XLOOKUP is evaluated through the registry's XLOOKUP entry;
application code does not call that entry as a normal JavaScript function.
Start with the function's description and example, then inspect FormulaFunction in the contracts package when
you need to define or register a custom function.
- classFunctionRegistry
-
class FunctionRegistry extends IFunctionRegistryconstructor FunctionRegistry(options?: FunctionRegistryOptions): FunctionRegistryproperty readonly symbol: typeof symbolstaticDI token for resolving FunctionRegistry.
method enforceCommandBoundary(isCommandExecuting: (): boolean, dispatchRemoval: (id: number): void): voidRequires post-initialization writes to occur during the owning editor's command dispatch.
method get(name: string): FormulaFunction | undefinedmethod has(name: string): booleanReturns whether the requested entry exists.
method list(): string[]Returns all registered entries.
method register(fn: FormulaFunction): (): voidRegisters a formula function under its canonical and compatible names.
method registerMany(functions: readonly FormulaFunction[]): (): voidRegisters multiple functions while publishing one combined invalidation.
method subscribe(listener: (functionNames: readonly string[]): void): (): voidSubscribes to registrations and removals that can change formula results.
method unregisterRegistration(id: number): voidRemoves one tokenized registration; disposal may dispatch an owner command.
- classFormulaExternalServiceRegistry
-
class FormulaExternalServiceRegistry extends IFormulaExternalServiceProviderTokenized editor-local external-service registry with isolated listener failures.
constructor FormulaExternalServiceRegistry(warn?: (message: string, error: unknown): void): FormulaExternalServiceRegistrymethod destroy(ownerToken?: object): voidmethod enforceCommandBoundary(isCommandExecuting: (): boolean, dispatchRemoval: (id: number): void): voidmethod enforceTeardownBoundary(ownerToken: object): voidReserves cleanup of an assembled registry for its owning editor.
method get<T>(definition: FormulaExternalServiceDefinition<T>): T | undefinedmethod invalidate<T>(definition: FormulaExternalServiceDefinition<T>): voidmethod register<T>(definition: FormulaExternalServiceDefinition<T>, service: T): (): voidmethod subscribeInvalidation(listener: (serviceIds: readonly string[]): void): (): voidmethod unregisterRegistration(id: number): void - classWebFormulaAddInService
-
class WebFormulaAddInService extends FormulaAddInServiceWeb host adapter: browsers cannot resolve or invoke native add-in procedures.
- interfaceFunctionRegistryOptions
-
FunctionRegistry public API.
property readonly excludedBuiltinNames?: readonly string[]Built-ins omitted from the initial catalog; hosts may register them later.
property readonly includeGoogleFunctions?: booleanGoogle-specific functions require an explicit host contribution when disabled.
Service caches
Caches reuse external formula results while preserving the engine’s status and invalidation rules.
- classAggregationCache
-
class AggregationCache extends IAggregationCacheCaches incremental aggregate results for large formula ranges.
constructor AggregationCache(): AggregationCacheproperty readonly symbol: typeof symbolstaticmethod clear(): voidClears all caches.
method enforceCommandBoundary(isCommandExecuting: (): boolean): voidBinds cache writes to the owning editor command dispatch after initialization.
method getCount(startRow: number, startCol: number, endRow: number, endCol: number): numberGets the COUNT for a range, meaning the number of numeric cells.
Uses the same cache and incremental strategy as getSum.
method getSum(startRow: number, startCol: number, endRow: number, endCol: number): numberGets the SUM for a range.
Cache hit returns directly in O(1). Cache miss requires a full O(N) scan on the first call.
Called by FormulaEvaluator while evaluating SUM, not exposed directly to users.
startRowStart row (0-based).
startColStart column (0-based).
endRowEnd row (0-based).
endColEnd column (0-based).
method onCellChanged(row: number, col: number, oldVal: FormulaValue, newVal: FormulaValue): voidNotifies the cache that one cell value changed.
Finds every cached range covering the cell and updates incrementally:
- newSum = oldSum - oldVal + newVal
- FormulaError oldVal/newVal values are treated as non-numeric and do not affect sum.
rowChanged row (0-based).
colChanged column (0-based).
oldValPrevious value.
newValNew value.
method onStructureChanged(): voidNotifies the cache that rows or columns were inserted or deleted.
Structural changes invalidate all range caches because range coordinates have changed, so the cache must be cleared.
method setCount(startRow: number, startCol: number, endRow: number, endCol: number, count: number): voidStores a successfully calculated COUNT for a rectangular range.
method setSum(startRow: number, startCol: number, endRow: number, endCol: number, sum: number): voidStores a successfully calculated SUM for a rectangular range.
- classAsyncFormulaServiceCache
-
class AsyncFormulaServiceCache<TKey, TValue>Host-driven async cache. read() never starts work or returns a Promise.
constructor AsyncFormulaServiceCache<TKey, TValue>(loader: (key: TKey): Promise<TValue>, warn?: (message: string, error: unknown): void): AsyncFormulaServiceCache<TKey, TValue>method clear(): voidDrops values and ignores results from requests started before this boundary.
method destroy(): voidmethod load(key: TKey): Promise<void>Loads one value, deduplicating concurrent requests for the same key.
method read(key: TKey): FormulaServiceCacheResult<TValue>method subscribeInvalidation(listener: (): void): (): void - classFormulaCubeCache
-
class FormulaCubeCache extends FormulaCubeServiceHost-driven OLAP cache. Formula evaluation reads synchronously and never starts connection I/O.
constructor FormulaCubeCache(loader: (query: FormulaCubeQuery): Promise<FormulaCubeResult>, warn?: (message: string, error: unknown): void): FormulaCubeCachemethod clear(): voidmethod destroy(): voidmethod load(query: FormulaCubeQuery): Promise<void>method read(query: FormulaCubeQuery): FormulaServiceCacheResult<FormulaCubeResult>method subscribeInvalidation(listener: (): void): (): void - classFormulaGoogleServiceCache
-
class FormulaGoogleServiceCache extends FormulaGoogleServiceHost-driven cache for AI, import, finance, and translation formulas.
constructor FormulaGoogleServiceCache(loader: (request: FormulaGoogleRequest): Promise<FormulaValue>, warn?: (message: string, error: unknown): void): FormulaGoogleServiceCachemethod clear(): voidmethod destroy(): voidmethod load(request: FormulaGoogleRequest): Promise<void>method read(request: FormulaGoogleRequest): FormulaServiceCacheResult<FormulaValue>method subscribeInvalidation(listener: (): void): (): void - classFormulaStockHistoryCache
-
class FormulaStockHistoryCache extends FormulaStockHistoryServiceHost-driven STOCKHISTORY cache; the loader owns transport, credentials, policy, and normalization.
constructor FormulaStockHistoryCache(loader: (request: FormulaStockHistoryRequest): Promise<FormulaError | unknown[][]>, warn?: (message: string, error: unknown): void): FormulaStockHistoryCachemethod clear(): voidmethod destroy(): voidmethod load(request: FormulaStockHistoryRequest): Promise<void>method read(request: FormulaStockHistoryRequest): FormulaServiceCacheResult<FormulaError | FormulaValue[][]>method subscribeInvalidation(listener: (): void): (): void - classFormulaTextServiceRegistry
-
class FormulaTextServiceRegistry extends IFormulaTextServiceProviderDefault tokenized editor-local registry for metadata and cached external formula services.
constructor FormulaTextServiceRegistry(): FormulaTextServiceRegistrymethod enforceCommandBoundary(isCommandExecuting: (): boolean, dispatchRemoval: (id: number): void): voidmethod getLanguageService(): FormulaLanguageService | undefinedmethod getPhoneticReader(): FormulaPhoneticReader | undefinedmethod registerLanguageService(service: FormulaLanguageService): (): voidmethod registerPhoneticReader(reader: FormulaPhoneticReader): (): voidmethod subscribeInvalidation(listener: (): void): (): voidmethod unregisterRegistration(id: number): void - classFormulaWebServiceCache
-
class FormulaWebServiceCache extends AsyncFormulaServiceCache<string, string | FormulaError>, FormulaWebServiceHost-driven WEBSERVICE cache; loaders own HTTP status checks, cancellation, and network policy.
Inherited members 5
Inherited from AsyncFormulaServiceCache.
Date and financial helpers
- functionnumericDateText
-
numericDateText(value: FormulaValue, context: EvalContext): number | undefined
Add-in functions
- variableaddInFunctions
-
addInFunctions: readonly FormulaFunction[] - variableCALL
-
Calls a host-authorized add-in procedure by registration id or module signature. Example:
=CALL("example","entry","JJ",1)invokesentrywith the argument1. - variableEUROCONVERT
-
Converts between the euro and legacy euro-member currencies using fixed EU rates. Example:
=EUROCONVERT(1.2,"DEM","EUR")returns0.61. - variableREGISTER_ID
-
Resolves a host-authorized add-in procedure to a stable numeric registration id. Example:
=REGISTER.ID("example","entry","JJ")returns the host's handle forentry.
Aggregation functions
- variableAGGREGATE
-
Applies an aggregate operation while honoring options for hidden rows and errors.
Example=AGGREGATE(4,6,{1,2,3}) returns 3. - variableAVERAGE
-
Returns the arithmetic mean of numeric values.
Example=AVERAGE(10,20,30) returns 20. - variableCOUNT
-
Counts numeric values in the supplied arguments and ranges.
Example=COUNT(10,20,30) returns 3. - variableSUBTOTAL
-
Calculates a subtotal for a range using a selected aggregation code.
Example=SUBTOTAL(9,A1:A3) returns the sum of A1:A3. - variableSUM
-
Adds numeric values from arguments, cells, and ranges.
Example=SUM(10,20,30) returns 60. - variableSUMIF
-
Adds values that satisfy one condition.
Example=SUMIF({1,2,3},">1",{10,20,30}) returns 50. - variableSUMIFS
-
Sums values whose corresponding ranges satisfy every criterion.
Array functions
- variableMDETERM
-
Returns the determinant of a square matrix.
Example=MDETERM({1,2;3,4}) returns -2. - variableMINVERSE
-
Returns the inverse matrix for a square, nonsingular matrix.
Example=MINVERSE({1,2;3,4}) returns {-2,1;1.5,-0.5}. - variableMMULT
-
Returns the matrix product of two compatible arrays.
Example=MMULT({1,2},{3;4}) returns 11. - variableMUNIT
-
Returns an identity matrix of the requested size.
Example=MUNIT(2) returns {1,0;0,1}. - variablePERCENTOF
-
Returns the proportion represented by one value or array within another.
Example=PERCENTOF({25},{100}) returns 0.25. - variableSEQUENCE
-
Generates a sequential array from a starting value and step.
Example=SEQUENCE(2,3,0,2) returns {0,2,4;6,8,10}. - variableSERIESSUM
-
Evaluates a power series from an initial value, step, and coefficients.
Example=SERIESSUM(2,0,1,{1,2,3}) returns 17. - variableSUMPRODUCT
-
Multiplies corresponding array elements and adds the products.
Example=SUMPRODUCT({2,3,4},{10,20,30}) returns 200. - variableSUMSQ
-
Returns the sum of the squares of numeric values.
Example=SUMSQ(0,3) returns 9. - variableSUMX2MY2
-
Returns the sum of the differences between corresponding squares.
Example=SUMX2MY2({1,2},{3,4}) returns -20. - variableSUMX2PY2
-
Returns the sum of the sums of corresponding squares.
Example=SUMX2PY2({1,2},{3,4}) returns 30. - variableSUMXMY2
-
Returns the sum of the squares of corresponding differences.
Example=SUMXMY2({1,2},{3,4}) returns 8.
Basic math functions
- variableABS
-
Returns the absolute value of a number.
Example=ABS(-2) returns 2. - variableEXP
-
Returns e raised to the specified power.
Example=EXP(0) returns 1. - variableINT
-
Rounds a number down to the nearest integer.
Example=INT(-0.1) returns -1. - variableLN
-
Returns the natural logarithm of a positive number.
Example=LN(1) returns 0. - variableLOG
-
Returns the logarithm of a number for a specified base.
Example=LOG(8,2) returns 3. - variableLOG10
-
Returns the base-10 logarithm of a positive number.
Example=LOG10(100) returns 2. - variableMOD
-
Returns the remainder after division.
Example=MOD(-3,2) returns 1. - variablePI
-
Returns the mathematical constant pi.
Example=PI()*0 returns 0. - variablePOWER
-
Raises a number to a specified power.
Example=POWER(2,0) returns 1. - variablePRODUCT
-
Multiplies all numeric values in the supplied arguments.
Example=PRODUCT(1,2,3) returns 6. - variableQUOTIENT
-
Returns the integer portion of a division.
Example=QUOTIENT(-10,3) returns -3. - variableSIGN
-
Returns -1, 0, or 1 according to the sign of a number.
Example=SIGN(0) returns 0. - variableSQRT
-
Returns the positive square root of a number.
Example=SQRT(0) returns 0. - variableSQRTPI
-
Returns the square root of a number multiplied by pi.
Example=SQRTPI(0) returns 0. - variableTRUNC
-
Truncates a number toward zero to a specified number of digits.
Example=TRUNC(-3.9) returns -3.
Combinatorics functions
- variableARABIC
-
Converts a Roman numeral text value to an Arabic number.
Example=ARABIC("MCMXCIX") returns 1999. - variableBASE
-
Converts a nonnegative integer to text in a selected radix.
Example=BASE(31,16) returns "1F". - variableCOMBIN
-
Returns the number of combinations for choosing items without repetition.
Example=COMBIN(8,2) returns 28. - variableCOMBINA
-
Returns the number of combinations for choosing items with repetition.
Example=COMBINA(4,3) returns 20. - variableDECIMAL
-
Converts text in a selected radix to a decimal number.
Example=DECIMAL("1010",2) returns 10. - variableGCD
-
Returns the greatest common divisor of numeric values.
Example=GCD(24,36) returns 12. - variableLCM
-
Returns the least common multiple of numeric values.
Example=LCM(4,6) returns 12. - variableMULTINOMIAL
-
Returns the multinomial coefficient for a set of numbers.
Example=MULTINOMIAL(1,1,1) returns 6. - variableROMAN
-
Converts an Arabic number to Roman numeral text.
Example=ROMAN(2024) returns "MMXXIV".
Compatibility functions
- variableBETADIST
-
BETADIST compatibility formula: Returns the cumulative beta distribution using the legacy signature.
Example=BETADIST(2,8,10,1,3) - variableBETAINV
-
BETAINV compatibility formula: Returns the inverse cumulative beta distribution.
Example=BETAINV(0.685470581,8,10,1,3) - variableBINOMDIST
-
BINOMDIST compatibility formula: Returns a binomial probability using the legacy function name.
Example=BINOMDIST(6,10,0.5,FALSE) - variableCEILING
-
CEILING compatibility formula: Rounds a number up to a legacy significance multiple.
Example=CEILING(2.5,1) - variableCHIDIST
-
CHIDIST compatibility formula: Returns the right-tailed chi-square probability.
Example=CHIDIST(18.307,10) - variableCHIINV
-
CHIINV compatibility formula: Returns the inverse right-tailed chi-square value.
Example=CHIINV(0.05,10) - variableCHITEST
-
CHITEST compatibility formula: Returns the chi-square independence test probability.
Example=CHITEST({58,35;11,25},{45,48;24,22}) - variablecompatibilityFunctions
-
compatibilityFunctions: readonly FormulaFunction[] - variableCONFIDENCE
-
CONFIDENCE compatibility formula: Returns a normal-distribution confidence interval.
Example=CONFIDENCE(0.05,2.5,50) - variableCOVAR
-
COVAR compatibility formula: Returns population covariance for two data sets.
Example=COVAR({2,4,8},{5,11,12}) - variableCRITBINOM
-
CRITBINOM compatibility formula: Returns the smallest binomial value meeting a cumulative probability.
Example=CRITBINOM(6,0.5,0.75) - variableEXPONDIST
-
EXPONDIST compatibility formula: Returns an exponential distribution value.
Example=EXPONDIST(0.2,10,TRUE) - variableFDIST
-
FDIST compatibility formula: Returns the right-tailed F distribution probability.
Example=FDIST(15.2069,6,4) - variableFINV
-
FINV compatibility formula: Returns the inverse right-tailed F distribution.
Example=FINV(0.01,6,4) - variableFLOOR
-
FLOOR compatibility formula: Rounds a number down to a legacy significance multiple.
Example=FLOOR(2.5,1) - variableFTEST
-
FTEST compatibility formula: Returns the two-tailed F-test probability.
Example=FTEST({1,2,3},{1,2,4}) - variableGAMMADIST
-
GAMMADIST compatibility formula: Returns a gamma distribution value.
Example=GAMMADIST(10,9,2,TRUE) - variableGAMMAINV
-
GAMMAINV compatibility formula: Returns an inverse gamma distribution value.
Example=GAMMAINV(0.068094,9,2) - variableHYPGEOMDIST
-
HYPGEOMDIST compatibility formula: Returns the non-cumulative hypergeometric probability.
Example=HYPGEOMDIST(1,4,8,20) - variableLOGINV
-
LOGINV compatibility formula: Returns the inverse lognormal distribution.
Example=LOGINV(0.039084,3.5,1.2) - variableLOGNORMDIST
-
LOGNORMDIST compatibility formula: Returns the cumulative lognormal distribution.
Example=LOGNORMDIST(4,3.5,1.2) - variableMODE
-
MODE compatibility formula: Returns the most frequent numeric value.
Example=MODE(1,1,2,3) - variableNEGBINOMDIST
-
NEGBINOMDIST compatibility formula: Returns the non-cumulative negative-binomial probability.
Example=NEGBINOMDIST(10,5,0.25) - variableNORM_INV
-
NORM.INV compatibility formula: Returns an inverse normal distribution value under the NORM.INV name.
Example=NORM.INV(0.908789,40,1.5) - variableNORMDIST
-
NORMDIST compatibility formula: Returns a normal distribution value.
Example=NORMDIST(42,40,1.5,TRUE) - variableNORMSDIST
-
NORMSDIST compatibility formula: Returns the cumulative standard normal distribution.
Example=NORMSDIST(1.333333) - variableNORMSINV
-
NORMSINV compatibility formula: Returns the inverse standard normal distribution.
Example=NORMSINV(0.908789) - variablePERCENTILE
-
PERCENTILE compatibility formula: Returns an inclusive percentile.
Example=PERCENTILE({1,2,3,4},0.5) - variablePERCENTRANK
-
PERCENTRANK compatibility formula: Returns an inclusive percentage rank.
Example=PERCENTRANK({1,2,3,4},3) - variablePOISSON
-
POISSON compatibility formula: Returns a Poisson distribution value.
Example=POISSON(2,5,TRUE) - variableQUARTILE
-
QUARTILE compatibility formula: Returns an inclusive quartile.
Example=QUARTILE({1,2,3,4,5},1) - variableRANK
-
RANK compatibility formula: Returns the rank of a number in a list.
Example=RANK(3,{1,2,3,4},0) - variableSTDEV
-
STDEV compatibility formula: Returns the sample standard deviation.
Example=STDEV(1,2,3) - variableSTDEVP
-
STDEVP compatibility formula: Returns the population standard deviation.
Example=STDEVP(1,2,3) - variableTDIST
-
TDIST compatibility formula: Returns a one- or two-tailed Student t probability.
Example=TDIST(1.96,60,2) - variableTINV
-
TINV compatibility formula: Returns the inverse two-tailed Student t value.
Example=TINV(0.05,10) - variableTTEST
-
TTEST compatibility formula: Returns a Student t-test probability.
Example=TTEST({1,2,3},{1,2,4},2,1) - variableVARP
-
VARP compatibility formula: Returns the population variance.
Example=VARP(1,2,3) - variableWEIBULL
-
WEIBULL compatibility formula: Returns a Weibull distribution value.
Example=WEIBULL(105,20,100,TRUE) - variableZTEST
-
ZTEST compatibility formula: Returns the one-tailed z-test probability.
Example=ZTEST({3,6,7,8},4)
Cube functions
- variablecubeFunctions
-
cubeFunctions: readonly [FormulaFunction, FormulaFunction, FormulaFunction, FormulaFunction, FormulaFunction, FormulaFunction, FormulaFunction] - variableCUBEKPIMEMBER
-
Returns a typed KPI property member. Example: CUBEKPIMEMBER("Sales","Revenue KPI",KPIGoal).
- variableCUBEMEMBER
-
Returns a typed cube member or tuple. Example: CUBEMEMBER("Sales","[Time].[Fiscal].[2004]").
- variableCUBEMEMBERPROPERTY
-
Returns one property of a cube member. Example: CUBEMEMBERPROPERTY("Sales","[Time].[2004]","Name").
- variableCUBERANKEDMEMBER
-
Returns the member at a one-based set rank. Example: CUBERANKEDMEMBER("Sales",A1,1).
- variableCUBESET
-
Returns a typed OLAP set. Example: CUBESET("Sales","[Product].Members","Products").
- variableCUBESETCOUNT
-
Returns the item count of a CUBESET result. Example: CUBESETCOUNT(A1).
- variableCUBEVALUE
-
Returns an aggregated cube value. Example: CUBEVALUE("Sales","[Measures].[Profit]").
Database functions
- variabledatabaseFunctions
-
databaseFunctions: readonly FormulaFunction[] - variableDAVERAGE
-
Returns the average of matching numeric database records.
Example=DAVERAGE(A1:C5,"Sales",E1:F2) - variableDCOUNT
-
Counts matching database records containing numbers.
Example=DCOUNT(A1:C5,"Sales",E1:F2) - variableDCOUNTA
-
Counts matching nonblank database records.
Example=DCOUNTA(A1:C5,"Name",E1:F2) - variableDGET
-
Returns the single field value from exactly one matching record.
Example=DGET(A1:C5,"Sales",E1:F2) - variableDMAX
-
Returns the largest matching numeric database value.
Example=DMAX(A1:C5,"Sales",E1:F2) - variableDMIN
-
Returns the smallest matching numeric database value.
Example=DMIN(A1:C5,"Sales",E1:F2) - variableDPRODUCT
-
Multiplies matching numeric database values.
Example=DPRODUCT(A1:C5,"Sales",E1:F2) - variableDSTDEV
-
Returns the sample standard deviation of matching records.
Example=DSTDEV(A1:C5,"Sales",E1:F2) - variableDSTDEVP
-
Returns the population standard deviation of matching records.
Example=DSTDEVP(A1:C5,"Sales",E1:F2) - variableDSUM
-
Sums matching numeric database values.
Example=DSUM(A1:C5,"Sales",E1:F2) - variableDVAR
-
Returns the sample variance of matching records.
Example=DVAR(A1:C5,"Sales",E1:F2) - variableDVARP
-
Returns the population variance of matching records.
Example=DVARP(A1:C5,"Sales",E1:F2)
Date and time functions
- interfaceExcelDateParts
-
property readonly day: numberproperty readonly month: numberproperty readonly phantomLeapDay?: booleanproperty readonly year: number - interfaceExcelDateTimeParseOptions
-
interface ExcelDateTimeParseOptions extends FormulaDateTimeParseOptionsOptions passed to locale-aware formula date/time parsers.
- interfaceParsedExcelDateTime
- typeBuiltInExcelDateTimeLocaleId
-
type BuiltInExcelDateTimeLocaleId = typeof BuiltInExcelDateTimeLocaleId[keyof typeof BuiltInExcelDateTimeLocaleId]BuiltInExcelDateTimeLocaleId: { Chinese: "zh"; English: "en" }Built-in locale identifiers registered by sear-plugin-i18n.
- variableChineseExcelDateTimeLocale
-
ChineseExcelDateTimeLocale: FormulaDateTimeLocaleChinese Excel-style date/time text parser and formatter.
- variableChineseExcelDateTimeSymbols
-
ChineseExcelDateTimeSymbols: FormulaDateTimeSymbolsChinese symbols consumed by the shared Excel-compatible date/time parser.
- variableCompatibleExcelDateTimeLocale
-
CompatibleExcelDateTimeLocale: FormulaDateTimeLocaleLegacy alias for the default parser, which now falls back to English symbols.
- variableDATE
-
Creates an Excel date serial from year, month, and day parts.
Example=DATE(2024,2,29) returns 45351. - variableDATEDIF
-
Returns the elapsed days, months, or years between two dates.
Example=DATEDIF("1/1/2001","1/1/2003","Y") returns 2. - variabledateTimeFunctions
-
dateTimeFunctions: readonly FormulaFunction[] - variableDATEVALUE
-
Converts date text into an Excel date serial.
Example=DATEVALUE("22-MAY-2011") returns 40685. - variableDAY
-
Returns the day of month for a date serial.
Example=DAY("2011-4-15") returns 15. - variableDAYS
-
Returns the number of days between an end date and a start date.
Example=DAYS("2021-12-31","2021-1-1") returns 364. - variableDAYS360
-
Returns the day difference using a 360-day accounting year.
Example=DAYS360("2011-1-1","2011-12-31") returns 360. - variableEDATE
-
Returns the date serial offset by a whole number of months.
Example=EDATE("2011-1-15",2) returns 40617. - variableEnglishExcelDateTimeLocale
-
EnglishExcelDateTimeLocale: FormulaDateTimeLocaleEnglish Excel-style date/time text parser and formatter.
- variableEnglishExcelDateTimeSymbols
-
EnglishExcelDateTimeSymbols: FormulaDateTimeSymbolsEnglish symbols consumed by the shared Excel-compatible date/time parser.
- variableEOMONTH
-
Returns the last day of the month offset from a start date.
Example=EOMONTH("2011-1-1",1) returns 40602. - variableEPOCHTODATE
-
EPOCHTODATE converts a Unix timestamp to an Excel UTC date-time serial. Example: =EPOCHTODATE(0) returns 25569.
- variableEXCEL_1904_SERIAL_OFFSET
-
EXCEL_1904_SERIAL_OFFSET: 1462 - variableHOUR
-
Returns the hour component from a time or date-time value.
Example=HOUR("2011-7-18 7:45") returns 7. - variableISOWEEKNUM
-
Returns the ISO 8601 week number for a date.
Example=ISOWEEKNUM("2012-3-9") returns 10. - variableLAST_EXCEL_DATE_SERIAL
-
LAST_EXCEL_DATE_SERIAL: 2958465 - variableMINUTE
-
Returns the minute component from a time or date-time value.
Example=MINUTE("12:45 PM") returns 45. - variableMONTH
-
Returns the month number for a date serial.
Example=MONTH("2011-4-15") returns 4. - variableNETWORKDAYS
-
Counts whole workdays between two dates, excluding weekends and holidays.
Example=NETWORKDAYS("2012-10-1","2013-3-1",{"2012-11-22";"2012-12-4";"2013-1-21"}) returns 107. - variableNETWORKDAYS_INTL
-
NETWORKDAYS.INTL: Counts workdays between dates with custom weekend rules. Example: =NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),"0010001",{"2006/1/2","2006/1/16"}) returns 20.
- variableNOW
-
Returns the current date and time as an Excel serial.
Example=NOW()-0.5 returns the serial for 12 hours before the injected clock time. - variableSECOND
-
Returns the second component from a time or date-time value.
Example=SECOND("4:48:18 PM") returns 18. - variableTIME
-
Converts hour, minute, and second parts into a fraction of one day.
Example=TIME(12,0,0) returns 0.5. - variableTIMEVALUE
-
Converts time text into the fractional part of one day.
Example=TIMEVALUE("2:24 AM") returns 0.1. - variableTO_DATE
-
TO_DATE converts a number to its Excel date serial by removing its time fraction. Example: =TO_DATE(45351.75) returns 45351.
- variableTODAY
-
Returns the current date as an Excel serial without a time fraction.
Example=TODAY()+5 returns the serial for five days after the injected current date. - variableWEEKDAY
-
Returns the day of week using the requested Excel return type.
Example=WEEKDAY("2008-2-14",2) returns 4. - variableWEEKNUM
-
Returns the week number of a date using Excel week-numbering rules.
Example=WEEKNUM("2012-3-9",2) returns 11. - variableWORKDAY
-
Returns the date serial after moving a number of workdays from a start date.
Example=WORKDAY("2008-10-1",151,{"11/26/2008";"12/4/2008";"1/21/2009"}) returns 39938. - variableWORKDAY_INTL
-
WORKDAY.INTL: Returns a workday-offset date serial with custom weekend rules. Example: =WORKDAY.INTL(DATE(2012,1,1),30,17) returns 40944.
- variableYEAR
-
Returns the year for a date serial.
Example=YEAR("7/5/2025") returns 2025. - variableYEARFRAC
-
Returns the fraction of a year represented by two dates.
Example=YEARFRAC("1/1/2012","7/30/2012",1) returns 0.5765027322404371. - functioncreateDefaultFormulaLocaleProvider
-
createDefaultFormulaLocaleProvider(): IFormulaLocaleProviderCreates the default formula locale provider used when no i18n plugin is installed.
- functioncreateExcelDateTimeLocale
-
createExcelDateTimeLocale(input: BuiltInExcelDateTimeLocaleId | { locale: string; symbols?: FormulaDateTimeSymbols }): FormulaDateTimeLocaleCreates a built-in Excel-compatible date/time locale.
- functioncurrentExcelDateTimeSerial
-
currentExcelDateTimeSerial(context: EvalContext): number - functionexcelDatePartsFromSerial
-
excelDatePartsFromSerial(serial: number): ExcelDateParts | null - functionexcelSerialFromDateParts
-
excelSerialFromDateParts(year: number, month: number, day: number): number | null - functionexcelSerialToUnixMillis
-
excelSerialToUnixMillis(serial: number): number - functionexcelTimeFraction
-
excelTimeFraction(hour: number, minute: number, second: number): number | null - functionformatExcelDateTime
-
formatExcelDateTime(serial: number, pattern?: string): string - functionparseExcelDate
-
parseExcelDate(value: unknown, options?: ExcelDateTimeParseOptions): number | null - functionparseExcelDateTime
-
parseExcelDateTime(value: unknown, options?: ExcelDateTimeParseOptions): ParsedExcelDateTime | null - functionparseExcelTime
-
parseExcelTime(value: unknown, options?: ExcelDateTimeParseOptions): number | null - functionunixMillisToExcelSerial
-
unixMillisToExcelSerial(epochMs: number): number
Financial functions
- interfaceCouponSchedule
-
Coupon schedule resolved around a bond settlement date.
property readonly basis: FinancialDayCountBasisproperty readonly couponsRemaining: numberproperty readonly daysFromPrevious: numberproperty readonly daysInPeriod: numberproperty readonly daysToNext: numberproperty readonly frequency: 1 | 2 | 4property readonly nextCoupon: numberproperty readonly previousCoupon: number - interfaceFinancialRootOptions
-
property readonly initialGuess?: numberproperty readonly lowerBound?: numberproperty readonly maxIterations?: numberproperty readonly tolerance?: numberproperty readonly upperBound?: number - typeBondResult
-
type BondResult = number | FormulaError - typeDayCountResult
-
type DayCountResult = number | FormulaError - typeDepreciationResult
-
type DepreciationResult = number | FormulaError - typeFinancialDayCountBasis
-
type FinancialDayCountBasis = 0 | 1 | 2 | 3 | 4Excel financial day-count conventions.
- typeFinancialNumberResult
-
type FinancialNumberResult = number | FormulaError - typeFinancialRootResult
-
type FinancialRootResult = number | FormulaError - variableACCRINT
-
ACCRINT: accrued interest for a periodically paying security.
- variableACCRINTM
-
ACCRINTM: accrued interest for a security paying interest at maturity.
- variableAMORDEGRC
-
AMORDEGRC: declining-balance depreciation with a French accounting coefficient.
- variableAMORLINC
-
AMORLINC: straight-line depreciation prorated over the first period.
- variableCOUPDAYBS
-
COUPDAYBS: days from the beginning of a coupon period to settlement.
- variableCOUPDAYS
-
COUPDAYS: number of days in the coupon period containing settlement.
- variableCOUPDAYSNC
-
COUPDAYSNC: days from settlement to the next coupon.
- variableCOUPNCD
-
COUPNCD: next coupon date after settlement.
- variableCOUPNUM
-
COUPNUM: number of coupons remaining after settlement.
- variableCOUPPCD
-
COUPPCD: previous coupon date before settlement.
- variableCUMIPMT
-
CUMIPMT: cumulative interest paid between two periods.
- variableCUMPRINC
-
CUMPRINC: cumulative principal paid between two periods.
- variableDB
-
DB: fixed-declining balance depreciation.
- variableDDB
-
DDB: double-declining or custom-factor depreciation.
- variableDISC
-
DISC: annualized discount rate for a discounted security.
- variableDOLLARDE
-
DOLLARDE: converts a fractional dollar price to a decimal price.
- variableDOLLARFR
-
DOLLARFR: converts a decimal dollar price to a fractional price.
- variableDURATION
-
DURATION: Macaulay duration of a coupon security.
- variableEFFECT
-
EFFECT: effective annual interest rate.
- variablefinancialFunctions
-
financialFunctions: readonly FormulaFunction[]All built-in financial worksheet functions.
- variableFV
-
FV: future value of an annuity.
- variableFVSCHEDULE
-
FVSCHEDULE: future value using a series of variable rates.
- variableINTRATE
-
INTRATE: annualized interest rate of a fully invested security.
- variableIPMT
-
IPMT: interest payment for a period.
- variableIRR
-
IRR: periodic internal rate of return.
- variableISPMT
-
ISPMT: interest paid during a specific period of an investment.
- variableMDURATION
-
MDURATION: modified duration of a coupon security.
- variableMIRR
-
MIRR: modified internal rate of return.
- variableNOMINAL
-
NOMINAL: nominal annual interest rate.
- variableNPER
-
NPER: number of periods for an annuity.
- variableNPV
-
NPV: net present value of periodic cash flows.
- variableODDFPRICE
-
ODDFPRICE: clean price for a security with an odd first coupon period.
- variableODDFYIELD
-
ODDFYIELD: yield for a security with an odd first coupon period.
- variableODDLPRICE
-
ODDLPRICE: clean price for a security with an odd last coupon period.
- variableODDLYIELD
-
ODDLYIELD: yield for a security with an odd last coupon period.
- variablePDURATION
-
PDURATION: periods required for an investment to reach a target value.
- variablePMT
-
PMT: periodic payment for an annuity.
- variablePPMT
-
PPMT: principal payment for a period.
- variablePRICE
-
PRICE: clean price of a coupon security.
- variablePRICEDISC
-
PRICEDISC: price of a discounted security.
- variablePRICEMAT
-
PRICEMAT: price of a security paying interest at maturity.
- variablePV
-
PV: present value of an annuity.
- variableRATE
-
RATE: periodic interest rate for an annuity.
- variableRECEIVED
-
RECEIVED: amount received at maturity for a discounted security.
- variableRRI
-
RRI: equivalent growth rate for an investment.
- variableSLN
-
SLN: straight-line depreciation.
- variableSYD
-
SYD: sum-of-years-digits depreciation.
- variableTBILLEQ
-
TBILLEQ: bond-equivalent yield for a Treasury bill.
- variableTBILLPRICE
-
TBILLPRICE: price per 100 for a Treasury bill.
- variableTBILLYIELD
-
TBILLYIELD: discount yield for a Treasury bill price.
- variableVDB
-
VDB: variable declining-balance depreciation.
- variableXIRR
-
XIRR: internal rate of return for irregular cash-flow dates.
- variableXNPV
-
XNPV: net present value for irregular cash-flow dates.
- variableYIELD
-
YIELD: yield of a coupon security.
- variableYIELDDISC
-
YIELDDISC: yield of a discounted security.
- variableYIELDMAT
-
YIELDMAT: yield of a security paying interest at maturity.
- functionbondDuration
-
bondDuration(settlement: number, maturity: number, annualRate: number, annualYield: number, redemption?: number, frequencyValue?: number, basisValue?: number): BondResultComputes Macaulay duration from a clean bond price.
- functionbondPrice
-
bondPrice(settlement: number, maturity: number, annualRate: number, annualYield: number, redemption?: number, frequencyValue?: number, basisValue?: number): BondResultPrices a coupon bond per 100 face value using a clean price.
- functioncollectFinancialCashFlows
-
collectFinancialCashFlows(arg: FormulaArg | undefined, context: EvalContext, acceptFormattedText?: boolean): number[] | FormulaErrorCollects a scalar, array, or range argument into finite numeric cash flows.
- functioncollectFinancialDateSerials
-
collectFinancialDateSerials(arg: FormulaArg | undefined, context: EvalContext, acceptFormattedText?: boolean): number[] | FormulaErrorDate arguments use whole serial days and reject boolean values.
- functioncollectFinancialScheduleRates
-
collectFinancialScheduleRates(arg: FormulaArg | undefined, context: EvalContext): number[] | FormulaErrorMulti-cell schedules reject stored text rates even when the text is numeric.
- functioncollectNpvCashFlows
-
collectNpvCashFlows(arg: FormulaArg | undefined, context: EvalContext): number[] | FormulaErrorNPV ignores nonnumeric cells in references without consuming a period.
- functioncouponAmount
-
couponAmount(faceValue: number, annualRate: number, frequency: 1 | 2 | 4): number | FormulaErrorReturns the coupon amount for a face value and annual coupon rate.
- functiondecliningBalanceDepreciation
-
decliningBalanceDepreciation(cost: number, salvage: number, life: number, period: number, factor?: number): DepreciationResultDeclining-balance depreciation for a one-based period.
- functionfinancialDayCountDays
-
financialDayCountDays(start: number, end: number, basis: FinancialDayCountBasis): DayCountResultReturns the number of days between two Excel serial dates for a basis.
- functionfinancialDivisionError
-
financialDivisionError(): FormulaErrorReturns a standard #DIV/0! result for an undefined periodic payment.
- functionfinancialNumberError
-
financialNumberError(): FormulaErrorReturns a standard #NUM! result for an invalid basis or date interval.
- functionfinancialValueError
-
financialValueError(): FormulaErrorReturns a standard #VALUE! result for malformed financial inputs.
- functionfinancialYearFraction
-
financialYearFraction(start: number, end: number, basis: FinancialDayCountBasis): DayCountResultReturns the Excel year fraction for a financial basis.
- functionforEachFinancialCashFlow
-
forEachFinancialCashFlow(arg: FormulaArg, context: EvalContext, callback: (value: number, index: number): boolean | void): FormulaError | undefinedStreams numeric values from a range without allocating an intermediate matrix.
- functionfutureValue
-
futureValue(rate: number, periods: number, payment: number, presentValue: number, type?: number): FinancialNumberResultComputes the future value of a periodic annuity.
- functionisFinancialMonthEnd
-
isFinancialMonthEnd(serial: number): booleanReturns whether an Excel serial date is the last calendar day of its month.
- functionisTextualFinancialReference
-
isTextualFinancialReference(arg: FormulaArg | undefined, context: EvalContext): boolean - functionnormalizeCouponFrequency
-
normalizeCouponFrequency(value: number): 1 | 2 | 4 | FormulaErrorValidates a bond frequency used by Excel financial functions.
- functionnormalizeFinancialBasis
-
normalizeFinancialBasis(value: number | undefined): FormulaError | FinancialDayCountBasisChecks and normalizes the five Excel day-count basis values.
- functionoddLastRegularPeriodDays
-
oddLastRegularPeriodDays(lastCoupon: number, frequency: 1 | 2 | 4, basis: FinancialDayCountBasis): number | FormulaErrorReturns the basis-dependent length of a regular period after the final coupon date.
- functionpayment
-
payment(rate: number, periods: number, presentValue: number, future?: number, type?: number): FinancialNumberResultComputes a periodic payment for an annuity.
- functionperiodicNpv
-
periodicNpv(rate: number, cashFlows: readonly number[]): FinancialNumberResultComputes periodic NPV. The first value is discounted by one period.
- functionreadFinancialNumber
-
readFinancialNumber(arg: FormulaArg | undefined, context: EvalContext, options?: { acceptLocalizedCurrency?: boolean }): FinancialNumberResultReads a scalar financial input, preserving formula errors.
- functionresolveCouponSchedule
-
resolveCouponSchedule(settlement: number, maturity: number, frequencyValue: number, basisValue?: number): FormulaError | CouponScheduleResolves the previous/next coupon dates and basis-dependent day counts.
- functionresolveOddFirstCouponSchedule
-
resolveOddFirstCouponSchedule(settlement: number, maturity: number, firstCoupon: number, frequencyValue: number, basisValue?: number): FormulaError | CouponScheduleResolves an odd first coupon schedule anchored at its first payment date.
- functionsolveFinancialRoot
-
solveFinancialRoot(functionValue: (value: number): number, derivative: (value: number): number | undefined, options?: FinancialRootOptions): FinancialRootResultSolves a one-dimensional financial equation with a bracketed Newton/bisection method. The fixed iteration budget prevents a malformed cash-flow formula from blocking recalculation.
- functionstraightLineDepreciation
-
straightLineDepreciation(cost: number, salvage: number, life: number): DepreciationResultStraight-line depreciation for one period.
- functionsumOfYearsDepreciation
-
sumOfYearsDepreciation(cost: number, salvage: number, life: number, period: number): DepreciationResultSum-of-years-digits depreciation for a one-based period.
- functionvariableDecliningBalance
-
variableDecliningBalance(cost: number, salvage: number, life: number, startPeriod: number, endPeriod: number, factor?: number, noSwitch?: boolean): DepreciationResultVariable declining-balance depreciation, with Excel's straight-line switch.
Google service functions
- variableADD
-
ADD returns the sum of two values. Example: =ADD(2,3) returns 5.
- variableAI
-
AI reads a Gemini result from a host-managed cache. Example: =AI("Summarize",A1) returns cached generated text.
- variableARRAYFORMULA
-
ARRAYFORMULA exposes an expression's array result. Example: =ARRAYFORMULA({1,2}*2) returns {2,4}.
- variableAVERAGE_WEIGHTED_DOTTED
-
AVERAGE.WEIGHTED is the dotted Google Sheets alias. Example: =AVERAGE.WEIGHTED({10,20},{1,3}) returns 17.5.
- variableCOUNTUNIQUE
-
COUNTUNIQUE counts distinct values. Example: =COUNTUNIQUE(1,1,2) returns 2.
- variableDIVIDE
-
DIVIDE divides one value by another. Example: =DIVIDE(6,2) returns 3.
- variableEQ
-
EQ tests two values for equality. Example: =EQ(2,2) returns TRUE.
- variableGOOGLEFINANCE
-
GOOGLEFINANCE reads market data from a host-managed cache. Example: =GOOGLEFINANCE("NASDAQ:GOOG","price") returns cached price data.
- variablegoogleFunctions
-
googleFunctions: readonly FormulaFunction[] - variableGOOGLETRANSLATE
-
GOOGLETRANSLATE reads a translation from a host-managed cache. Example: =GOOGLETRANSLATE("hello","en","zh-CN") returns cached text.
- variableGT
-
GT tests whether the first value is greater. Example: =GT(3,2) returns TRUE.
- variableGTE
-
GTE tests whether the first value is at least the second. Example: =GTE(2,2) returns TRUE.
- variableIMPORTDATA
-
IMPORTDATA reads CSV or TSV data from a host-managed cache. Example: =IMPORTDATA("https://example.com/data.csv") returns cached rows.
- variableIMPORTFEED
-
IMPORTFEED reads RSS or Atom data from a host-managed cache. Example: =IMPORTFEED("https://example.com/feed.xml") returns cached feed rows.
- variableIMPORTHTML
-
IMPORTHTML reads an HTML table or list from a host-managed cache. Example: =IMPORTHTML("https://example.com","table",1) returns cached rows.
- variableIMPORTRANGE
-
IMPORTRANGE reads another spreadsheet through a host-managed cache. Example: =IMPORTRANGE("sheet-id","A1:B2") returns cached cells.
- variableIMPORTXML
-
IMPORTXML reads XPath-selected data from a host-managed cache. Example: =IMPORTXML("https://example.com","//title") returns cached nodes.
- variableJOIN
-
JOIN concatenates values with a delimiter. Example: =JOIN("-",{1,2,3}) returns "1-2-3".
- variableLT
-
LT tests whether the first value is smaller. Example: =LT(2,3) returns TRUE.
- variableLTE
-
LTE tests whether the first value is no greater than the second. Example: =LTE(2,2) returns TRUE.
- variableMINUS
-
MINUS subtracts the second value from the first. Example: =MINUS(5,2) returns 3.
- variableMULTIPLY
-
MULTIPLY multiplies two values. Example: =MULTIPLY(3,4) returns 12.
- variableNE
-
NE tests two values for inequality. Example: =NE(2,3) returns TRUE.
- variablePOW
-
POW raises a base to an exponent. Example: =POW(2,3) returns 8.
- variableQUERY
-
QUERY runs a Google Visualization-style query over tabular data. Example: =QUERY({"A",1;"B",2},"select Col1 where Col2 > 1",0) returns "B".
- variableSORTN
-
SORTN returns the first ranked rows after sorting. Example: =SORTN({3;1;2},2) returns {1;2}.
- variableSPARKLINE
-
SPARKLINE renders numeric data as a compact text sparkline. Example: =SPARKLINE({1,3,2}) returns a three-glyph trend.
- variableSPLIT
-
SPLIT divides text into a spilling row. Example: =SPLIT("a,b,c",",") returns {"a","b","c"}.
- variableTO_DOLLARS
-
TO_DOLLARS converts a value to a dollar amount. Example: =TO_DOLLARS(12.5) returns 12.5.
- variableTO_PERCENT
-
TO_PERCENT converts a decimal to its percentage value. Example: =TO_PERCENT(0.25) returns 0.25 for percentage formatting.
- variableTO_PURE_NUMBER
-
TO_PURE_NUMBER strips display formatting and returns a number. Example: =TO_PURE_NUMBER("42") returns 42.
- variableTO_TEXT
-
TO_TEXT converts a scalar to text. Example: =TO_TEXT(42) returns "42".
- variableUMINUS
-
UMINUS reverses a number's sign. Example: =UMINUS(3) returns -3.
- variableUNARY_PERCENT
-
UNARY_PERCENT converts a percentage value to its decimal. Example: =UNARY_PERCENT(25) returns 0.25.
- variableUPLUS
-
UPLUS returns the numeric value unchanged. Example: =UPLUS("3") returns 3.
Information functions
- variableCELL
-
Returns information about a referenced cell.
Example=CELL("row",B3) - variableERROR_TYPE
-
ERROR.TYPE: Returns the numeric code for an error value.
Example=ERROR.TYPE(NA()) - variableINFO
-
Returns host or workbook environment information.
Example=INFO("numfile") - variableinformationFunctions
-
informationFunctions: readonly FormulaFunction[] - variableISBETWEEN
-
ISBETWEEN tests whether a value lies between bounds. Example: =ISBETWEEN(5,1,10) returns TRUE.
- variableISBLANK
-
Returns TRUE only for a genuinely empty referenced cell.
Example=ISBLANK(A1) - variableISDATE
-
ISDATE reports whether a value is a valid date or date serial. Example: =ISDATE("2024-02-29") returns TRUE.
- variableISEMAIL
-
ISEMAIL checks common email-address syntax without contacting a server. Example: =ISEMAIL("a@example.com") returns TRUE.
- variableISERR
-
Returns TRUE for errors other than #N/A.
Example=ISERR(1/0) - variableISERROR
-
Returns TRUE for any formula error.
Example=ISERROR(1/0) - variableISEVEN
-
Returns TRUE when a number is even.
Example=ISEVEN(4) - variableISFORMULA
-
Returns TRUE when a referenced cell contains a formula.
Example=ISFORMULA(A1) - variableISLOGICAL
-
Returns TRUE for logical values.
Example=ISLOGICAL(TRUE) - variableISNA
-
Returns TRUE only for #N/A.
Example=ISNA(NA()) - variableISNONTEXT
-
Returns TRUE for non-text values.
Example=ISNONTEXT(42) - variableISNUMBER
-
Returns TRUE for numeric values.
Example=ISNUMBER(42) - variableISODD
-
Returns TRUE when a number is odd.
Example=ISODD(3) - variableISREF
-
Returns TRUE when its argument preserves reference identity.
Example=ISREF(A1) - variableISTEXT
-
Returns TRUE for text values.
Example=ISTEXT("text") - variableISURL
-
ISURL checks whether text is an absolute URL. Example: =ISURL("https://example.com") returns TRUE.
- variableN
-
Converts numbers, booleans, and other values to a numeric projection.
Example=N(TRUE) - variableNA
-
Returns the #N/A error value.
Example=NA() - variableSHEET
-
Returns the one-based index of a sheet.
Example=SHEET() - variableSHEETS
-
Returns the number of sheets in the workbook.
Example=SHEETS() - variableSTOCKHISTORY
-
Returns historical market data from a host-managed synchronous cache.
Example=STOCKHISTORY("MSFT",DATE(2024,1,1)) - variableTYPE
-
Returns the numeric type code for a formula value.
Example=TYPE(42)
Logical and conditional functions
- variableAND
-
Returns TRUE when every supplied logical value is TRUE.
Example=AND(TRUE,1) - variableFALSE
-
Returns the logical value FALSE.
Example=FALSE() - variableIF
-
Returns one value when a condition is true and another when it is false.
- variableIFERROR
-
Replaces a formula error with a fallback value.
Example=IFERROR(1/0,0) returns 0. - variableIFNA
-
Returns a fallback only when the input is #N/A.
Example=IFNA(NA(),0) - variableIFS
-
Returns the value paired with the first TRUE condition.
Example=IFS(A1>90,"A",A1>80,"B",TRUE,"C") - variableLET
-
Evaluates sequential name/value bindings in a child lexical scope.
Example=LET(rate,2,amount,5,rate*amount) returns 10. - variablelogicalFunctions
-
logicalFunctions: readonly FormulaFunction[] - variableNOT
-
Returns the inverse of a logical value.
Example=NOT(TRUE) - variableOR
-
Returns TRUE when any supplied logical value is TRUE.
Example=OR(FALSE,TRUE) - variableSWITCH
-
Matches one expression and returns its paired result.
Example=SWITCH(2,1,"one",2,"two","other") - variableTRUE
-
Returns the logical value TRUE.
Example=TRUE() - variableXOR
-
Returns TRUE when an odd number of values are TRUE.
Example=XOR(TRUE,FALSE)
Lookup and reference functions
- variableADDRESS
-
ADDRESS returns an A1 or R1C1 address. Example: =ADDRESS(2,3) returns $C$2.
- variableAREAS
-
AREAS counts areas in a reference. Example: =AREAS(A1:B2) returns 1.
- variableARRAY_CONSTRAIN
-
ARRAY_CONSTRAIN limits an array to a row and column count. Example: =ARRAY_CONSTRAIN(A1:C3,2,1).
- variableCHOOSE
-
CHOOSE selects a value by index. Example: =CHOOSE(2,"A","B") returns B.
- variableCHOOSECOLS
-
CHOOSECOLS selects columns from an array. Example: =CHOOSECOLS(A1:C2,1,3).
- variableCHOOSEROWS
-
CHOOSEROWS selects rows from an array. Example: =CHOOSEROWS(A1:C3,1,3).
- variableCOLUMN
-
COLUMN returns a reference's first column number. Example: =COLUMN(C5) returns 3.
- variableCOLUMNS
-
COLUMNS returns the number of columns in a reference. Example: =COLUMNS(A1:C2) returns 3.
- variableDROP
-
DROP removes rows and columns. Example: =DROP(A1:C3,1) removes the first row.
- variableEXPAND
-
EXPAND pads an array. Example: =EXPAND(A1:B2,3,3,0).
- variableFILTER
-
FILTER returns rows matching a Boolean include array. Example: =FILTER(A1:B3,{TRUE;FALSE;TRUE}).
- variableFLATTEN
-
FLATTEN combines one or more arrays into a single column. Example: =FLATTEN(A1:B2,C1:C2).
- variableFORMULATEXT
-
FORMULATEXT returns source text for a formula cell. Example: =FORMULATEXT(A1).
- variableGETPIVOTDATA
-
GETPIVOTDATA reads a host pivot data service. Example: =GETPIVOTDATA("Sales",A1).
- variableGROUPBY
-
GROUPBY groups rows by a key and aggregates values by sum. Example: =GROUPBY(A2:A5,B2:B5,SUM).
- variableHLOOKUP
-
HLOOKUP searches a table's first row. Example: =HLOOKUP("B",A1:C2,2,FALSE).
- variableHSTACK
-
HSTACK joins arrays horizontally. Example: =HSTACK({1;2},{3;4}).
- variableHYPERLINK
-
HYPERLINK returns display text for a host-rendered link. Example: =HYPERLINK("https://example.com","Open").
- variableIMAGE
-
IMAGE reads a host-managed image cache. Example: =IMAGE("https://example.com/logo.png").
- variableINDEX
-
INDEX returns a value or subarray by row and column. Example: =INDEX(A1:C3,2,3).
- variableINDIRECT
-
INDIRECT resolves text to a reference. Example: =INDIRECT("A1") returns A1.
- variableLOOKUP
-
LOOKUP performs approximate vector lookup. Example: =LOOKUP(2,{1,2,3},{"A","B","C"}) returns B.
- variablelookupFunctionNames
-
lookupFunctionNames: readonly ["ADDRESS", "AREAS", "ARRAY_CONSTRAIN", "CHOOSE", "CHOOSECOLS", "CHOOSEROWS", "COLUMN", "COLUMNS", "DROP", "EXPAND", "FILTER", "FLATTEN", "FORMULATEXT", "GETPIVOTDATA", "GROUPBY", "HLOOKUP", "HSTACK", "HYPERLINK", "IMAGE", "INDEX", "INDIRECT", "LOOKUP", "MATCH", "OFFSET", "PIVOTBY", "ROW", "ROWS", "RTD", "SORT", "SORTBY", "TAKE", "TOCOL", "TOROW", "TRANSPOSE", "TRIMRANGE", "UNIQUE", "VSTACK", "WRAPCOLS", "WRAPROWS", "XLOOKUP", "XMATCH"]Names implemented by the lookup/reference and dynamic-array family.
- variableMATCH
-
MATCH returns the position of a lookup value. Example: =MATCH("B",{"A";"B"},0) returns 2.
- variableOFFSET
-
OFFSET returns a shifted reference. Example: =OFFSET(A1,1,1) returns B2.
- variablePIVOTBY
-
PIVOTBY creates a two-key summary by sum. Example: =PIVOTBY(A2:A5,B2:B5,C2:C5).
- variableROW
-
ROW returns a reference's first row number. Example: =ROW(C5) returns 5.
- variableROWS
-
ROWS returns the number of rows in a reference. Example: =ROWS(A1:C2) returns 2.
- variableRTD
-
RTD reads a host-managed real-time data cache. Example: =RTD("provider",,"price","MSFT").
- variableSORT
-
SORT orders rows by one column. Example: =SORT(A1:B3,1,1).
- variableSORTBY
-
SORTBY orders rows using one or more key arrays. Example: =SORTBY(A1:B3,B1:B3,1).
- variableTAKE
-
TAKE keeps rows and columns. Example: =TAKE(A1:C3,2) keeps the first two rows.
- variableTOCOL
-
TOCOL flattens an array into one column. Example: =TOCOL(A1:B2).
- variableTOROW
-
TOROW flattens an array into one row. Example: =TOROW(A1:B2).
- variableTRANSPOSE
-
TRANSPOSE swaps array rows and columns. Example: =TRANSPOSE(A1:B2).
- variableTRIMRANGE
-
TRIMRANGE removes fully blank outer rows and columns. Example: =TRIMRANGE(A1:D5).
- variableUNIQUE
-
UNIQUE returns distinct rows. Example: =UNIQUE({1;1;2}) returns 1 and 2.
- variableVLOOKUP
-
Searches the first column of a table and returns a value from a selected column.
Example=VLOOKUP(2,{1,"A";2,"B"},2,FALSE) returns "B". - variableVSTACK
-
VSTACK joins arrays vertically. Example: =VSTACK({1,2},{3,4}).
- variableWRAPCOLS
-
WRAPCOLS wraps a vector by columns. Example: =WRAPCOLS({1,2,3},2,0).
- variableWRAPROWS
-
WRAPROWS wraps a vector by rows. Example: =WRAPROWS({1,2,3},2,0).
- variableXLOOKUP
-
XLOOKUP searches a vector and returns a corresponding value. Example: =XLOOKUP(2,{1,2},{"A","B"}).
- variableXMATCH
-
XMATCH returns a position with modern MATCH modes. Example: =XMATCH("B",{"A";"B"}).
Random functions
- variableRAND
-
Returns a random decimal number greater than or equal to 0 and less than 1.
Example=RAND() returns a value in the range [0,1). - variableRANDARRAY
-
Generates an array of random numbers with the requested shape and bounds.
Example=RANDARRAY(1,4,5,5,TRUE) returns {5,5,5,5}. - variableRANDBETWEEN
-
Returns a random integer between two inclusive bounds.
Example=RANDBETWEEN(7,7) returns 7.
Rounding functions
- variableCEILING_MATH
-
CEILING.MATH: Rounds a number up to the nearest multiple.
Example=CEILING.MATH(24.3,5) returns 25. - variableCEILING_PRECISE
-
CEILING.PRECISE: Rounds a number up to the nearest multiple, using an absolute direction.
Example=CEILING.PRECISE(-4.3,2) returns -4. - variableEVEN
-
Rounds a number away from zero to the nearest even integer.
Example=EVEN(1.5) returns 2. - variableFACT
-
Returns the factorial of a nonnegative integer.
Example=FACT(5) returns 120. - variableFACTDOUBLE
-
Returns the double factorial of a nonnegative integer.
Example=FACTDOUBLE(7) returns 105. - variableFLOOR_MATH
-
FLOOR.MATH: Rounds a number down to the nearest multiple.
Example=FLOOR.MATH(-8.1,2) returns -10. - variableFLOOR_PRECISE
-
FLOOR.PRECISE: Rounds a number down to the nearest multiple using an absolute direction.
Example=FLOOR.PRECISE(-3.2,1) returns -4. - variableISO_CEILING
-
ISO.CEILING: Rounds a number up to the nearest multiple.
Example=ISO.CEILING(-4.3,2) returns -4. - variableMROUND
-
Rounds a number to the nearest specified multiple.
Example=MROUND(10,3) returns 9. - variableODD
-
Rounds a number away from zero to the nearest odd integer.
Example=ODD(0) returns 1. - variableROUND
-
Rounds a number to a specified number of digits.
Example=ROUND(-1.475,2) returns -1.48. - variableROUNDDOWN
-
Rounds a number toward zero to a specified number of digits.
Example=ROUNDDOWN(-3.9,0) returns -3. - variableROUNDUP
-
Rounds a number away from zero to a specified number of digits.
Example=ROUNDUP(-3.1,0) returns -4.
Statistics functions
- variableAVEDEV
-
AVEDEV formula. Evaluates the Excel-compatible AVEDEV statistical function. Example: =AVEDEV(1,2,3).
- variableAVERAGE_WEIGHTED
-
AVERAGE_WEIGHTED formula. Computes a weighted arithmetic mean. Example: =AVERAGE_WEIGHTED({10,20},{1,3}) returns 17.5.
- variableAVERAGEA
-
AVERAGEA formula. Evaluates the Excel-compatible AVERAGEA statistical function. Example: =AVERAGEA(1,2,3).
- variableAVERAGEIF
-
AVERAGEIF formula. Evaluates the Excel-compatible AVERAGEIF statistical function. Example: =AVERAGEIF(1,2,3).
- variableAVERAGEIFS
-
AVERAGEIFS formula. Evaluates the Excel-compatible AVERAGEIFS statistical function. Example: =AVERAGEIFS(1,2,3).
- variableBETA_DIST
-
BETA.DIST formula. Evaluates the Excel-compatible BETA.DIST statistical function. Example: =BETA.DIST(1,1,TRUE).
- variableBETA_INV
-
BETA.INV formula. Evaluates the Excel-compatible BETA.INV statistical function. Example: =BETA.INV(0.5,1,1).
- variableBINOM_DIST
-
BINOM.DIST formula. Evaluates the Excel-compatible BINOM.DIST statistical function. Example: =BINOM.DIST(1,1,TRUE).
- variableBINOM_DIST_RANGE
-
BINOM.DIST.RANGE formula. Evaluates the Excel-compatible BINOM.DIST.RANGE statistical function. Example: =BINOM.DIST.RANGE(1,1,TRUE).
- variableBINOM_INV
-
BINOM.INV formula. Evaluates the Excel-compatible BINOM.INV statistical function. Example: =BINOM.INV(0.5,1,1).
- variableCHISQ_DIST
-
CHISQ.DIST formula. Evaluates the Excel-compatible CHISQ.DIST statistical function. Example: =CHISQ.DIST(1,1,TRUE).
- variableCHISQ_DIST_RT
-
CHISQ.DIST.RT formula. Evaluates the Excel-compatible CHISQ.DIST.RT statistical function. Example: =CHISQ.DIST.RT(1,1,TRUE).
- variableCHISQ_INV
-
CHISQ.INV formula. Evaluates the Excel-compatible CHISQ.INV statistical function. Example: =CHISQ.INV(0.5,1,1).
- variableCHISQ_INV_RT
-
CHISQ.INV.RT formula. Evaluates the Excel-compatible CHISQ.INV.RT statistical function. Example: =CHISQ.INV.RT(0.5,1,1).
- variableCHISQ_TEST
-
CHISQ.TEST formula. Evaluates the Excel-compatible CHISQ.TEST statistical function. Example: =CHISQ.TEST(1,2,3).
- variableCONFIDENCE_NORM
-
CONFIDENCE.NORM formula. Evaluates the Excel-compatible CONFIDENCE.NORM statistical function. Example: =CONFIDENCE.NORM(1,2,3).
- variableCONFIDENCE_T
-
CONFIDENCE.T formula. Evaluates the Excel-compatible CONFIDENCE.T statistical function. Example: =CONFIDENCE.T(1,2,3).
- variableCORREL
-
CORREL formula. Evaluates the Excel-compatible CORREL statistical function. Example: =CORREL(1,2,3).
- variableCOUNTA
-
COUNTA formula. Evaluates the Excel-compatible COUNTA statistical function. Example: =COUNTA(1,2,3).
- variableCOUNTBLANK
-
COUNTBLANK formula. Evaluates the Excel-compatible COUNTBLANK statistical function. Example: =COUNTBLANK(1,2,3).
- variableCOUNTIF
-
COUNTIF formula. Evaluates the Excel-compatible COUNTIF statistical function. Example: =COUNTIF(1,2,3).
- variableCOUNTIFS
-
COUNTIFS formula. Evaluates the Excel-compatible COUNTIFS statistical function. Example: =COUNTIFS(1,2,3).
- variableCOVARIANCE_P
-
COVARIANCE.P formula. Evaluates the Excel-compatible COVARIANCE.P statistical function. Example: =COVARIANCE.P(1,2,3).
- variableCOVARIANCE_S
-
COVARIANCE.S formula. Evaluates the Excel-compatible COVARIANCE.S statistical function. Example: =COVARIANCE.S(1,2,3).
- variableDEVSQ
-
DEVSQ formula. Evaluates the Excel-compatible DEVSQ statistical function. Example: =DEVSQ(1,2,3).
- variableEXPON_DIST
-
EXPON.DIST formula. Evaluates the Excel-compatible EXPON.DIST statistical function. Example: =EXPON.DIST(1,1,TRUE).
- variableF_DIST
-
F.DIST formula. Evaluates the Excel-compatible F.DIST statistical function. Example: =F.DIST(1,1,TRUE).
- variableF_DIST_RT
-
F.DIST.RT formula. Evaluates the Excel-compatible F.DIST.RT statistical function. Example: =F.DIST.RT(1,1,TRUE).
- variableF_INV
-
F.INV formula. Evaluates the Excel-compatible F.INV statistical function. Example: =F.INV(0.5,1,1).
- variableF_INV_RT
-
F.INV.RT formula. Evaluates the Excel-compatible F.INV.RT statistical function. Example: =F.INV.RT(0.5,1,1).
- variableF_TEST
-
F.TEST formula. Evaluates the Excel-compatible F.TEST statistical function. Example: =F.TEST(1,2,3).
- variableFISHER
-
FISHER formula. Evaluates the Excel-compatible FISHER statistical function. Example: =FISHER(1,2,3).
- variableFISHERINV
-
FISHERINV formula. Evaluates the Excel-compatible FISHERINV statistical function. Example: =FISHERINV(0.5,1,1).
- variableFORECAST
-
FORECAST formula. Evaluates the Excel-compatible FORECAST statistical function. Example: =FORECAST(1,2,3).
- variableFORECAST_ETS
-
FORECAST.ETS formula. Evaluates the Excel-compatible FORECAST.ETS statistical function. Example: =FORECAST.ETS(1,2,3).
- variableFORECAST_ETS_CONFINT
-
FORECAST.ETS.CONFINT formula. Evaluates the Excel-compatible FORECAST.ETS.CONFINT statistical function. Example: =FORECAST.ETS.CONFINT(1,2,3).
- variableFORECAST_ETS_SEASONALITY
-
FORECAST.ETS.SEASONALITY formula. Evaluates the Excel-compatible FORECAST.ETS.SEASONALITY statistical function. Example: =FORECAST.ETS.SEASONALITY(1,2,3).
- variableFORECAST_ETS_STAT
-
FORECAST.ETS.STAT formula. Evaluates the Excel-compatible FORECAST.ETS.STAT statistical function. Example: =FORECAST.ETS.STAT(1,2,3).
- variableFORECAST_LINEAR
-
FORECAST.LINEAR formula. Evaluates the Excel-compatible FORECAST.LINEAR statistical function. Example: =FORECAST.LINEAR(1,2,3).
- variableFREQUENCY
-
FREQUENCY formula. Evaluates the Excel-compatible FREQUENCY statistical function. Example: =FREQUENCY(1,2,3).
- variableGAMMA
-
GAMMA formula. Evaluates the Excel-compatible GAMMA statistical function. Example: =GAMMA(1,2,3).
- variableGAMMA_DIST
-
GAMMA.DIST formula. Evaluates the Excel-compatible GAMMA.DIST statistical function. Example: =GAMMA.DIST(1,1,TRUE).
- variableGAMMA_INV
-
GAMMA.INV formula. Evaluates the Excel-compatible GAMMA.INV statistical function. Example: =GAMMA.INV(0.5,1,1).
- variableGAMMALN
-
GAMMALN formula. Evaluates the Excel-compatible GAMMALN statistical function. Example: =GAMMALN(1,2,3).
- variableGAMMALN_PRECISE
-
GAMMALN.PRECISE formula. Evaluates the Excel-compatible GAMMALN.PRECISE statistical function. Example: =GAMMALN.PRECISE(1,2,3).
- variableGAUSS
-
GAUSS formula. Evaluates the Excel-compatible GAUSS statistical function. Example: =GAUSS(1,2,3).
- variableGEOMEAN
-
GEOMEAN formula. Evaluates the Excel-compatible GEOMEAN statistical function. Example: =GEOMEAN(1,2,3).
- variableGROWTH
-
GROWTH formula. Evaluates the Excel-compatible GROWTH statistical function. Example: =GROWTH(1,2,3).
- variableHARMEAN
-
HARMEAN formula. Evaluates the Excel-compatible HARMEAN statistical function. Example: =HARMEAN(1,2,3).
- variableHYPGEOM_DIST
-
HYPGEOM.DIST formula. Evaluates the Excel-compatible HYPGEOM.DIST statistical function. Example: =HYPGEOM.DIST(1,1,TRUE).
- variableINTERCEPT
-
INTERCEPT formula. Evaluates the Excel-compatible INTERCEPT statistical function. Example: =INTERCEPT(1,2,3).
- variableKURT
-
KURT formula. Evaluates the Excel-compatible KURT statistical function. Example: =KURT(1,2,3).
- variableLARGE
-
LARGE formula. Evaluates the Excel-compatible LARGE statistical function. Example: =LARGE(1,2,3).
- variableLINEST
-
LINEST formula. Evaluates the Excel-compatible LINEST statistical function. Example: =LINEST(1,2,3).
- variableLOGEST
-
LOGEST formula. Evaluates the Excel-compatible LOGEST statistical function. Example: =LOGEST(1,2,3).
- variableLOGNORM_DIST
-
LOGNORM.DIST formula. Evaluates the Excel-compatible LOGNORM.DIST statistical function. Example: =LOGNORM.DIST(1,1,TRUE).
- variableLOGNORM_INV
-
LOGNORM.INV formula. Evaluates the Excel-compatible LOGNORM.INV statistical function. Example: =LOGNORM.INV(0.5,1,1).
- variableMARGINOFERROR
-
MARGINOFERROR formula. Estimates the normal confidence margin for a sample. Example: =MARGINOFERROR(0.95,A1:A10).
- variableMAX
-
MAX formula. Evaluates the Excel-compatible MAX statistical function. Example: =MAX(1,2,3).
- variableMAXA
-
MAXA formula. Evaluates the Excel-compatible MAXA statistical function. Example: =MAXA(1,2,3).
- variableMAXIFS
-
MAXIFS formula. Evaluates the Excel-compatible MAXIFS statistical function. Example: =MAXIFS(1,2,3).
- variableMEDIAN
-
MEDIAN formula. Evaluates the Excel-compatible MEDIAN statistical function. Example: =MEDIAN(1,2,3).
- variableMIN
-
MIN formula. Evaluates the Excel-compatible MIN statistical function. Example: =MIN(1,2,3).
- variableMINA
-
MINA formula. Evaluates the Excel-compatible MINA statistical function. Example: =MINA(1,2,3).
- variableMINIFS
-
MINIFS formula. Evaluates the Excel-compatible MINIFS statistical function. Example: =MINIFS(1,2,3).
- variableMODE_MULT
-
MODE.MULT formula. Evaluates the Excel-compatible MODE.MULT statistical function. Example: =MODE.MULT(1,2,3).
- variableMODE_SNGL
-
MODE.SNGL formula. Evaluates the Excel-compatible MODE.SNGL statistical function. Example: =MODE.SNGL(1,2,3).
- variableNEGBINOM_DIST
-
NEGBINOM.DIST formula. Evaluates the Excel-compatible NEGBINOM.DIST statistical function. Example: =NEGBINOM.DIST(1,1,TRUE).
- variableNORM_DIST
-
NORM.DIST formula. Evaluates the Excel-compatible NORM.DIST statistical function. Example: =NORM.DIST(1,1,TRUE).
- variableNORM_S_DIST
-
NORM.S.DIST formula. Evaluates the Excel-compatible NORM.S.DIST statistical function. Example: =NORM.S.DIST(1,1,TRUE).
- variableNORM_S_INV
-
NORM.S.INV formula. Evaluates the Excel-compatible NORM.S.INV statistical function. Example: =NORM.S.INV(0.5,1,1).
- variableNORMINV
-
NORMINV formula. Evaluates the Excel-compatible NORMINV statistical function. Example: =NORMINV(0.5,1,1).
- variablePEARSON
-
PEARSON formula. Evaluates the Excel-compatible PEARSON statistical function. Example: =PEARSON(1,2,3).
- variablePERCENTILE_EXC
-
PERCENTILE.EXC formula. Evaluates the Excel-compatible PERCENTILE.EXC statistical function. Example: =PERCENTILE.EXC(1,2,3).
- variablePERCENTILE_INC
-
PERCENTILE.INC formula. Evaluates the Excel-compatible PERCENTILE.INC statistical function. Example: =PERCENTILE.INC(1,2,3).
- variablePERCENTRANK_EXC
-
PERCENTRANK.EXC formula. Evaluates the Excel-compatible PERCENTRANK.EXC statistical function. Example: =PERCENTRANK.EXC(1,2,3).
- variablePERCENTRANK_INC
-
PERCENTRANK.INC formula. Evaluates the Excel-compatible PERCENTRANK.INC statistical function. Example: =PERCENTRANK.INC(1,2,3).
- variablePERMUT
-
PERMUT formula. Evaluates the Excel-compatible PERMUT statistical function. Example: =PERMUT(1,2,3).
- variablePERMUTATIONA
-
PERMUTATIONA formula. Evaluates the Excel-compatible PERMUTATIONA statistical function. Example: =PERMUTATIONA(1,2,3).
- variablePHI
-
PHI formula. Evaluates the Excel-compatible PHI statistical function. Example: =PHI(1,2,3).
- variablePOISSON_DIST
-
POISSON.DIST formula. Evaluates the Excel-compatible POISSON.DIST statistical function. Example: =POISSON.DIST(1,1,TRUE).
- variablePROB
-
PROB formula. Evaluates the Excel-compatible PROB statistical function. Example: =PROB(1,2,3).
- variableQUARTILE_EXC
-
QUARTILE.EXC formula. Evaluates the Excel-compatible QUARTILE.EXC statistical function. Example: =QUARTILE.EXC(1,2,3).
- variableQUARTILE_INC
-
QUARTILE.INC formula. Evaluates the Excel-compatible QUARTILE.INC statistical function. Example: =QUARTILE.INC(1,2,3).
- variableRANK_AVG
-
RANK.AVG formula. Evaluates the Excel-compatible RANK.AVG statistical function. Example: =RANK.AVG(1,2,3).
- variableRANK_EQ
-
RANK.EQ formula. Evaluates the Excel-compatible RANK.EQ statistical function. Example: =RANK.EQ(1,2,3).
- variableRSQ
-
RSQ formula. Evaluates the Excel-compatible RSQ statistical function. Example: =RSQ(1,2,3).
- variableSKEW
-
SKEW formula. Evaluates the Excel-compatible SKEW statistical function. Example: =SKEW(1,2,3).
- variableSKEW_P
-
SKEW.P formula. Evaluates the Excel-compatible SKEW.P statistical function. Example: =SKEW.P(1,2,3).
- variableSLOPE
-
SLOPE formula. Evaluates the Excel-compatible SLOPE statistical function. Example: =SLOPE(1,2,3).
- variableSMALL
-
SMALL formula. Evaluates the Excel-compatible SMALL statistical function. Example: =SMALL(1,2,3).
- variableSTANDARDIZE
-
STANDARDIZE formula. Evaluates the Excel-compatible STANDARDIZE statistical function. Example: =STANDARDIZE(1,2,3).
- variableSTDEV_P
-
STDEV.P formula. Evaluates the Excel-compatible STDEV.P statistical function. Example: =STDEV.P(1,2,3).
- variableSTDEV_S
-
STDEV.S formula. Evaluates the Excel-compatible STDEV.S statistical function. Example: =STDEV.S(1,2,3).
- variableSTDEVA
-
STDEVA formula. Evaluates the Excel-compatible STDEVA statistical function. Example: =STDEVA(1,2,3).
- variableSTDEVPA
-
STDEVPA formula. Evaluates the Excel-compatible STDEVPA statistical function. Example: =STDEVPA(1,2,3).
- variableSTEYX
-
STEYX formula. Evaluates the Excel-compatible STEYX statistical function. Example: =STEYX(1,2,3).
- variableT_DIST
-
T.DIST formula. Evaluates the Excel-compatible T.DIST statistical function. Example: =T.DIST(1,1,TRUE).
- variableT_DIST_2T
-
T.DIST.2T formula. Evaluates the Excel-compatible T.DIST.2T statistical function. Example: =T.DIST.2T(1,1,TRUE).
- variableT_DIST_RT
-
T.DIST.RT formula. Evaluates the Excel-compatible T.DIST.RT statistical function. Example: =T.DIST.RT(1,1,TRUE).
- variableT_INV
-
T.INV formula. Evaluates the Excel-compatible T.INV statistical function. Example: =T.INV(0.5,1,1).
- variableT_INV_2T
-
T.INV.2T formula. Evaluates the Excel-compatible T.INV.2T statistical function. Example: =T.INV.2T(0.5,1,1).
- variableT_TEST
-
T.TEST formula. Evaluates the Excel-compatible T.TEST statistical function. Example: =T.TEST(1,2,3).
- variableTREND
-
TREND formula. Evaluates the Excel-compatible TREND statistical function. Example: =TREND(1,2,3).
- variableTRIMMEAN
-
TRIMMEAN formula. Evaluates the Excel-compatible TRIMMEAN statistical function. Example: =TRIMMEAN(1,2,3).
- variableVAR
-
VAR formula. Estimates sample variance for compatibility with legacy Excel workbooks. Example: =VAR(1,2,3).
- variableVAR_P
-
VAR.P formula. Evaluates the Excel-compatible VAR.P statistical function. Example: =VAR.P(1,2,3).
- variableVAR_S
-
VAR.S formula. Evaluates the Excel-compatible VAR.S statistical function. Example: =VAR.S(1,2,3).
- variableVARA
-
VARA formula. Evaluates the Excel-compatible VARA statistical function. Example: =VARA(1,2,3).
- variableVARPA
-
VARPA formula. Evaluates the Excel-compatible VARPA statistical function. Example: =VARPA(1,2,3).
- variableWEIBULL_DIST
-
WEIBULL.DIST formula. Evaluates the Excel-compatible WEIBULL.DIST statistical function. Example: =WEIBULL.DIST(1,1,TRUE).
- variableZ_TEST
-
Z.TEST formula. Evaluates the Excel-compatible Z.TEST statistical function. Example: =Z.TEST(1,2,3).
Text functions
- variableARRAYTOTEXT
-
Converts an array to concise or strict text. Example: ARRAYTOTEXT({1,"A"},1) returns "{1,"A"}".
- variableASC
-
Converts full-width DBCS characters to half-width characters. Example: ASC("ＡＢＣ") returns "ABC".
- variableBAHTTEXT
-
Spells a number as Thai baht and satang. Example: BAHTTEXT(1234) returns Thai amount text.
- variableCHAR
-
Returns the character for a code-page number. Example: CHAR(65) returns "A".
- variableCjkFormulaTextLocale
-
CjkFormulaTextLocale: FormulaTextLocaleExcel-compatible CJK text locale where wide CJK code points count as two bytes.
- variableCLEAN
-
Removes ASCII control characters 0 through 31. Example: CLEAN(CHAR(9)&"A") returns "A".
- variableCODE
-
Returns the code of the first character. Example: CODE("A") returns 65.
- variableCONCAT
-
Joins text and ranges without a delimiter. Example: CONCAT("A",1) returns "A1".
- variableCONCATENATE
-
Legacy text concatenation. Example: CONCATENATE("Hello"," World") returns "Hello World".
- variableDBCS
-
Converts half-width characters to full-width DBCS characters. Example: DBCS("ABC") returns "ＡＢＣ".
- variableDETECTLANGUAGE
-
Reads language detection from the host cache. Example: DETECTLANGUAGE("hello") commonly returns "en".
- variableDOLLAR
-
Formats a number as currency text. Example: DOLLAR(1234.5,2) returns "$1,234.50".
- variableEXACT
-
Compares two strings with case sensitivity. Example: EXACT("A","a") returns FALSE.
- variableFIND
-
Finds case-sensitive text and returns a one-based position. Example: FIND("b","abc") returns 2.
- variableFINDB
-
Finds case-sensitive text using locale-defined DBCS byte positions. Example: FINDB("b", "abc") returns 2.
- variableFIXED
-
Formats rounded fixed-decimal text. Example: FIXED(1234.567,1) returns "1,234.6".
- variableJIS
-
Converts half-width text to Japanese full-width text. Example: JIS("ABC ｶ") returns "ＡＢＣ カ".
- variableLEFT
-
Returns characters from the start of text. Example: LEFT("Sale",2) returns "Sa".
- variableLEFTB
-
Returns leading text limited by a locale-defined DBCS byte count. Example: LEFTB("abc", 2) returns "ab".
- variableLEN
-
Returns the UTF-16 character count used by Excel compatibility mode. Example: LEN("Excel") returns 5.
- variableLENB
-
Returns text length measured in locale-defined DBCS bytes. Example: LENB("abc") returns 3.
- variableLOWER
-
Converts letters to lowercase. Example: LOWER("Excel") returns "excel".
- variableMID
-
Returns a substring by one-based start and length. Example: MID("Fluid Flow",7,4) returns "Flow".
- variableMIDB
-
Returns text from a locale-defined DBCS byte position. Example: MIDB("abc", 2, 1) returns "b".
- variableNUMBERSTRING
-
Converts a nonnegative number to one of the supported Chinese numeral styles.
- variableNUMBERVALUE
-
Parses numeric text with explicit separators. Example: NUMBERVALUE("2.500,27",",",".") returns 2500.27.
- variablePHONETIC
-
Returns stored phonetic metadata for a cell. Example: PHONETIC(A1) returns its furigana when available.
- variablePROPER
-
Capitalizes each word and lowercases remaining letters. Example: PROPER("this is A TITLE") returns "This Is A Title".
- variableREGEXEXTRACT
-
Extracts regex matches or capture groups. Example: REGEXEXTRACT("A12","[0-9]+") returns "12".
- variableREGEXMATCH
-
REGEXMATCH tests whether text matches a regular expression. Example: =REGEXMATCH("A12","[0-9]+") returns TRUE.
- variableREGEXREPLACE
-
Replaces all or one regex match. Example: REGEXREPLACE("A12","[0-9]+","#") returns "A#".
- variableREGEXTEST
-
Tests whether text matches a regular expression. Example: REGEXTEST("A12","[0-9]+") returns TRUE.
- variableREPLACE
-
Replaces characters at a one-based position. Example: REPLACE("abc",2,1,"X") returns "aXc".
- variableREPLACEB
-
Replaces text at a locale-defined DBCS byte position. Example: REPLACEB("abc", 2, 1, "x") returns "axc".
- variableREPT
-
Repeats text a specified number of times. Example: REPT("-",3) returns "---".
- variableRIGHT
-
Returns characters from the end of text. Example: RIGHT("Sale",2) returns "le".
- variableRIGHTB
-
Returns trailing text limited by a locale-defined DBCS byte count. Example: RIGHTB("abc", 2) returns "bc".
- variableSEARCH
-
Finds text case-insensitively with Excel wildcards. Example: SEARCH("b*","abc") returns 2.
- variableSEARCHB
-
Finds case-insensitive text using locale-defined DBCS byte positions. Example: SEARCHB("B", "abc") returns 2.
- variableSingleByteFormulaTextLocale
-
SingleByteFormulaTextLocale: FormulaTextLocaleExcel-compatible text locale where every Unicode code point counts as one byte.
- variableSUBSTITUTE
-
Replaces matching text, optionally only one occurrence. Example: SUBSTITUTE("a-b","-","/") returns "a/b".
- variableT
-
Returns text values and an empty string for non-text values. Example: T("Excel") returns "Excel".
- variableTEXT
-
Formats a value with an Excel format code. Example: TEXT(1234.5,"#,##0.00") returns "1,234.50".
- variableTEXTAFTER
-
Returns text after a delimiter occurrence. Example: TEXTAFTER("red-blue","-") returns "blue".
- variableTEXTBEFORE
-
Returns text before a delimiter occurrence. Example: TEXTBEFORE("red-blue","-") returns "red".
- variabletextFunctions
-
textFunctions: readonly FormulaFunction[] - variableTEXTJOIN
-
Joins values with a delimiter and optional empty-value filtering. Example: TEXTJOIN(",",TRUE,"A","B") returns "A,B".
- variableTEXTSPLIT
-
Splits text into a spill matrix. Example: TEXTSPLIT("A,B",",") returns {"A","B"}.
- variableTRANSLATE
-
Reads a previously loaded translation from the host cache. Example: TRANSLATE("hello", "en", "es") reads the cached result.
- variableTRIM
-
Removes outer ASCII spaces and collapses internal runs. Example: TRIM(" a b ") returns "a b".
- variableUNICHAR
-
Returns the Unicode character for a code point. Example: UNICHAR(9733) returns "★".
- variableUNICODE
-
Returns the Unicode code point of the first character. Example: UNICODE("★") returns 9733.
- variableUPPER
-
Converts letters to uppercase. Example: UPPER("Excel") returns "EXCEL".
- variableVALUE
-
Converts common numeric text to a number. Example: VALUE("$1,234.50") returns 1234.5.
- variableVALUETOTEXT
-
Converts one value to concise or strict text. Example: VALUETOTEXT("A",1) returns ""A"".
- functioncreateFormulaTextLocale
-
createFormulaTextLocale(input: { calculationModeLabel?: (mode: "Automatic" | "Manual"): string; characterCode?: (text: string): number; characterFromCode?: (code: number): string | undefined; charWidth: CharWidth; compareText?: (left: string, right: string): number; locale: string; toFullWidth?: (text: string): string; toHalfWidth?: (text: string): string; … }): FormulaTextLocaleCreates a byte-oriented text locale from a character-width strategy.
- functionformatBahtText
-
formatBahtText(value: number): string | undefinedConverts a finite number to Excel-style Thai baht and satang text.
Trigonometry functions
- variableACOS
-
Returns the arccosine of a number in radians.
Example=ACOS(1) returns 0. - variableACOSH
-
Returns the inverse hyperbolic cosine of a number.
Example=ACOSH(2) returns approximately 1.316957897. - variableACOT
-
Returns the arccotangent of a number in radians.
Example=ACOT(1) returns pi/4. - variableACOTH
-
Returns the inverse hyperbolic cotangent of a number.
Example=ACOTH(2) returns approximately 0.549306144. - variableASIN
-
Returns the arcsine of a number in radians.
Example=ASIN(0) returns 0. - variableASINH
-
Returns the inverse hyperbolic sine of a number.
Example=ASINH(0) returns 0. - variableATAN
-
Returns the arctangent of a number in radians.
Example=ATAN(0) returns 0. - variableATAN2
-
Returns the angle from an X and Y coordinate pair in radians.
Example=ATAN2(1,0) returns 0. - variableATANH
-
Returns the inverse hyperbolic tangent of a number.
Example=ATANH(0) returns 0. - variableCOS
-
Returns the cosine of an angle in radians.
Example=COS(0) returns 1. - variableCOSH
-
Returns the hyperbolic cosine of a number.
Example=COSH(0) returns 1. - variableCOT
-
Returns the cotangent of an angle in radians.
Example=COT(RADIANS(45)) returns 1. - variableCOTH
-
Returns the hyperbolic cotangent of a number.
Example=COTH(1) returns approximately 1.313035285. - variableCSC
-
Returns the cosecant of an angle in radians.
Example=CSC(PI()/2) returns 1. - variableCSCH
-
Returns the hyperbolic cosecant of a number.
Example=CSCH(1) returns approximately 0.850918128. - variableDEGREES
-
Converts an angle from radians to degrees.
Example=DEGREES(PI()) returns 180. - variableRADIANS
-
Converts an angle from degrees to radians.
Example=RADIANS(180) returns pi. - variableSEC
-
Returns the secant of an angle in radians.
Example=SEC(0) returns 1. - variableSECH
-
Returns the hyperbolic secant of a number.
Example=SECH(0) returns 1. - variableSIN
-
Returns the sine of an angle in radians.
Example=SIN(0) returns 0. - variableSINH
-
Returns the hyperbolic sine of a number.
Example=SINH(0) returns 0. - variableTAN
-
Returns the tangent of an angle in radians.
Example=TAN(0) returns 0. - variableTANH
-
Returns the hyperbolic tangent of a number.
Example=TANH(0) returns 0.
Web service functions
- variableENCODEURL
-
Encodes text as an RFC 3986 UTF-8 URL component. Example: ENCODEURL("a b") returns "a%20b".
- variableFILTERXML
-
Extracts XPath matches from XML as a spilling column. Example: FILTERXML("<r><v>A</v></r>","//v") returns A.
- variablewebFunctions
-
webFunctions: readonly FormulaFunction[] - variableWEBSERVICE
-
Reads response text from a host-loaded HTTP cache. Example: WEBSERVICE("https://example.com/data.xml") returns cached XML.
No matching API entries.