A Sheet formula starts with = and can use any of the functions below. They work as they do in Google Sheets and Excel: the same names, the same arguments, the same results. Arguments in [brackets] are optional, and … means you can give as many as you like.
Typing a function's name in a cell lists the matching functions; inside its brackets a hint shows its arguments, with the one you are on in bold.
How Formulas Read
- A range is two corners with a colon:
A1:B10. A whole column isA:A, a whole row3:3. $keeps a reference fixed when it is copied or filled:$A$1never moves,A$1keeps its row,$A1its column. F4 cycles them while you write.- Another sheet on the tab is named before an
!, in quotes when its name has spaces:='Q3 Budget'!B4. - A function that returns several values (
SORT,FILTER,UNIQUE,SEQUENCE) spills into the cells below and to the right. If something is in the way, it shows#SPILL!. - Errors tell you what went wrong:
#DIV/0!divides by zero,#N/Afound nothing,#NAME?is a function name it doesn't know,#REF!points at a cell that was deleted,#VALUE!got the wrong kind of value,#NUM!is a number out of range (or a range too large to read),#ERROR!is a formula it can't read. Hover the cell to see why.
Plan Card Functions
The Plan Cards functions read the document's cards, so a sheet can count and add up the work on your boards and stay current as cards move. =CARDCOUNT("State", "Done") counts the cards in Done; =CARDSUM("Estimate", "State", "In Progress") adds up the estimates of the cards in progress. Fields and values are named as your boards show them.
Maths
| Function | Does | Example |
|---|---|---|
ABS(value) | The value without its sign | =ABS(-4) |
CEILING(value, [factor]) | Rounds up to a multiple of the factor | =CEILING(23, 5) |
EXP(value) | e raised to a power | =EXP(1) |
FACT(value) | The factorial of a number | =FACT(5) |
FLOOR(value, [factor]) | Rounds down to a multiple of the factor | =FLOOR(23, 5) |
GCD(value1, [value2, …]) | The greatest common divisor | =GCD(12, 18) |
INT(value) | Rounds down to a whole number | =INT(7.8) |
LCM(value1, [value2, …]) | The least common multiple | =LCM(4, 6) |
LN(value) | The natural logarithm | =LN(10) |
LOG(value, [base]) | The logarithm in a base (10 by default) | =LOG(8, 2) |
LOG10(value) | The base-10 logarithm | =LOG10(1000) |
MOD(dividend, divisor) | The remainder after division, with the sign of the divisor | =MOD(10, 3) |
MROUND(value, factor) | Rounds to the nearest multiple of the factor | =MROUND(17, 5) |
PI() | The number π | =PI() |
POWER(base, exponent) | A number raised to a power | =POWER(2, 10) |
PRODUCT(value1, [value2, …]) | Multiplies numbers together | =PRODUCT(A2:A4) |
QUOTIENT(dividend, divisor) | The whole part of a division | =QUOTIENT(10, 3) |
RAND() | A random number from 0 up to 1 | =RAND() |
RANDBETWEEN(low, high) | A random whole number between two numbers | =RANDBETWEEN(1, 6) |
ROUND(value, [places]) | Rounds to a number of decimal places, halves away from zero | =ROUND(2.675, 2) |
ROUNDDOWN(value, [places]) | Rounds towards zero | =ROUNDDOWN(3.29, 1) |
ROUNDUP(value, [places]) | Rounds away from zero | =ROUNDUP(3.21, 1) |
SIGN(value) | 1, 0 or -1 by the sign of a number | =SIGN(-3) |
SQRT(value) | The square root | =SQRT(16) |
SUM(value1, [value2, …]) | Adds numbers and the numbers in ranges | =SUM(A2:A10) |
SUMIF(range, criterion, [sum_range]) | Adds the cells that meet a criterion | =SUMIF(B2:B10, ">100") |
SUMIFS(sum_range, range1, criterion1, [range2, criterion2, …]) | Adds the cells that meet every criterion | =SUMIFS(C2:C10, A2:A10, "North", B2:B10, ">5") |
SUMPRODUCT(array1, [array2, …]) | Multiplies matching items of arrays and adds the products | =SUMPRODUCT(B2:B5, C2:C5) |
TRUNC(value, [places]) | Cuts off decimal places | =TRUNC(-7.8) |
Statistics
| Function | Does | Example |
|---|---|---|
AVERAGE(value1, [value2, …]) | The mean of the numbers | =AVERAGE(B2:B10) |
AVERAGEIF(range, criterion, [average_range]) | The mean of the cells that meet a criterion | =AVERAGEIF(A2:A10, "North", B2:B10) |
AVERAGEIFS(average_range, range1, criterion1, [range2, criterion2, …]) | The mean of the cells that meet every criterion | =AVERAGEIFS(C2:C10, A2:A10, "North") |
CORREL(range1, range2) | The correlation of two ranges | =CORREL(A2:A10, B2:B10) |
COUNT(value1, [value2, …]) | How many numbers there are | =COUNT(B2:B10) |
COUNTA(value1, [value2, …]) | How many cells are not empty | =COUNTA(A2:A10) |
COUNTBLANK(range) | How many cells are empty | =COUNTBLANK(A2:A10) |
COUNTIF(range, criterion) | How many cells meet a criterion | =COUNTIF(A2:A10, "Done") |
COUNTIFS(range1, criterion1, [range2, criterion2, …]) | How many rows meet every criterion | =COUNTIFS(A2:A10, "Done", B2:B10, ">3") |
LARGE(range, n) | The nth largest number | =LARGE(B2:B10, 2) |
MAX(value1, [value2, …]) | The largest number | =MAX(B2:B10) |
MAXIFS(max_range, range1, criterion1, [range2, criterion2, …]) | The largest number among the cells that meet every criterion | =MAXIFS(C2:C10, A2:A10, "North") |
MEDIAN(value1, [value2, …]) | The middle number | =MEDIAN(B2:B10) |
MIN(value1, [value2, …]) | The smallest number | =MIN(B2:B10) |
MINIFS(min_range, range1, criterion1, [range2, criterion2, …]) | The smallest number among the cells that meet every criterion | =MINIFS(C2:C10, A2:A10, "North") |
MODE(value1, [value2, …]) | The most common number | =MODE(B2:B10) |
PERCENTILE(range, k) | The value at a percentile (0 to 1) | =PERCENTILE(B2:B10, 0.9) |
QUARTILE(range, quart) | The value at a quartile (0 to 4) | =QUARTILE(B2:B10, 1) |
RANK(value, range, [ascending]) | A number's rank in a list (largest first unless ascending) | =RANK(B2, B2:B10) |
SMALL(range, n) | The nth smallest number | =SMALL(B2:B10, 2) |
STDEV(value1, [value2, …]) | The standard deviation of a sample | =STDEV(B2:B10) |
STDEV.P(value1, [value2, …]) | The standard deviation of a whole population | =STDEV.P(B2:B10) |
VAR(value1, [value2, …]) | The variance of a sample | =VAR(B2:B10) |
VAR.P(value1, [value2, …]) | The variance of a whole population | =VAR.P(B2:B10) |
Logic
| Function | Does | Example |
|---|---|---|
AND(value1, [value2, …]) | TRUE when every value is TRUE | =AND(B2>0, C2>0) |
FALSE() | FALSE | =FALSE() |
IF(condition, if_true, [if_false]) | One value when a condition is TRUE, another when not | =IF(B2>100, "Over", "Under") |
IFERROR(value, if_error) | The value, or another when it is an error | =IFERROR(A2/B2, 0) |
IFNA(value, if_na) | The value, or another when it is #N/A | =IFNA(VLOOKUP(A2, D:E, 2, FALSE), "Missing") |
IFS(condition1, value1, [condition2, value2, …]) | The value of the first TRUE condition | =IFS(B2>90, "A", B2>80, "B", TRUE, "C") |
NOT(value) | The opposite of TRUE or FALSE | =NOT(B2>0) |
OR(value1, [value2, …]) | TRUE when any value is TRUE | =OR(B2>0, C2>0) |
SWITCH(expression, case1, value1, [case2, value2, …], [default]) | The value of the case the expression equals | =SWITCH(A2, 1, "One", 2, "Two", "Many") |
TRUE() | TRUE | =TRUE() |
XOR(value1, [value2, …]) | TRUE when an odd number of values are TRUE | =XOR(B2>0, C2>0) |
Information
| Function | Does | Example |
|---|---|---|
ERROR.TYPE(value) | The number of an error (1 for #DIV/0!, ...) | =ERROR.TYPE(A2) |
ISBLANK(value) | TRUE when a cell is empty | =ISBLANK(A2) |
ISERROR(value) | TRUE for any error | =ISERROR(A2/B2) |
ISFORMULA(cell) | TRUE when a cell holds a formula | =ISFORMULA(B2) |
ISLOGICAL(value) | TRUE for TRUE or FALSE | =ISLOGICAL(A2) |
ISNA(value) | TRUE for #N/A | =ISNA(MATCH(A2, D:D, 0)) |
ISNUMBER(value) | TRUE for a number | =ISNUMBER(A2) |
ISTEXT(value) | TRUE for text | =ISTEXT(A2) |
N(value) | A value as a number (text is 0) | =N(A2) |
NA() | The #N/A error | =NA() |
TYPE(value) | The kind of a value: 1 number, 2 text, 4 TRUE/FALSE, 16 error, 64 array | =TYPE(A2) |
Lookup
| Function | Does | Example |
|---|---|---|
CHOOSE(index, value1, [value2, …]) | The value at a position in a list | =CHOOSE(2, "Low", "Mid", "High") |
COLUMN([cell]) | The column number of a cell | =COLUMN(D2) |
COLUMNS(range) | How many columns a range has | =COLUMNS(A1:F1) |
HLOOKUP(key, range, row, [is_sorted]) | Finds a key in the first row and gives a value from its column | =HLOOKUP("Q3", A1:F5, 4, FALSE) |
INDEX(range, row, [column]) | The cell at a row and column of a range | =INDEX(A2:C10, 3, 2) |
INDIRECT(reference_text, [a1]) | The cells a text reference names | =INDIRECT("B" & A2) |
LOOKUP(key, search_range, [result_range]) | Finds a key in a sorted range | =LOOKUP(42, A2:A10, B2:B10) |
MATCH(key, range, [type]) | A key's position in a row or column | =MATCH("Bob", A2:A10, 0) |
OFFSET(cell, rows, columns, [height], [width]) | A range moved from a starting cell | =SUM(OFFSET(A1, 1, 0, 3)) |
ROW([cell]) | The row number of a cell | =ROW(B4) |
ROWS(range) | How many rows a range has | =ROWS(A2:A10) |
VLOOKUP(key, range, column, [is_sorted]) | Finds a key in the first column and gives a value from its row | =VLOOKUP(A2, D2:F20, 3, FALSE) |
XLOOKUP(key, lookup_range, result_range, [missing], [match_mode], [search_mode]) | Finds a key and gives the matching value from another range | =XLOOKUP(A2, D:D, F:F, "Not found") |
XMATCH(key, range, [match_mode], [search_mode]) | A key's position, exactly or the nearest | =XMATCH("Bob", A2:A10) |
Text
| Function | Does | Example |
|---|---|---|
CHAR(number) | The character of a code | =CHAR(9733) |
CLEAN(text) | Text without control characters | =CLEAN(A2) |
CODE(text) | The code of the first character | =CODE("A") |
CONCAT(value1, [value2, …]) | Joins values together | =CONCAT(A2, " ", B2) |
CONCATENATE(value1, [value2, …]) | Joins values together | =CONCATENATE(A2, " ", B2) |
EXACT(text1, text2) | TRUE when two texts are the same, case included | =EXACT(A2, B2) |
FIND(search_for, text, [start]) | Where text starts in other text (case matters) | =FIND("@", A2) |
HYPERLINK(url, [label]) | A link to a web page | =HYPERLINK("https://livediagram.app", "livediagram") |
JOIN(delimiter, value1, [value2, …]) | Joins values with a delimiter | =JOIN(" / ", A2:C2) |
LEFT(text, [count]) | The first characters of text | =LEFT(A2, 3) |
LEN(text) | How many characters text has | =LEN(A2) |
LOWER(text) | Text in small letters | =LOWER(A2) |
MID(text, start, count) | Characters from the middle of text | =MID(A2, 2, 3) |
PROPER(text) | Text With Each Word Capitalised | =PROPER(A2) |
REGEXEXTRACT(text, pattern) | The part of text that matches a regular expression | =REGEXEXTRACT(A2, "\d+") |
REGEXMATCH(text, pattern) | TRUE when text matches a regular expression | =REGEXMATCH(A2, "^\d+$") |
REGEXREPLACE(text, pattern, replacement) | Replaces what matches a regular expression | =REGEXREPLACE(A2, "\s+", " ") |
REPLACE(text, start, count, new_text) | Replaces characters at a position | =REPLACE(A2, 1, 3, "XYZ") |
REPT(text, count) | Text repeated | =REPT("★", B2) |
RIGHT(text, [count]) | The last characters of text | =RIGHT(A2, 4) |
SEARCH(search_for, text, [start]) | Where text starts in other text (case ignored, wildcards) | =SEARCH("inv*", A2) |
SPLIT(text, delimiter, [split_by_each], [remove_empty]) | Splits text into cells across a row | =SPLIT(A2, ",") |
SUBSTITUTE(text, search_for, replace_with, [occurrence]) | Replaces text with other text | =SUBSTITUTE(A2, "-", " ") |
TEXT(value, format) | A number as text in a format | =TEXT(A2, "dd/mm/yyyy") |
TEXTJOIN(delimiter, ignore_empty, value1, [value2, …]) | Joins values with a delimiter | =TEXTJOIN(", ", TRUE, A2:A10) |
TRIM(text) | Text without extra spaces | =TRIM(A2) |
UPPER(text) | Text in capitals | =UPPER(A2) |
VALUE(text) | Text that looks like a number, as the number | =VALUE("1,234.5") |
Date and time
| Function | Does | Example |
|---|---|---|
DATE(year, month, day) | A date from its parts | =DATE(2026, 10, 8) |
DATEDIF(start, end, unit) | The time between two dates in years ("Y"), months ("M") or days ("D") | =DATEDIF(A2, TODAY(), "Y") |
DATEVALUE(text) | A date typed as text, as a date | =DATEVALUE("8 Oct 2026") |
DAY(date) | The day of the month | =DAY(A2) |
DAYS(end, start) | The days between two dates | =DAYS(B2, A2) |
EDATE(start, months) | The date a number of months away | =EDATE(A2, 3) |
EOMONTH(start, months) | The last day of a month a number of months away | =EOMONTH(A2, 0) |
HOUR(time) | The hour of a time | =HOUR(A2) |
ISOWEEKNUM(date) | The ISO week of the year | =ISOWEEKNUM(A2) |
MINUTE(time) | The minute of a time | =MINUTE(A2) |
MONTH(date) | The month of a date (1 to 12) | =MONTH(A2) |
NETWORKDAYS(start, end, [holidays]) | The working days between two dates | =NETWORKDAYS(A2, B2) |
NOW() | The date and time now | =NOW() |
SECOND(time) | The second of a time | =SECOND(A2) |
TIME(hour, minute, second) | A time from its parts | =TIME(14, 30, 0) |
TIMEVALUE(text) | A time typed as text, as a time | =TIMEVALUE("2:30 pm") |
TODAY() | Today's date | =TODAY() |
WEEKDAY(date, [type]) | The day of the week as a number | =WEEKDAY(A2, 2) |
WEEKNUM(date, [type]) | The week of the year | =WEEKNUM(A2) |
WORKDAY(start, days, [holidays]) | The date a number of working days away | =WORKDAY(A2, 10) |
YEAR(date) | The year of a date | =YEAR(A2) |
YEARFRAC(start, end, [basis]) | The share of a year between two dates | =YEARFRAC(A2, B2) |
Arrays
| Function | Does | Example |
|---|---|---|
ARRAYFORMULA(formula) | Works a formula out over whole ranges | =ARRAYFORMULA(A2:A10*B2:B10) |
FILTER(range, condition1, [condition2, …]) | The rows that meet every condition | =FILTER(A2:C20, C2:C20>100) |
SEQUENCE(rows, [columns], [start], [step]) | A run of numbers | =SEQUENCE(10) |
SORT(range, [column], [ascending], [column2, ascending2, …]) | A range sorted by its columns | =SORT(A2:C20, 3, FALSE) |
SORTBY(range, by_range1, [order1], [by_range2, order2, …]) | A range sorted by other ranges | =SORTBY(A2:A20, B2:B20, -1) |
TRANSPOSE(range) | A range with rows and columns swapped | =TRANSPOSE(A1:C4) |
UNIQUE(range, [by_column], [exactly_once]) | The distinct rows of a range | =UNIQUE(A2:A20) |
Finance
| Function | Does | Example |
|---|---|---|
FV(rate, periods, payment, [present_value], [type]) | The future value of an investment | =FV(4%/12, 120, -200) |
IRR(values, [guess]) | The internal rate of return of cash flows | =IRR(B2:B6) |
NPER(rate, payment, present_value, [future_value], [type]) | How many periods a loan takes | =NPER(5%/12, -400, 20000) |
NPV(rate, value1, [value2, …]) | The net present value of cash flows | =NPV(8%, B2:B6) |
PMT(rate, periods, present_value, [future_value], [type]) | The payment for a loan | =PMT(5%/12, 60, 20000) |
PV(rate, periods, payment, [future_value], [type]) | The present value of payments | =PV(5%/12, 60, -400) |
RATE(periods, payment, present_value, [future_value], [type], [guess]) | The interest rate per period | =RATE(60, -400, 20000) |
Plan cards
| Function | Does | Example |
|---|---|---|
CARD(number, field) | One field of a card | =CARD(12, "Title") |
CARDCOUNT([field1, value1, …]) | How many cards match every field and value | =CARDCOUNT("State", "Done") |
CARDS(fields, [field1, value1, …]) | A table of the matching cards, spilled | =CARDS("Number, Title, State", "Type", "Bug") |
CARDSUM(sum_field, [field1, value1, …]) | The sum of a number field over the matching cards | =CARDSUM("Estimate", "Assignee", "Sam") |
Was this article helpful?