REFERENCE

Formula reference

Every function a sheet can compute, each with an example over the small sheet below and the answer it gives. The examples are worked by the same engine your sheets use.

	AClaim	BFee	CStatus	DHours
1	Late delivery	1,200	open	4
2	Damaged goods	450	closed	2
3	Wrong item	800	open	3
4	Missing parts	450	pending	2
5	Refund delay	2,500	open	9
6			closed	1

Writing a formula

Start the cell with =. A1 is column A of the first row of data; the column names are headings, not a row.
A range runs corner to corner: B1:B6 is six cells of one column, A1:B6 two columns.
Name a range in the inspector (Fees = B2:B40) and write the name instead.
Text goes in double quotes: =IF(B1>1000, "large", "small").
A formula reads the tab it is on.

Math

SUM

SUM(value1, [value2], …)

Adds the numbers in its arguments. Text and blank cells are skipped.

=SUM(B1:B6)gives 5,400

AVERAGE

AVERAGE(value1, [value2], …)

The mean of the numbers. Text and blanks are skipped; with no numbers at all the answer is #DIV/0!.

=AVERAGE(B1:B6)gives 1,080

COUNT

COUNT(value1, [value2], …)

How many of the cells hold a number.

=COUNT(B1:B6)gives 5

COUNTA

COUNTA(value1, [value2], …)

How many of the cells are not blank, whatever they hold. A cell of only spaces counts as blank.

=COUNTA(A1:A6)gives 5

MIN

MIN(value1, [value2], …)

The smallest number.

=MIN(B1:B6)gives 450

MAX

MAX(value1, [value2], …)

The largest number.

=MAX(B1:B6)gives 2,500

ROUND

ROUND(number, [digits])

Rounds to the given number of decimal places; with no digits, to a whole number.

=ROUND(B1/7, 1)gives 171.4

ABS

ABS(number)

The number without its sign.

=ABS(B2-B1)gives 750

Logic

IF(test, if_true, [if_false])

One value when the test holds and another when it does not. Without if_false, a failed test gives FALSE.

=IF(B1>1000, "large", "small")gives large

AND

AND(test1, [test2], …)

TRUE when every test holds.

=AND(B1>1000, C1="open")gives TRUE

OR(test1, [test2], …)

TRUE when any test holds.

=OR(C2="open", D2>5)gives FALSE

NOT

NOT(test)

The opposite of the test.

=NOT(C2="open")gives TRUE

IFERROR

IFERROR(value, fallback)

The value, or the fallback when the value is an error such as #DIV/0! or #N/A.

=IFERROR(B1/0, 0)gives 0

Text

CONCAT

CONCAT(text1, [text2], …)

Joins its arguments into one piece of text.

=CONCAT(A1, " (", C1, ")")gives Late delivery (open)

CONCATENATE

CONCATENATE(text1, [text2], …)

The same as CONCAT, under the name older spreadsheets use.

=CONCATENATE(C3, "-", D3)gives open-3

LEN

LEN(text)

How many characters the text has, spaces included.

=LEN(A2)gives 13

LEFT

LEFT(text, [count])

The first characters of the text; one when no count is given.

=LEFT(A3, 5)gives Wrong

RIGHT

RIGHT(text, [count])

The last characters of the text; one when no count is given.

=RIGHT(A4, 5)gives parts

MID

MID(text, start, count)

Characters from the middle of the text, counting the first character as 1.

=MID(A1, 6, 8)gives delivery

TRIM

TRIM(text)

The text without spaces at either end, and with every run of spaces inside it made one.

=TRIM(A5)gives Refund delay

UPPER

UPPER(text)

The text in capitals.

=UPPER(C1)gives OPEN

LOWER

LOWER(text)

The text in small letters.

=LOWER(A2)gives damaged goods

Lookup

XLOOKUP

XLOOKUP(lookup, lookup_range, return_range, [if_not_found])

Finds the first cell in lookup_range equal to lookup and returns the cell in the same position of return_range. Without if_not_found, a miss is #N/A. Both ranges are named, so inserting a column never changes the answer.

=XLOOKUP("Wrong item", A1:A6, B1:B6)gives 800

MATCH

MATCH(lookup, range, [match_type])

The position of lookup in the range, counting from 1. match_type 0 finds an exact match (text may use * and ?); 1, the default, finds the last value at most lookup in an ascending range; -1 the last at least lookup in a descending one. A miss is #N/A.

=MATCH("Wrong item", A1:A6, 0)gives 3

INDEX

INDEX(range, row, [column])

The cell at that row and column of the range, counting from 1. Paired with MATCH it looks up by position. A position outside the range is #REF!.

=INDEX(A1:A6, MATCH(800, B1:B6, 0))gives Wrong item

Date

TODAY

TODAY()

Today's date in UTC, written as year-month-day. It changes by itself each day.

=TODAY()gives today’s date, as year-month-day

DATE

DATE(year, month, day)

