Sheet formulas

Every operator, function, error and number format a report sheet formula understands, with worked examples on a small table.

View as Markdown

Formulas add a column of your own to a table block on a report. The language is Excel's, without the parts that need a grid: the operators are Excel's, the function names are Excel's and each means what it means in Excel. That is what lets an exported workbook carry the same formula and keep calculating.

The sample table

The examples on this page run against this table, which is what a table block might return. The net revenue column is a sum, and ikura has no revenue.

product	net revenue	cost
chai	1,200	700
aniseed syrup	800	900
chang	2,000	500
ikura	(empty)	0

Where an example gives one result, it is the result on the chai row unless the text says otherwise.

Syntax

A formula is an expression. A leading = is allowed and ignored, and spaces are ignored everywhere except inside text.

Piece	Written as	Notes
Column	`[net revenue]`	The column's name in square brackets, spaces and all
Number	`12`, `3.5`, `.5`, `1e3`	No thousands separators. Write a negative with a minus sign: `-4`.
Text	`"chai"`	In double quotes. Write a double quote inside text by doubling it: `"say ""hi"""`.
Truth value	`TRUE`, `FALSE`	Shown as TRUE and FALSE
Function	`ROUND([cost], 1)`	A name, then arguments in brackets separated by commas
Grouping	`([a] + [b]) * 2`	Brackets change the order of work

Function names are not case-sensitive: sum, Sum and SUM are the same function. A formula can be up to 500 characters. The editor says so under the box and will not save a longer one.

References

A column reference in square brackets is the column's name as its header shows it. It is matched without regard to case, so [Net Revenue] finds net revenue.

A reference means one of two things depending on where it sits:

On its own or inside an operator or ordinary function, it is this row's value. [net revenue] - [cost] is 500 on the chai row.
As a bare argument of SUM, AVERAGE, MIN, MAX, COUNT or COUNTA, it is the whole column. [net revenue] / SUM([net revenue]) is each row's share of the total: 0.3 on the chai row.

Only a bare [column] inside an aggregate reads the whole column. Any other argument is worked out for the current row. SUM([net revenue] * 2) is therefore not the total of a doubled column; on the chai row it is 2,400, the doubled value of that one row. To total a computed value, give it a column of its own and then sum that: add revenue x2 as a formula, then use SUM([revenue x2]).

Some more rules:

A formula can read the columns before it, including formula columns you added earlier. It cannot read itself or a column added after it.
Whole-column functions cover the rows the block returned, no more. If the table says of more, the block's row limit cut the result short and a total covers only the rows you can see.
A formula keeps reading a column you hide after writing it. The formula editor only accepts columns that are showing.
A reference to a column that does not exist makes the whole formula unreadable: its column shows #NAME?, and the sentence under the table says which column is missing.

Empty cells and text that looks like numbers

Formulas treat the data the way Excel does:

An empty cell is 0 in arithmetic, so [net revenue] - [cost] on the ikura row is 0, not an error. An empty cell compared with text is the empty text.
Text that reads as a number is a number. The warehouse often sends numbers as text (2,000 or 0.00). "12" + 1 is 13, and [net revenue] = 0 is true for a value that arrives as "0.00". Two texts still compare as text, so the codes "007" and "7" are different.
Other text in arithmetic is an error. [product] * 2 is #VALUE!.
TRUE is 1 and FALSE is 0 in arithmetic.

Operators

From the one that binds tightest to the one that binds loosest. Operators at the same level work from left to right, and brackets override the order.

Operator	What it does	Example	Result
`%` after a value	Divides by 100	`50%`	0.5
`-` or `+` before a value	Sign	`-[cost]`	-700
`^`	Power	`2^3`	8
`*` and `/`	Multiply and divide	`[cost] * 10%`	70
`+` and `-`	Add and subtract	`[net revenue] - [cost]`	500
`&`	Joins text	`[product] & " (" & [cost] & ")"`	chai (700)
`=` `<>` `<` `>` `<=` `>=`	Compare, giving TRUE or FALSE	`[net revenue] > 1000`	TRUE

As in Excel, a sign binds tighter than a power, so -2^2 is 4 and not -4.

Comparing

Two numbers compare as numbers, even when one or both arrived as text.
A number and an empty cell compare as if the empty cell were 0, so IF([net revenue] = 0, ...) is true on a row with no revenue. That is the guard people write against dividing by zero, and it works.
Two texts compare without regard to case: [product] = "CHAI" is TRUE on the chai row, and < and > compare alphabetically.

Functions

