Formulas and functions

The Flax formula language — cell and range references, operators, error values, and a reference of the built-in functions grouped by category.

Sheets speak the spreadsheet formula language you already know. This page covers how to reference cells, the operators available, the error values you may see, and the built-in functions grouped by category.

#Writing a formula

A formula starts with = and can combine literals, references, operators, and function calls, for example:

=SUM(A1:A10) * 1.2
=IF(B2 > 0, "profit", "loss")
=VLOOKUP(A2, Orders!A:C, 3, FALSE)

Unknown function names resolve to #NAME? rather than failing silently, so a mistyped formula is always visible.

#Cell and range references

Form Meaning
A1 A single cell (column A, row 1)
$A$1 Absolute reference (both fixed); mixed forms $A1 and A$1 also work
A1:B3 A rectangular range
A:A A whole column
Orders!A1 A cell on another tab in the same workbook
'Cash Flow'!B2 A cell on a tab whose name has spaces (single-quoted)

Cross-tab references are covered in Workbooks and tabs. A range may not span two tabs.

#Operators

Operator Purpose
+ - * / Arithmetic
^ Exponentiation (right-associative)
& String concatenation
= <> < <= > >= Comparison
unary + / - Sign

TRUE and FALSE are literals. Text literals use double quotes, with "" to escape a quote inside a string.

#Error values

When a formula cannot produce a value it returns a standard error code:

Error Meaning
#DIV/0! Division by zero
#VALUE! Wrong type of argument
#NAME? Unknown function or unresolved reference
#REF! Reference to a missing cell or renamed tab
#N/A Value not available (e.g. a failed lookup)
#NUM! Invalid numeric result
#CYCLE! Circular reference between cells

#Function reference

The formula engine ships a broad library. The tables below list the functions by category; each takes arguments the same way as its spreadsheet equivalent.

#Math and rounding
SUM PRODUCT ABS SQRT
INT ROUND ROUNDUP ROUNDDOWN
TRUNC MROUND CEILING FLOOR
MOD QUOTIENT POWER EXP
LN LOG LOG10 SIGN
GCD LCM EVEN ODD
PI SUMPRODUCT

#Aggregation and conditional aggregation
AVERAGE COUNT COUNTA COUNTBLANK
MIN MAX SUMIF SUMIFS
COUNTIF COUNTIFS AVERAGEIF AVERAGEIFS
MAXIFS MINIFS

#Statistical
MEDIAN MODE STDEV STDEVP
VAR VARP LARGE SMALL
RANK PERCENTILE QUARTILE

#Logical and information
IF IFS IFERROR IFNA
SWITCH AND OR XOR
NOT ISBLANK ISNUMBER ISTEXT
ISERROR ISNA ISLOGICAL N
NA

#Lookup and reference
VLOOKUP HLOOKUP XLOOKUP LOOKUP
INDEX MATCH CHOOSE ROWS
COLUMNS

#Text
CONCAT CONCATENATE TEXTJOIN LEN
LEFT RIGHT MID FIND
SEARCH REPLACE SUBSTITUTE REPT
UPPER LOWER PROPER TRIM
CLEAN EXACT CHAR CODE
VALUE NUMBERVALUE

#Date and time
DATE DATEVALUE YEAR MONTH
DAY HOUR MINUTE SECOND
TIME WEEKDAY WEEKNUM EDATE
EOMONTH DAYS DATEDIF TODAY
NOW

Tip

As you type a function name, the editor suggests matching functions with their signatures, so you rarely need to remember exact argument order.

#Next steps