Function reference

Every built-in formula function, with signatures, semantics, and worked examples.

Every function built into the DreamSheets formula engine. Optional arguments are shown in square brackets. All function names are case-insensitive. For syntax, references, operators, and error handling, see the formula language guide.

Aggregates

SUM

SUM(value, …)

Adds all numeric arguments. Arguments can be columns, single values, or a mix.

SUM(Amount)                    → total of the current table's Amount column
SUM(Sales.Amount)              → total of another table's column
SUM(Q1Total, Q2Total, 500)     → tiles and literals mix freely

AVERAGE

AVERAGE(value, …)

Mean of the numeric arguments. Blank cells are ignored (they don't drag the average down as zeros).

AVERAGE(Sales.Amount)

COUNT

COUNT(value, …)

Counts how many arguments are numbers. Like Excel, it never coerces — text that looks like a number is not counted. To count non-blank rows of a text column, use COUNTIF(Column, "<>").

COUNT(Sales.Amount)            → rows with a numeric Amount

MIN / MAX

MIN(value, …)
MAX(value, …)

Smallest / largest numeric argument.

MAX(Sales.Amount) - MIN(Sales.Amount)   → the range of the data

Conditional aggregation

Criteria are Excel-style strings: a bare value matches equality ("East"), and a leading comparator compares (">10", ">=0.5", "<>0"). Text matching is case-insensitive.

Wildcards work in text criteria, as in Excel: * matches any run of characters and ? exactly one. So "*east*" means contains "east", "north*" starts with "north", "?ay" matches Bay, Day and May, and "<>*test*" means doesn't contain "test". Put ~ in front of a *, ? or ~ to match that character itself ("Why~?"). Wildcards match only text cells ("1*" won't match the number 10, but will match the text "10"), and they don't apply after >, <, >= or <=.

Or write the condition itself. Anywhere a column/criterion pair goes, a comparison over a column works instead — no quoting, and the editor completes the column names:

SUMIF(Amount, Units > 10)                    → same as SUMIF(Units, ">10", Amount)
COUNTIF(Status <> "Closed")                  → open rows
SUMIFS(Amount, Region = "East", Units > 10)  → conditions and pairs mix freely
@Amount / SUMIF(Amount, Rank > 0)            → each row's share of the ranked total

Write the column bare (Units > 10), never @Units > 10: @Units is only this row's cell, so the condition collapses to a single TRUE/FALSE (Excel's meaning, and almost never the one you want — the editor warns). Blanks and text never match a comparison like > 10, just as with ">10". The condition form doesn't exist in Excel, so a column using it exports to .xlsx as values.

Values are read the same way SUM reads them: a text cell that parses as a number counts (a text-typed column full of digits is a common import result), and anything else is skipped.

Coming from Excel: these take whole columns, not A1 ranges. Where Excel writes SUMIF(A2:A100, "East", C2:C100), DreamSheets writes SUMIF(Region, "East", Amount) — or SUMIF(Sales.Region, "East", Sales.Amount) from outside the table. There are no row-bounded ranges: A2:A4 is a syntax error, and a column always means all of its rows.

COUNTIF

COUNTIF(column, criterion)

Counts cells in column matching a criterion.

COUNTIF(Region, "East")        → rows in the East region
COUNTIF(Amount, ">1000")       → rows with Amount over 1000
COUNTIF(Notes, "*refund*")     → rows whose Notes mention a refund

COUNTIFS

COUNTIFS(column1, criterion1, …)