Function	Arguments	Reads a whole column
`SUM`	One or more	Yes
`AVERAGE`	One or more	Yes
`MIN`	One or more	Yes
`MAX`	One or more	Yes
`COUNT`	One or more	Yes
`COUNTA`	One or more	Yes
`ROUND`	1 to 2	No
`ROUNDUP`	1 to 2	No
`ROUNDDOWN`	1 to 2	No
`ABS`	1	No
`INT`	1	No
`MOD`	2	No
`POWER`	2	No
`SQRT`	1	No
`IF`	2 to 3	No
`IFERROR`	2	No
`AND`	One or more	No
`OR`	One or more	No
`NOT`	1	No
`ISBLANK`	1	No
`ISNUMBER`	1	No
`LEN`	1	No
`UPPER`	1	No
`LOWER`	1	No
`TRIM`	1	No
`CONCAT`	One or more	No
`TEXT`	1 to 2	No
`VALUE`	1	No
`LEFT`	1 to 2	No
`RIGHT`	1 to 2	No

Column summaries: sum, avg, min, max, count. Formats: auto, integer, number, percent, currency, compact, text.

The functions marked as reading a whole column are the six aggregates. Functions that take any number of arguments (SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, AND, OR and CONCAT) take at least one. The sections below say what each does.

Whole-column functions

Function	Returns	Example	Result
`SUM(x, ...)`	The total of the numbers	`SUM([net revenue])`	4,000
`AVERAGE(x, ...)`	The mean of the numbers. `#DIV/0!` when there are none.	`AVERAGE([net revenue])`	1,333.33
`MIN(x, ...)`	The smallest number, or empty when there are none	`MIN([net revenue])`	800
`MAX(x, ...)`	The largest number, or empty when there are none	`MAX([net revenue])`	2,000
`COUNT(x, ...)`	How many numbers	`COUNT([product])`	0
`COUNTA(x, ...)`	How many values are not empty	`COUNTA([product])`	4

These read numbers only and skip anything else, so AVERAGE([net revenue]) divides 4,000 by 3: the empty ikura cell is left out, not counted as 0. Give an aggregate two columns and it reads both in full: SUM([net revenue], [cost]) is 6,100. A value that is not a bare column, such as SUM([cost], 10), is worked out for the row and counted once.

Rounding and arithmetic

Function	Returns	Example	Result
`ROUND(x, [places])`	`x` rounded, halves away from zero. `places` defaults to 0 and may be negative.	`ROUND(1.005, 2)`	1.01
`ROUNDUP(x, [places])`	`x` rounded away from zero	`ROUNDUP([net revenue] / 7, 1)`	171.5
`ROUNDDOWN(x, [places])`	`x` rounded towards zero	`ROUNDDOWN(-2.349, 2)`	-2.34
`ABS(x)`	The size of `x`, without its sign	`ABS([cost] - [net revenue])`	500
`INT(x)`	`x` rounded down to a whole number	`INT(-1.5)`	-2
`MOD(x, d)`	The remainder of `x` divided by `d`, with the sign of `d`. `#DIV/0!` when `d` is 0.	`MOD(-7, 3)`	2
`POWER(x, y)`	`x` to the power `y`	`POWER(2, 10)`	1,024
`SQRT(x)`	The square root. `#NUM!` for a negative.	`SQRT(16)`	4

ROUND works on the number as written, so it agrees with Excel where plain arithmetic would not: ROUND(1.005, 2) is 1.01, ROUND(-2.5, 0) is -3 and ROUND(1234.5, -2) is 1,200. ROUND(x) with no second argument rounds to a whole number.

Logic

Function	Returns	Example	Result
`IF(test, then, [else])`	`then` when `test` is true, otherwise `else`, or FALSE when `else` is left out	`IF([net revenue] > [cost], "profit", "loss")`	profit
`IFERROR(x, fallback)`	`x`, unless working it out gives a formula error, then `fallback`	`IFERROR([cost] / [net revenue], "n/a")`	0.5833 (and n/a on `ikura`)
`AND(x, ...)`	TRUE when every argument is true	`AND([net revenue] > 1000, [cost] < 800)`	TRUE
`OR(x, ...)`	TRUE when any argument is true	`OR([net revenue] > 1500, [cost] > 800)`	FALSE
`NOT(x)`	TRUE when `x` is false	`NOT([net revenue] > 1000)`	FALSE
`ISBLANK(x)`	TRUE when `x` is empty	`ISBLANK([net revenue])`	TRUE on `ikura`
`ISNUMBER(x)`	TRUE when `x` is a number, or text that reads as one	`ISNUMBER("12")`	TRUE

A test counts as true when it is TRUE, a number other than 0, or text that is not the number 0. It is false when it is FALSE, 0 or empty.

IF only works out the branch it returns, so IF([net revenue] = 0, 0, [cost] / [net revenue]) never divides by zero. IFERROR rescues #DIV/0!, #VALUE! and #NUM!. It cannot rescue #NAME?, because a formula that cannot be read is never run.

Text

