Formula reference
121 formulas, explained.
Type = at the start of a cell to use a formula, just as in Excel. Formula mode
(⌘4) shows the formulas themselves; every other mode shows their results.
The examples use this table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Region |
| 2 | Apple | 1.5 | 10 | North |
| 3 | Banana | 0.8 | 20 | South |
| 4 | Cherry | 3 | 15 | North |
| 5 | Date | 2.25 | 0 | East |
A range like C2:C5 means cells C2 to C5.
Operators
+ − * / | Add, subtract, multiply, divide | =B2*C2 → 15 |
^ | Power | =2^3 → 8 |
% | Percent | =50% → 0.5 |
& | Join text | =A2&" x"&C2 → Apple x10 |
= <> < > <= >= | Compare (text ignores case) | =C3>C2 → TRUE |
Order of operations matches Excel. Errors: #DIV/0! dividing by zero,
#VALUE! wrong kind of value, #REF! a formula that refers to
itself, #NAME? unknown function, #N/A nothing found.
Wrap a formula in IFERROR to replace an error.
No formulas match that search.
Math & totals
SUM
SUM(number1, [number2], …) Adds numbers or ranges together.
=SUM(C2:C5) 45 AVERAGE
AVERAGE(number1, …) The mean of the numbers.
=AVERAGE(B2:B5) 1.8875 MIN
MIN(number1, …) The smallest number.
=MIN(B2:B5) 0.8 MAX
MAX(number1, …) The largest number.
=MAX(C2:C5) 20 COUNT
COUNT(value1, …) How many cells contain numbers.
=COUNT(B1:B5) 4 COUNTA
COUNTA(value1, …) How many cells are not empty.
=COUNTA(A1:A5) 5 COUNTBLANK
COUNTBLANK(range) How many cells are empty.
=COUNTBLANK(A2:D5) 0 PRODUCT
PRODUCT(number1, …) Multiplies numbers together.
=PRODUCT(B2, C2) 15 SUMPRODUCT
SUMPRODUCT(range1, range2, …) Multiplies matching items in the ranges, then adds the results. Handy for price × quantity totals.
=SUMPRODUCT(B2:B5, C2:C5) 76 SUMSQ
SUMSQ(number1, …) Adds the squares of the numbers.
=SUMSQ(3, 4) 25 ROUND
ROUND(number, digits) Rounds to a number of decimal places.
=ROUND(1.8875, 2) 1.89 ROUNDUP
ROUNDUP(number, digits) Rounds away from zero.
=ROUNDUP(0.81, 1) 0.9 ROUNDDOWN
ROUNDDOWN(number, digits) Rounds towards zero.
=ROUNDDOWN(2.25, 0) 2 MROUND
MROUND(number, multiple) Rounds to the nearest multiple.
=MROUND(17, 5) 15 INT
INT(number) Rounds down to a whole number.
=INT(-2.5) -3 TRUNC
TRUNC(number, [digits]) Cuts off the decimals without rounding.
=TRUNC(-2.5) -2 CEILING
CEILING(number, significance) Rounds up to a multiple.
=CEILING(2.25, 0.5) 2.5 FLOOR
FLOOR(number, significance) Rounds down to a multiple.
=FLOOR(2.25, 0.5) 2 ABS
ABS(number) The number without its sign.
=ABS(-7) 7 SIGN
SIGN(number) 1 if positive, -1 if negative, 0 if zero.
=SIGN(-7) -1 MOD
MOD(number, divisor) The remainder after dividing.
=MOD(17, 5) 2 QUOTIENT
QUOTIENT(number, divisor) The whole-number part of a division.
=QUOTIENT(17, 5) 3 POWER
POWER(number, power) Raises a number to a power. Same as 2^10.
=POWER(2, 10) 1024 SQRT
SQRT(number) The square root.
=SQRT(144) 12 EXP
EXP(number) e raised to a power.
=EXP(0) 1 LN
LN(number) The natural logarithm.
=LN(1) 0 LOG
LOG(number, [base]) The logarithm, base 10 unless you give another.
=LOG(8, 2) 3 LOG10
LOG10(number) The base-10 logarithm.
=LOG10(1000) 3 PI
PI() The number π.
=PI() 3.141592654 FACT
FACT(number) The factorial: 5 × 4 × 3 × 2 × 1.
=FACT(5) 120 RAND
RAND() A random number between 0 and 1.
=RAND() e.g. 0.3443 RANDBETWEEN
RANDBETWEEN(low, high) A random whole number in a range.
=RANDBETWEEN(1, 6) e.g. 5 Conditional totals
COUNTIF
COUNTIF(range, criteria) Counts cells that match a condition.
=COUNTIF(D2:D5, "North") 2 COUNTIFS
COUNTIFS(range1, criteria1, …) Counts rows that match every condition.
=COUNTIFS(D2:D5, "North", C2:C5, ">10") 1 SUMIF
SUMIF(range, criteria, [sum_range]) Adds the values where a condition matches.
=SUMIF(D2:D5, "North", C2:C5) 25 SUMIFS
SUMIFS(sum_range, range1, criteria1, …) Adds the values where every condition matches.
=SUMIFS(C2:C5, D2:D5, "North", B2:B5, ">2") 15 AVERAGEIF
AVERAGEIF(range, criteria, [average_range]) Averages the values where a condition matches.
=AVERAGEIF(D2:D5, "North", B2:B5) 2.25 AVERAGEIFS
AVERAGEIFS(average_range, range1, criteria1, …) Averages the values where every condition matches.
=AVERAGEIFS(C2:C5, B2:B5, "<3") 10 MAXIFS
MAXIFS(max_range, range1, criteria1, …) The largest value where the conditions match.
=MAXIFS(C2:C5, D2:D5, "North") 15 MINIFS
MINIFS(min_range, range1, criteria1, …) The smallest value where the conditions match.
=MINIFS(B2:B5, D2:D5, "North") 1.5 Statistics
MEDIAN
MEDIAN(number1, …) The middle value.
=MEDIAN(C2:C5) 12.5 MODE
MODE(number1, …) The most common value.
=MODE(1, 2, 2, 3) 2 STDEV
STDEV(number1, …) Standard deviation of a sample.
=STDEV(2, 4, 4, 4, 5, 5, 7, 9) 2.138089935 STDEVP
STDEVP(number1, …) Standard deviation of a whole population.
=STDEVP(2, 4, 4, 4, 5, 5, 7, 9) 2 VAR
VAR(number1, …) Variance of a sample.
=VAR(1, 2, 3, 4, 5) 2.5 VARP
VARP(number1, …) Variance of a whole population.
=VARP(1, 2, 3, 4) 1.25 LARGE
LARGE(range, k) The k-th largest value.
=LARGE(C2:C5, 2) 15 SMALL
SMALL(range, k) The k-th smallest value.
=SMALL(C2:C5, 2) 10 RANK
RANK(number, range, [ascending]) Where a number ranks in a list (1 = largest).
=RANK(15, C2:C5) 2 PERCENTILE
PERCENTILE(range, k) The value at a percentile, with k from 0 to 1.
=PERCENTILE(C2:C5, 0.5) 12.5 QUARTILE
QUARTILE(range, quart) The value at a quartile (0 to 4).
=QUARTILE(C2:C5, 1) 7.5 Logic
IF
IF(test, value_if_true, [value_if_false]) Returns one thing if a test is true and another if it isn't.
=IF(C2 > 5, "Plenty", "Low") Plenty IFS
IFS(test1, value1, test2, value2, …) Checks tests in order and returns the value for the first true one.
=IFS(C4 >= 20, "High", C4 >= 10, "Medium", TRUE, "Low") Medium IFERROR
IFERROR(value, value_if_error) Replaces any error with something friendlier.
=IFERROR(1/0, "n/a") n/a IFNA
IFNA(value, value_if_na) Replaces a #N/A (nothing found) result.
=IFNA(MATCH("Kiwi", A2:A5, 0), "Not found") Not found AND
AND(test1, test2, …) TRUE only if every test is true.
=AND(C2 > 5, D2 = "North") TRUE OR
OR(test1, test2, …) TRUE if any test is true.
=OR(D3 = "North", D3 = "South") TRUE XOR
XOR(test1, test2, …) TRUE if an odd number of tests are true.
=XOR(TRUE, FALSE) TRUE NOT
NOT(test) Flips TRUE and FALSE.
=NOT(C5 > 0) TRUE SWITCH
SWITCH(value, match1, result1, …, [default]) Picks a result by matching a value.
=SWITCH(D3, "North", "N", "South", "S", "Other") S CHOOSE
CHOOSE(index, option1, option2, …) Picks an option by its position.
=CHOOSE(2, "Red", "Green", "Blue") Green TRUE
TRUE The logical value TRUE.
=TRUE TRUE FALSE
FALSE The logical value FALSE.
=FALSE FALSE Text
LEN
LEN(text) How many characters.
=LEN(A3) 6 LEFT
LEFT(text, [count]) Characters from the start.
=LEFT(A2, 3) App RIGHT
RIGHT(text, [count]) Characters from the end.
=RIGHT(A2, 2) le MID
MID(text, start, count) Characters from the middle.
=MID(A3, 2, 3) ana UPPER
UPPER(text) Converts to capitals.
=UPPER(A2) APPLE LOWER
LOWER(text) Converts to lower case.
=LOWER("HELLO") hello PROPER
PROPER(text) Capitalises Each Word.
=PROPER("ava thompson") Ava Thompson TRIM
TRIM(text) Removes extra spaces.
=TRIM(" too many ") too many CLEAN
CLEAN(text) Removes invisible control characters.
=CLEAN(A2) Apple CONCAT
CONCAT(text1, text2, …) Joins text together. The & operator does the same.
=CONCAT(A2, " from ", D2) Apple from North CONCATENATE
CONCATENATE(text1, text2, …) Joins text together (older name for CONCAT).
=CONCATENATE(A2, "-", C2) Apple-10 TEXTJOIN
TEXTJOIN(separator, skip_empty, text1, …) Joins a range with a separator between each item.
=TEXTJOIN(", ", TRUE, A2:A4) Apple, Banana, Cherry SUBSTITUTE
SUBSTITUTE(text, old, new, [which]) Replaces some text with other text.
=SUBSTITUTE("2026-09-25", "-", "/") 2026/09/25 REPLACE
REPLACE(text, start, count, new) Replaces characters at a position.
=REPLACE("Copycell", 1, 4, "Sheet") Sheetcell FIND
FIND(find, text, [start]) Where text first appears (case matters).
=FIND("n", A3) 3 SEARCH
SEARCH(find, text, [start]) Where text first appears (case doesn't matter, * and ? wildcards).
=SEARCH("N", A3) 3 REPT
REPT(text, times) Repeats text.
=REPT("*", 5) ***** EXACT
EXACT(text1, text2) TRUE if two texts match exactly, including case.
=EXACT("apple", A2) FALSE VALUE
VALUE(text) Turns text into a number.
=VALUE("12.5") 12.5 TEXT
TEXT(value, format) Formats a number or date as text.
=TEXT(1234.5, "$#,##0.00") $1,234.50 CHAR
CHAR(code) The character for a code.
=CHAR(65) A CODE
CODE(text) The code of the first character.
=CODE("A") 65 Lookup
VLOOKUP
VLOOKUP(value, table, column, [approximate]) Finds a value in the first column of a table and returns a value from another column in that row.
=VLOOKUP("Cherry", A2:D5, 2, FALSE) 3 HLOOKUP
HLOOKUP(value, table, row, [approximate]) Like VLOOKUP, but looks along the top row.
=HLOOKUP("Qty", A1:D5, 3, FALSE) 20 XLOOKUP
XLOOKUP(value, lookup_range, return_range, [if_not_found]) Finds a value in one range and returns the matching item from another. The modern replacement for VLOOKUP.
=XLOOKUP("Banana", A2:A5, D2:D5) South INDEX
INDEX(range, row, [column]) The value at a position in a range.
=INDEX(A2:D5, 3, 1) Cherry MATCH
MATCH(value, range, [type]) The position of a value in a range. Use 0 for an exact match.
=MATCH("Cherry", A2:A5, 0) 3 ROW
ROW([cell]) The row number of a cell.
=ROW(C4) 4 COLUMN
COLUMN([cell]) The column number of a cell (A = 1).
=COLUMN(C4) 3 ROWS
ROWS(range) How many rows a range has.
=ROWS(A2:D5) 4 COLUMNS
COLUMNS(range) How many columns a range has.
=COLUMNS(A2:D5) 4 Checking values
ISBLANK
ISBLANK(value) TRUE if a cell is empty.
=ISBLANK(E2) TRUE ISNUMBER
ISNUMBER(value) TRUE if it's a number.
=ISNUMBER(B2) TRUE ISTEXT
ISTEXT(value) TRUE if it's text.
=ISTEXT(A2) TRUE ISLOGICAL
ISLOGICAL(value) TRUE if it's TRUE or FALSE.
=ISLOGICAL(C2 > 5) TRUE ISERROR
ISERROR(value) TRUE for any error.
=ISERROR(1/0) TRUE ISERR
ISERR(value) TRUE for any error except #N/A.
=ISERR(NA()) FALSE ISNA
ISNA(value) TRUE only for #N/A.
=ISNA(NA()) TRUE ISEVEN
ISEVEN(number) TRUE if the number is even.
=ISEVEN(C2) TRUE ISODD
ISODD(number) TRUE if the number is odd.
=ISODD(C4) TRUE NA
NA() The #N/A error, for “no value here”.
=NA() #N/A N
N(value) Converts a value to a number: text becomes 0, TRUE becomes 1.
=N(TRUE) 1 T
T(value) Returns text as it is, and anything else as empty.
=T(A2) Apple Dates and times
TODAY
TODAY() Today's date.
=TODAY() Today's date NOW
NOW() The date and time right now.
=NOW() The current date and time DATE
DATE(year, month, day) Builds a date from its parts.
=TEXT(DATE(2026, 9, 25), "d mmm yyyy") 25 Sep 2026 DATEVALUE
DATEVALUE(text) Turns text like 2026-09-25 into a date. Dates are stored as day numbers, as in Excel.
=DATEVALUE("2026-09-25") 46290 YEAR
YEAR(date) The year of a date.
=YEAR("2026-09-25") 2026 MONTH
MONTH(date) The month, 1 to 12.
=MONTH("2026-09-25") 9 DAY
DAY(date) The day of the month.
=DAY("2026-09-25") 25 HOUR
HOUR(time) The hour, 0 to 23.
=HOUR("2026-09-25 14:30") 14 MINUTE
MINUTE(time) The minute.
=MINUTE("2026-09-25 14:30") 30 SECOND
SECOND(time) The second.
=SECOND("2026-09-25 14:30:15") 15 WEEKDAY
WEEKDAY(date, [type]) The day of the week, 1 (Sunday) to 7 (Saturday).
=WEEKDAY("2026-09-25") 6 DAYS
DAYS(end, start) The number of days between two dates.
=DAYS("2026-12-25", "2026-09-25") 91 EDATE
EDATE(date, months) The same day a number of months later (or earlier).
=TEXT(EDATE("2026-09-25", 3), "yyyy-mm-dd") 2026-12-25 EOMONTH
EOMONTH(date, months) The last day of a month.
=TEXT(EOMONTH("2026-09-25", 0), "yyyy-mm-dd") 2026-09-30 DATEDIF
DATEDIF(start, end, unit) The gap between two dates in "d" days, "m" months or "y" years.
=DATEDIF("2026-01-01", "2026-09-25", "m") 8 Want to try them? Download the sample file: its last row already uses COUNTA, SUM, COUNTIF, SUMIF and TEXT.