Counts rows matching every column/criterion pair (they're ANDed).

COUNTIFS(Region, "East", Amount, ">1000")

SUMIF

SUMIF(column, criterion, [sum_column])

Sums sum_column on the rows where column matches the criterion. Both must be columns of the same table (equal length). With two arguments, sums the matching cells of column itself.

SUMIF(Region, "East", Amount)              → East revenue
SUMIF(Amount, ">0")                        → sum of only the positive amounts
SUMIF(Sales.Region, "East", Sales.Amount)  → same, from another table

SUMIFS

SUMIFS(sum_column, column1, criterion1, …)

Sums sum_column over rows matching every pair. Note the argument order: unlike SUMIF, the sum column comes first (as in Excel).

SUMIFS(Amount, Region, "East", Units, ">10")
SUMIFS(Revenue, Year, "2026", Product, "Pro")

This is the tool for building small report tables by hand: a calc column of SUMIFS(Sales.Amount, Sales.Region, @Region) on a Regions table gives you per-region totals that update live.

AVERAGEIF / AVERAGEIFS

AVERAGEIF(range, criterion, [average_range])
AVERAGEIFS(average_range, range1, criterion1, …)

Mean of the matching rows, with the same criterion syntax as SUMIF. Blank and non-numeric cells in the averaged column are skipped rather than counted as zeros, so the divisor is the number of numeric matches. No numeric match at all is #DIV/0!, not 0.

As with SUMIFS, the plural form takes the value range first.

AVERAGEIF(Region, "East", Amount)            → mean East order
AVERAGEIFS(Amount, Region, "East", Units, ">10")

MINIFS / MAXIFS

MINIFS(min_range, range1, criterion1, …)
MAXIFS(max_range, range1, criterion1, …)

Smallest / largest value over the rows matching every criterion. Like Excel, these return 0 when nothing matches — guard with COUNTIFS if you need to tell "no rows" from a real zero.

MAXIFS(Amount, Region, "East")               → biggest East order

SUMPRODUCT

SUMPRODUCT(array1, array2, …)

Multiplies equal-length columns element-wise and sums the products — a weighted sum or dot product in one call.

SUMPRODUCT(Qty, Price)                     → order total without a helper column
SUMPRODUCT(Weights, Scores) / SUM(Weights) → weighted average

Only the plain multi-column form is supported. Excel's array-arithmetic idiom SUMPRODUCT((Region="East") * Amount) is not — write SUMIFS(Amount, Region, "East") instead.

Lookup

LOOKUP

LOOKUP(value, lookup_col, return_col, [default])

Finds value in lookup_col and returns the matching entry of return_col. With no match it returns default if given, else #N/A. This is DreamSheets' replacement for VLOOKUP/XLOOKUP: it names the columns directly, so it can't break when columns move.

LOOKUP(@SKU, Products.SKU, Products.Price)          → price lookup in a calc column
LOOKUP(@State, TaxTable.State, TaxTable.Rate, 0)    → 0 when the state isn't listed

The classic pattern — enriching an Orders table from a Products table — is one calc column:

@Qty * LOOKUP(@SKU, Products.SKU, Products.UnitPrice)

Lookups over large tables are automatically indexed, so a 100,000-row lookup column stays fast.

Logic

IF

IF(condition, then, else)

Returns then when the condition is true, otherwise else. Only the taken branch is evaluated, so the untaken branch can safely contain an error.

IF(@Amount > 1000, "large", "small")
IF(@Units = 0, 0, @Revenue / @Units)

IFS

IFS(condition1, value1, condition2, value2, …)

Returns the value paired with the first true condition — a flat replacement for nested IFs. If no condition is true the result is #N/A, so end with TRUE and a fallback to give it a default. Like IF, only the chosen value is evaluated.

IFS(@Score >= 90, "A", @Score >= 80, "B", @Score >= 70, "C", TRUE, "F")

SWITCH

SWITCH(expression, value1, result1, value2, result2, …, [default])

Compares expression with each value in turn and returns the result paired with the first one that's equal. With no match it returns default, or #N/A if you left it out. Values compare exactly like =, so text matching is case-sensitive.

SWITCH(@Status, "O", "Open", "C", "Closed", "Unknown")
SWITCH(WEEKDAY(@Date), 1, "Weekend", 7, "Weekend", "Weekday")

IFERROR

IFERROR(value, fallback)

Returns fallback if value evaluates to any error, else value.

IFERROR(@Revenue / @Units, 0)
IFERROR(LOOKUP(@ID, Ref.ID, Ref.Name), "unknown")

AND / OR / NOT

AND(condition, …)
OR(condition, …)
NOT(condition)

Standard boolean logic. Strict like Excel: text arguments are a #TYPE! error; numbers count as true when non-zero.

IF(AND(@Amount > 0, @Region = "East"), @Amount, 0)

ISERROR / ISNA / ISBLANK

ISERROR(value)
ISNA(value)
ISBLANK(value)

ISERROR is true for any error; ISNA only for #N/A (useful for telling "lookup missed" apart from "lookup broke"); ISBLANK is true for an empty cell.

IF(ISBLANK(@Cost), 0, @Price / @Cost)
IF(ISNA(LOOKUP(@SKU, Products.SKU, Products.Price)), "NEW SKU", "ok")

ISNUMBER / ISTEXT

ISNUMBER(value)
ISTEXT(value)

ISNUMBER is true when the value is a number — dates count, since a date is a serial number. Text that merely looks like a number ("12") is text, so it's ISTEXT that's true for it. Neither passes an error through: an error is simply not a number.

That last part makes ISNUMBER the standard way to ask whether text contains something, because SEARCH returns an error when it finds nothing:

ISNUMBER(SEARCH("refund", @Notes))                   → TRUE when Notes mentions a refund
IF(ISNUMBER(SEARCH("urgent", @Subject)), "!", "")

Math

ROUND / ROUNDUP / ROUNDDOWN

ROUND(number, digits)
ROUNDUP(number, digits)
ROUNDDOWN(number, digits)

ROUND is standard rounding; ROUNDUP rounds away from zero and ROUNDDOWN toward zero (Excel's definitions).

ROUND(@Price * 1.08875, 2)     → tax-inclusive price to the cent
ROUNDUP(@Items / 12, 0)        → number of dozen-boxes needed

ABS / SQRT

ABS(number)
SQRT(number)

Absolute value; square root (SQRT of a negative number is an error).

MOD

MOD(number, divisor)

Remainder after division. The sign follows the divisor, as in Excel: MOD(-3, 2) is 1. This makes cyclic patterns work with negative inputs:

MOD(ROW() - 1, 7) + 1          → repeating 1–7 (day-of-cycle) counter

INT

INT(number)

Rounds down to the nearest integer — INT(-2.5) is -3 (a floor, not truncation). Use ROUNDDOWN(n, 0) to truncate toward zero.

LN / LOG / LOG10 / EXP

LN(number)
LOG(number, [base])
LOG10(number)
EXP(number)

Natural log, log to any base (default 10), base-10 log, and e raised to a power. LN and EXP are inverses. Zero or a negative number is an error, not a silent NaN — the same rule as SQRT.

LN(@Income)                    → log-transform a skewed variable
EXP(@Coefficient)              → back to the original scale
LOG(@Cells, 2)                 → doublings

POWER

POWER(number, power)

Identical to the ^ operator, including its error rules (0 to a negative power is #DIV/0!; an overflowing or complex result is #EVAL!). Use whichever reads better.

SUMSQ / SIGN / PI

SUMSQ(value, …)
SIGN(number)
PI()

Sum of squares; -1/0/1 by sign; the constant π.

TRUNC / CEILING / FLOOR

TRUNC(number, [digits])
CEILING(number, [significance])
FLOOR(number, [significance])

TRUNC chops toward zero at digits decimals — TRUNC(-2.5) is -2, where INT(-2.5) is -3. CEILING and FLOOR round away from and toward zero to a multiple of significance (default 1), following Excel's sign rules: a positive number with a negative significance is an error.

CEILING(@Price, 0.05)          → round up to the nearest 5c
FLOOR(@Minutes, 15)            → down to the quarter hour

FACT / GAMMALN / COMBIN / PERMUT

FACT(number)
GAMMALN(x)
COMBIN(n, k)
PERMUT(n, k)

Factorial (of the whole part; above 170 it overflows and errors), the natural log of the gamma function (x > 0), and the counts of combinations (order ignored) and permutations (order matters). COMBIN is built multiplicatively, so COMBIN(52, 5) is exact where n!/(k!(n−k)!) would overflow.

COMBIN(52, 5)                  → 2,598,960 poker hands
GAMMALN(@N + 1)                → LN(FACT(@N)) without the overflow

Statistics

Excel's names and Excel's arithmetic throughout, so a column here and a column in a spreadsheet (or a vector in R) agree digit for digit. Two rules worth knowing before you pick a function:

• Sample vs population. The .S forms divide by n−1, the .P forms by n. STDEV.S of a single value is #DIV/0!, not 0 — one observation carries no information about spread.
• `.INC` vs `.EXC`. The inclusive forms interpolate at k(n−1) and accept any k from 0 to 1; the exclusive forms interpolate at k(n+1) and only accept k between 1/(n+1) and n/(n+1). Match whichever your source used.

Text and blank cells inside a range are skipped by every function here, so a column with a stray label still computes.

STDEV.S / STDEV.P / VAR.S / VAR.P

STDEV.S(value, …)     VAR.S(value, …)
STDEV.P(value, …)     VAR.P(value, …)

Standard deviation and variance, sample (n−1) and population (n). Computed with Welford's algorithm rather than the textbook Σx² − (Σx)²/n, which loses accuracy when the mean is large next to the spread — a column of timestamps or 8-figure revenues gets the right answer.

STDEV.S(Sales.Amount)
STDEV.S(Amount) / SQRT(COUNT(Amount))    → standard error of the mean

MEDIAN / MODE.SNGL

MEDIAN(value, …)
MODE.SNGL(value, …)

Middle value (the mean of the middle two when the count is even), and the most frequent value. MODE.SNGL breaks ties toward whichever value appears first and returns #N/A when nothing repeats.

COUNTA / COUNTBLANK

COUNTA(value, …)
COUNTBLANK(value, …)

COUNTA counts every non-blank value — text and booleans included, unlike COUNT, which counts numbers only. COUNTBLANK counts the blanks (empty text counts as blank). COUNTA is also the one aggregate that counts error cells instead of failing on them, so it stays useful as a "how many rows have data" check.

PERCENTILE.INC / PERCENTILE.EXC

PERCENTILE.INC(array, k)
PERCENTILE.EXC(array, k)

The value at fraction k of the sorted data, interpolated. PERCENTILE.INC matches R's default quantile() (type 7).

PERCENTILE.INC(Sales.Amount, 0.9)     → the 90th percentile

QUARTILE.INC / QUARTILE.EXC

QUARTILE.INC(array, quart)
QUARTILE.EXC(array, quart)

quart 0–4 (.INC) or 1–3 (.EXC), and exactly equal to the matching PERCENTILE call at quart/4.

QUARTILE.INC(Amount, 3) - QUARTILE.INC(Amount, 1)   → the interquartile range

RANK.EQ / RANK.AVG / PERCENTRANK

RANK.EQ(number, ref, [order])
RANK.AVG(number, ref, [order])
PERCENTRANK(array, x, [significance])

Rank within a column, largest first unless order is non-zero. Tied values all take the best rank under RANK.EQ and share the average of the ranks they span under RANK.AVG. A number that isn't in ref is #N/A.

PERCENTRANK gives the standing as a fraction from 0 to 1, interpolating between neighbours and truncating (not rounding) to significance decimals, default 3.

RANK.EQ(@Score, Score)               → 1 for the top score
PERCENTRANK(Score, @Score)           → the same thing as a percentile

LARGE / SMALL

LARGE(array, k)
SMALL(array, k)

The k-th largest / smallest value; k = 1 is MAX / MIN. k outside 1…n is an error.

SUM(LARGE(Amount, 1), LARGE(Amount, 2), LARGE(Amount, 3))   → top three

SKEW / KURT

SKEW(value, …)
KURT(value, …)

Sample skewness (which tail is longer) and sample excess kurtosis (how heavy the tails are) — excess, so a normal sample sits near 0 rather than 3. SKEW needs 3 values, KURT needs 4, and both need some variation in the data.

AVEDEV / DEVSQ

AVEDEV(value, …)
DEVSQ(value, …)

Mean absolute deviation from the mean, and the sum of squared deviations from it.

TRIMMEAN / GEOMEAN / HARMEAN

TRIMMEAN(array, percent)
GEOMEAN(value, …)
HARMEAN(value, …)

TRIMMEAN drops percent of the values before averaging, split between the two tails (the number dropped rounds down to a multiple of 2, so the trim stays symmetric). GEOMEAN is the mean for growth rates and ratios, HARMEAN the mean for rates; both need every value to be positive.

TRIMMEAN(ResponseTime, 0.1)     → mean without the top and bottom 5%
GEOMEAN(GrowthFactor)           → average compound growth

STANDARDIZE

STANDARDIZE(x, mean, stddev)

The z-score — how many standard deviations x sits from the mean. stddev must be positive.

STANDARDIZE(@Score, AVERAGE(Score), STDEV.S(Score))   → standardized column

NORM.DIST / NORM.INV

NORM.DIST(x, mean, stddev, cumulative)          also: NORMALDISTRIBUTION(…)
NORM.INV(probability, mean, stddev)             also: INVERSENORM(…)

Normal-distribution density or cumulative probability, and its inverse. Excel's dotted names and the spelled-out DreamSheets names are the same functions — use either; older documents hold the long ones. cumulative = TRUE gives the probability a value is ≤ x; FALSE gives the density. NORM.INV needs 0 < probability < 1.

NORMALDISTRIBUTION(@Score, 100, 15, TRUE)      → percentile of a score in a normal(100, 15) population
INVERSENORM(0.95, @Mean, @StdDev)              → the 95th-percentile threshold
INVERSENORM(RANDOM(0, 1, 6, 1), @Mean, @StdDev)  → sample a random value from the distribution

T.DIST / T.INV / CONFIDENCE.T / CONFIDENCE.NORM

T.DIST(x, deg_freedom, cumulative)
T.INV(probability, deg_freedom)
CONFIDENCE.T(alpha, standard_dev, size)
CONFIDENCE.NORM(alpha, standard_dev, size)

Student's t distribution and the confidence-interval half-width built on it. CONFIDENCE.T is the one to reach for: it returns half the width of the (1 − alpha) interval for a mean, so you write the interval as mean ± CONFIDENCE.T(…). Use CONFIDENCE.NORM only when the population standard deviation is genuinely known rather than estimated from the data — it is narrower, and narrower is wrong when you estimated.

AVERAGE(Trial.Score) - CONFIDENCE.T(0.05, STDEV.S(Trial.Score), COUNT(Trial.Score))   → lower bound of the 95% CI
AVERAGE(Trial.Score) + CONFIDENCE.T(0.05, STDEV.S(Trial.Score), COUNT(Trial.Score))   → upper bound
T.INV(0.975, COUNT(Trial.Score) - 1)          → the critical t for a two-sided 95% interval
2 * (1 - T.DIST(ABS(@t), @df, TRUE))          → two-tailed p-value from a t statistic

Excel's T.INV.2T, T.DIST.2T and T.DIST.RT are not available — a function name may carry only one dot here. T.INV(1 − alpha/2, df) is exactly T.INV.2T(alpha, df), and the other two follow from T.DIST the same way. A workbook you import that uses them keeps the values Excel last calculated.

SLOPE / INTERCEPT / RSQ

SLOPE(known_ys, known_xs)
INTERCEPT(known_ys, known_xs)
RSQ(known_ys, known_xs)

The least-squares line through the paired points: its slope, its y-intercept, and its R² (squared Pearson correlation). The two ranges must be the same length; a pair is skipped when either side isn't numeric. This is the same fit a chart's linear trendline draws, so a tile computing SLOPE(...) always agrees with the line on the chart.

SLOPE(Sales.Revenue, Sales.Month)       → revenue growth per month
RSQ(Sales.Revenue, Sales.Month)         → how linear the trend is (0–1)

STEYX

STEYX(known_ys, known_xs)

Standard error of the predicted y — how far the points sit from the fitted line, on n − 2 degrees of freedom. Needs at least three pairs, since two points fit a line exactly.

STEYX(Sales.Revenue, Sales.Month)       → typical miss of the fitted line, in revenue units

Careful: a prediction interval around FORECAST(x, …) widens the further x sits from the mean of the known xs, and STEYX carries no x. Using it alone gives the interval at the centre and is too narrow everywhere else. For a correct interval, run Tools → Statistics → Linear regression.

CORREL

CORREL(array1, array2)

Pearson correlation coefficient between two ranges, −1 to 1. #DIV/0! when either range has zero variance.

FORECAST / FORECAST.LINEAR

FORECAST(x, known_ys, known_xs)

Predicts a y value at x from the linear fit of the known points. The two spellings are the same function (Excel's too).

FORECAST(2027, Revenue, Year)           → next year's revenue on the current trend

TREND

TREND(known_ys, [known_xs], [new_xs])

Fitted values from the linear regression. Omit known_xs to use 1, 2, 3, …; new_xs picks where to evaluate (a scalar gives a scalar, a range gives an array; omitted, it evaluates at known_xs). Simple regression only — Excel's multi-column known_xs form isn't supported. For multiple regression — several predictors, categories, diagnostics — use Tools → Statistics.

TRENDEQUATION

TRENDEQUATION(known_ys, known_xs, type, [order])

The fitted trendline equation as text, e.g. y = 2.31x - 4.5 — DreamSheets-only, made for tiles that display a chart's trend. type is "linear", "exp", "log", "power" or "poly" (with order 2–6). The text matches the chart's equation label character-for-character, and Python scripts can read it through sheet.tiles.

TRENDEQUATION(Sales.Revenue, Sales.Month, "linear")   → "y = 1523.4x + 8021"

Text

CONCAT

CONCAT(value, …)

Joins all arguments into one string. The & operator does the same inline: @First & " " & @Last.

TEXTJOIN

TEXTJOIN(delimiter, ignore_empty, value, …)

Joins values with delimiter between them. A value can be a whole column, which makes this the way to list a column in one cell or tile. With ignore_empty set to TRUE, blank cells are skipped; with FALSE they leave empty slots (a,,c).

TEXTJOIN(", ", TRUE, Team.Name)            → "Ana, Ben, Chris"
TEXTJOIN(" ", TRUE, @First, @Middle, @Last) → no double space when Middle is blank

LEN / TRIM / UPPER / LOWER

LEN(text)      → character count
TRIM(text)     → strips leading/trailing whitespace
UPPER(text)    → UPPERCASE
LOWER(text)    → lowercase

HYPERLINK

HYPERLINK(url, [text])

Makes the cell (or tile) a clickable link. text is what's displayed; leave it out and the url is shown instead.

HYPERLINK("https://support.example/42", "Ticket 42")
HYPERLINK("https://tickets.example/" & @Id, @Title)   → a whole column of links

The cell's value is the display text, not the url — so a lookup, a sort, a COUNTIF or an Excel export all see the plain text a user would have typed. Only https://, http://, mailto: and tel: links open; anything else renders as ordinary text.

You rarely need to type this: right-click a cell → Insert link… (or the 🔗 button in a tile's editor) writes it for you. And a cell whose value simply is a URL or an email address becomes a link on its own, with nothing stored at all.

TEXT

TEXT(value, format_text)

Formats a number or date as text using an Excel format code — the way to control how a number looks once it's joined into a sentence (& alone shows the raw number).

"Revenue: " & TEXT(SUM(Sales.Amount), "$#,##0")    → "Revenue: $1,234,567"
TEXT(SUM(Sales.Amount), "$#,##0.0,,\"M\"")          → "$1.2M"
TEXT(@Margin, "0.0%")                              → "25.6%"
TEXT(@Date, "mmm d, yyyy")                         → "Mar 5, 2026"
TEXT(@Delta, "+0;-0;0")                            → "+12" / "-3" / "0"

In a tile you rarely type this: each {…} value in a sentence like Total: {SUM(Sales.Amount)} already takes the tile's Number format, and right-click a value → Format value… gives just that one its own format by writing the TEXT(…) for you (Use tile format removes it).

Inside a formula a " in the code is written \". Scientific (0.00E+00), fraction (# ?/?), elapsed-time ([h]) and conditional ([>100]) codes aren't supported and give #TYPE!.

LEFT / RIGHT / MID

LEFT(text, [num_chars])
RIGHT(text, [num_chars])
MID(text, start_num, num_chars)

First / last / middle characters (num_chars defaults to 1; start_num is 1-based).

LEFT(@SKU, 3)                  → the category prefix of "ABC-1042"
MID(@SKU, 5, 4)                → the numeric part
UPPER(LEFT(@Name, 1)) & MID(@Name, 2, LEN(@Name))   → capitalize a name

SUBSTITUTE

SUBSTITUTE(text, old, new, [instance])

Replaces old with new (case-sensitive). instance replaces only that occurrence.

SUBSTITUTE(@Phone, "-", "")    → strip dashes
SUBSTITUTE(@Code, "/", "-", 1) → replace only the first slash

FIND / SEARCH

FIND(find_text, within_text, [start_num])
SEARCH(find_text, within_text, [start_num])

Return the position (1-based) where find_text first appears in within_text, starting the search at character start_num (default 1). When it isn't there, the result is an error, which ISNUMBER or IFERROR turns into a yes/no or a default.

The difference between them is the same as in Excel:

• FIND is case-sensitive and matches find_text literally.
• SEARCH ignores case and reads wildcards: * for any run of characters, ? for any one character, and ~ in front of either to match it literally.

FIND("-", @SKU)                              → 4 for "ABC-1042"
LEFT(@Email, FIND("@", @Email) - 1)          → the part before the @
MID(@Email, FIND("@", @Email) + 1, 100)      → the domain
ISNUMBER(SEARCH("east", @Region))            → does Region contain "east", any case?

The position counts characters, so it lines up with LEFT, MID and RIGHT. To count the rows that contain some text, use a wildcard criterion instead: COUNTIF(Region, "*east*").

VALUE

VALUE(text)

Converts number-like text to a number: "1,234", "$5.00", "12%". You rarely need it — arithmetic already does this conversion (@Amount * 1), and a text column holding only numbers becomes a number column on its own — so it mostly turns up in imported Excel workbooks. Text that isn't a number, including date-like text such as "3/5/2026", is an error.

Dates

A date is a serial number (days since 1970-01-01) with time as the fractional part — see dates in the language guide. Plain arithmetic works: @Date + 30, @End - @Start.

TODAY / NOW

TODAY()    → today's date
NOW()      → current date and time

A calculated column whose formula is rooted in one of these auto-formats as a date (or date & time).

TODAY() - @InvoiceDate         → age of each invoice in days

DATE

DATE(year, month, day)

Builds a date. Out-of-range parts roll over like Excel: DATE(2026, 13, 1) is January 2027.

YEAR / MONTH / DAY / HOUR / MINUTE / SECOND

YEAR(serial)   MONTH(serial)   DAY(serial)
HOUR(serial)   MINUTE(serial)  SECOND(serial)

Extract calendar/time parts as numbers (MONTH is 1–12, HOUR 0–23).

WEEKDAY

WEEKDAY(serial, [type])

Day of week. type 1 = Sunday is 1 (default), 2 = Monday is 1, 3 = Monday is 0.

IF(WEEKDAY(@Date, 2) > 5, "weekend", "weekday")

EDATE / EOMONTH

EDATE(serial, months)
EOMONTH(serial, months)

EDATE shifts a date by whole months (clamping Jan 31 + 1 month to Feb 28/29); EOMONTH gives the last day of the month that many months away.

EDATE(@Start, 12)              → one year later, same day
EOMONTH(TODAY(), 0)            → end of the current month
EOMONTH(@Invoice, 1)           → "net: end of next month" due date

DATEDIF

DATEDIF(start, end, unit)

Whole-unit difference between two dates; unit is "d", "m", or "y".

DATEDIF(@Hired, TODAY(), "y")  → completed years of service

Financial

These follow Excel's conventions exactly, including the two that surprise everyone:

NPV / XNPV

NPV(rate, value, …)
XNPV(rate, values, dates)

Net present value of periodic cash flows; XNPV for cash flows on specific dates (discounted ACT/365 from the first date).

-50000 + NPV(0.08, Project.CashFlow)
XNPV(0.08, Deals.Amount, Deals.CloseDate)

IRR / XIRR

IRR(values, [guess])
XIRR(values, dates, [guess])

Internal rate of return — the rate at which NPV is zero. Needs at least one positive and one negative flow; returns #EVAL! if it can't converge (pass a guess near the expected rate to help). XIRR handles irregular dates — the right tool for real investment ledgers:

IRR(Project.CashFlow)
XIRR(Ledger.Amount, Ledger.Date)   → money-weighted return of an account

PV / FV

PV(rate, nper, pmt, [fv], [type])
FV(rate, nper, pmt, [pv], [type])

Present / future value of an annuity. type is 0 (payments at period end, default) or 1 (period start).

FV(0.07 / 12, 30 * 12, -500)   → saving $500/month for 30 years at 7%
PV(0.05, 20, -12000)           → value today of $12k/year for 20 years

PMT

PMT(rate, nper, pv, [fv], [type])

Payment per period. The mortgage classic:

-PMT(0.065 / 12, 360, 400000)  → monthly payment on a $400k, 30-year, 6.5% loan

Note the per-period rate (0.065 / 12) and period count (360 months) — mixing annual and monthly is the most common modelling error.

NPER / RATE

NPER(rate, pmt, pv, [fv], [type])
RATE(nper, pmt, pv, [fv], [type], [guess])

NPER: how many periods to pay off (or grow to) a balance. RATE: the implied per-period interest rate, solved iteratively.

NPER(0.06 / 12, -800, 25000)   → months to pay off a $25k loan at $800/month
RATE(48, -350, 14000) * 12     → implied annual rate of a car loan

Row context

These only make sense inside a calculated column, where each row is evaluated in turn.

PREVROW

PREVROW(Column, [default])

The value of Column one row up — the roll-forward primitive. On the first row it returns default (or blank, which is 0 in arithmetic). This is how you build running totals and balances without positional tricks:

PREVROW(Balance, 1000) + @Deposit - @Withdrawal   → a running balance starting at 1000
PREVROW(Total, 0) + @Amount                       → a running total
PREVROW(End, 100) * 1.05                          → 5% compounding growth per row

ROW

ROW()

The current row's 1-based number within its table. Unlike Excel's ROW(), it means the table row, not a worksheet position.

ROW()                          → a row-number column
MOD(ROW() - 1, 4) + 1          → quarters cycling 1,2,3,4

RANDOM

RANDOM(min, max, [decimals], [seed])

A stable random value in [min, max], rounded to decimals (default 0 = integers). Unlike Excel's volatile RAND(), each cell's value is assigned once — it doesn't reshuffle when you edit elsewhere or reload. Two columns with identical arguments produce identical values; pass a different seed to get an independent second column.

RANDOM(1, 100)                 → integers 1–100
RANDOM(0, 1, 4)                → 4-decimal fractions
RANDOM(1, 6) + RANDOM(1, 6, 0, 2)  → two independent dice

Table access

Positional access for the cases where names aren't enough (building views over ranked data, self-referencing tables).

CELLBYINDEX

CELLBYINDEX(Table, Row, Column)

Reads the cell at 1-based row and column indices, both computable at runtime. Table[Row, Column] is shorthand for the same thing.

CELLBYINDEX(Leaderboard, 1, 2) → the top row's second column
Sales[ROW(), 3]                → this row's third column of Sales

COLUMNBYINDEX

COLUMNBYINDEX(Table, Column)

In a calculated column: reads the current row at a 1-based column index.

ROWBYINDEX

ROWBYINDEX(Table, Row)

In a calculated column: reads the current column at a 1-based row index.

ROWBYINDEX(Sales, 1)           → compare every row against the first row
@Amount / ROWBYINDEX(Sales, 1) → each row as a multiple of the first

Custom and plugin functions

Beyond the built-ins, formulas can call custom functions you define in the document (see the language guide) and functions registered by plugins (see Building a plugin). Built-in names always win: neither can shadow a function on this page.