Function	Returns	Example	Result
`LEN(x)`	The number of characters	`LEN([product])`	4
`UPPER(x)`	Upper case	`UPPER([product])`	CHAI
`LOWER(x)`	Lower case	`LOWER("Chai")`	chai
`TRIM(x)`	Removes leading and trailing spaces and squeezes runs of spaces to one	`TRIM(" a b ")`	a b
`CONCAT(x, ...)`	The arguments joined as text	`CONCAT([product], ": ", [cost])`	chai: 700
`LEFT(x, [n])`	The first `n` characters. `n` defaults to 1.	`LEFT([product], 3)`	cha
`RIGHT(x, [n])`	The last `n` characters. `n` defaults to 1.	`RIGHT([product], 2)`	ai
`TEXT(x, format)`	A number written as text with thousands separators	`TEXT([net revenue], "0.00")`	1,200.00
`VALUE(x)`	Text read as a number. `#VALUE!` when it is not one.	`VALUE("1,200")`	1,200

TEXT reads only how many zeros follow the decimal point in its format. "0.00" means two decimals and "0" means none. It always adds thousands separators, and it does not understand percent, currency or date formats. Text that is not a number comes back unchanged. To show a value as a percent in a column, set the column's format instead.

Errors

A formula that cannot be worked out on a row shows an error word in that cell, and the rest of the table carries on. Errors are Excel's words.

Cell shows	Meaning	Typical causes
`#DIV/0!`	Divided by zero	`[cost] / [net revenue]` on a row with no revenue. `MOD(x, 0)`. `AVERAGE` of no numbers.
`#VALUE!`	A text value where a number was needed	`[product] * 2`. `VALUE("abc")`.
`#NUM!`	Not a real number	`SQRT` of a negative. A result too large to hold.
`#NAME?`	The formula cannot be read at all	An unknown column or function, a missing closing bracket or quote, or the wrong number of arguments

A #NAME? fills the whole column, marks the column's header with a red !, and the first problem is written under the table after the column's name. Some of the sentences you may see:

Under the table	What to fix
there is no column called “units”	Spell the column as its header shows it
FOO is not a function this sheet knows	Check the function's name against the list above
SUM is missing its closing )	Close the bracket
a column reference needs a closing ]	Close the square bracket
a text value needs a closing quote	Close the double quote
the formula ends too soon	Something is missing after an operator

IF and IFERROR are how you handle the first three. A #NAME? is always a mistake in the formula itself.

Number formats

A format changes how a value is shown and never the value itself, so a formula that reads a formatted column sees the underlying number. Use ROUND to change the value. A column's format is set from its menu on the table.

Format	Value	Shown as
automatic	1234.567	1,234.57
whole number	1234.567	1,235
number	1234.567	1,234.57
percent	0.256	25.6%
currency	1234.5	$1,234.50
compact (1.2M)	12500	12.5K
compact (1.2M)	1250000	1.3M
text	1234.567	1234.567

Detail worth knowing:

percent multiplies by 100, so it expects a ratio. 0.256 shows as 25.6%, and 25.6 shows as 2,560.0%.
compact leaves numbers under 10,000 in full (9,500 stays 9,500) and shortens larger ones to K, M or B.
automatic leaves text alone and keeps a value such as 00123 as text, since a code with leading zeros is not a number.
Numeric formats right-align the column.
TRUE and FALSE always show as TRUE and FALSE.

In the exported workbook

When you download a table as xlsx, each formula column is written as a formula in Excel's own grid. [net revenue] - [cost] on the first data row of the sample table becomes =(B2-C2), and [net revenue] / SUM([net revenue]) becomes =(B2/SUM(B$2:B$5)), with the whole-column reference turned into a fixed range over every data row. Excel recalculates it, so a formula column keeps working after you change a number in the file. A column you hid on the report is written into the workbook as a hidden column, so a formula that reads it keeps its cell reference and keeps calculating.

Limits

Limit	Value
Formula columns on one table block, hidden ones included	12
Length of one formula	500 characters
Length of a column's name	60 characters
Rows a formula can see	The rows the block returned, at most 200

What a formula cannot do: refer to cells such as A1, look things up (VLOOKUP), add conditionally (SUMIF), work with dates, or keep a running total. A formula sees only the rows of its own table. If you need a shaped number in more than one report, define it as a metric so every report gets it.

Worked examples

Margin, and margin as a percent of revenue. Add two columns. The second reads the first.

Text

margin        [net revenue] - [cost]
margin %      IF([net revenue] = 0, 0, [margin] / [net revenue])

Set margin % to the percent format. On the chai row it shows 41.7%, and on ikura, which has no revenue, 0.0%.

Each row's share of the total.

Text

share         [net revenue] / SUM([net revenue])

With the percent format the sample table reads 30.0%, 20.0%, 50.0% and 0.0%.

Flag the rows that need a look.

Text

status        IF([cost] > [net revenue], "loss", "ok")

Text results are left-aligned like any other text column.

A tidy label.

Text

label         UPPER(LEFT([product], 3)) & " · " & TEXT([cost], "0")

On the chai row this reads CHA · 700.

A ratio that never shows an error.

Text

cost ratio    IFERROR([cost] / [net revenue], 0)

On ikura this is 0 instead of #DIV/0!.

Something unclear or out of date? Tell us, and we will fix the page.