The date for a year, month and day, written year-month-day. A month past 12 or a day past the month's end rolls forward, as in Excel.

=DATE(2026, 2, 30)gives 2026-03-02

YEAR

YEAR(date)

The year of a date written year-month-day, or of a date serial number.

=YEAR("2026-10-10")gives 2,026

MONTH

MONTH(date)

The month of a date, from 1 to 12.

=MONTH("2026-10-10")gives 10

DAY

DAY(date)

The day of the month of a date, from 1 to 31.

=DAY("2026-10-10")gives 10

EOMONTH

EOMONTH(start, months)

The last day of the month that many months after start's month; 0 is start's own month and a negative number counts back.

=EOMONTH("2026-01-15", 1)gives 2026-02-28

DATEDIF

DATEDIF(start, end, unit)

Whole years ("Y"), months ("M") or days ("D") from start to end; "YM", "MD" and "YD" give what is left past whole years or months. An end before the start is #NUM!.

=DATEDIF("2025-03-15", "2026-10-10", "M")gives 18

Statistics

MEDIAN

MEDIAN(value1, [value2], …)

The middle number, or the mean of the middle two when there is an even count.

=MEDIAN(B1:B6)gives 800

MODE

MODE(value1, [value2], …)

The number that appears most often; among ties, the one that appears first. #N/A when no number repeats.

=MODE(B1:B6)gives 450

VAR

VAR(value1, [value2], …)

The variance of a sample: the squared distances from the mean, divided by one less than the count.

=VAR(D1:D6)gives 8.3

VARP

VARP(value1, [value2], …)

The variance of a whole population: divided by the count itself.

=VARP(D1:D6)gives 6.9167

STDEV

STDEV(value1, [value2], …)

The standard deviation of a sample: the square root of VAR.

=ROUND(STDEV(D1:D6), 3)gives 2.881

STDEVP

STDEVP(value1, [value2], …)

The standard deviation of a whole population: the square root of VARP.

=ROUND(STDEVP(D1:D6), 3)gives 2.63

PERCENTILE

PERCENTILE(range, k)

The value below which a share k of the numbers fall, k between 0 and 1, interpolating between neighbours as Excel's PERCENTILE.INC does. A k outside 0 to 1 is #NUM!.

=PERCENTILE(B1:B6, 0.9)gives 1,980

QUARTILE

QUARTILE(range, quart)

A quartile: 0 is the minimum, 1 the first quartile, 2 the median, 3 the third quartile and 4 the maximum.

=QUARTILE(B1:B6, 1)gives 450

CORREL

CORREL(range1, range2)

How closely two columns move together, from -1 to 1, over the rows where both hold a number.

=ROUND(CORREL(B1:B6, D1:D6), 3)gives 0.996

Conditional

COUNTIF

COUNTIF(range, criterion)

How many cells meet the criterion: a value ("open"), a comparison (">500", "<>closed"), a pattern with * and ? ("Wr*"), or "" for blank. Text is matched without regard to case.

=COUNTIF(C1:C6, "open")gives 3

SUMIF

SUMIF(range, criterion, [sum_range])

Adds the cells of sum_range whose partners in range meet the criterion. Without sum_range, adds the matching cells themselves.

=SUMIF(C1:C6, "open", B1:B6)gives 4,500

AVERAGEIF

AVERAGEIF(range, criterion, [average_range])

The mean of the cells whose partners meet the criterion; #DIV/0! when none do.

=AVERAGEIF(B1:B6, ">500")gives 1,500

COUNTBLANK

COUNTBLANK(range)

How many cells in the range are blank. A cell of only spaces counts as blank.

=COUNTBLANK(A1:B6)gives 2

COUNTIFS

COUNTIFS(range1, criterion1, [range2, criterion2], …)

How many positions meet every criterion at once, each read as COUNTIF reads it. The ranges must be the same size.

=COUNTIFS(C1:C6, "open", B1:B6, ">1000")gives 2

SUMIFS

SUMIFS(sum_range, range1, criterion1, [range2, criterion2], …)

Adds the cells of sum_range at the positions meeting every criterion.

=SUMIFS(B1:B6, C1:C6, "open", D1:D6, ">3")gives 3,700

AVERAGEIFS

AVERAGEIFS(average_range, range1, criterion1, [range2, criterion2], …)

The mean of average_range at the positions meeting every criterion; #DIV/0! when none do.

=AVERAGEIFS(D1:D6, C1:C6, "closed")gives 1.5

When a formula fails

A failed formula shows why in the cell, in Excel’s words, so it can be searched for.

#DIV/0!: Divided by zero, or an average of nothing.
#VALUE!: A value of the wrong kind, or the wrong number of arguments.
#NAME?: A function or a range name this sheet does not have.
#REF!: A reference to a cell that is not there.
#NUM!: A number out of range, such as a percentile above 1.
#N/A: A lookup that found nothing, or a mode with no repeats.
#CYCLE!: A formula that depends on itself.