Fieldbook lets you set a column to a formula, like spreadsheets—but with some key differences.
Formulas use column names
Instead of cell references like A1 or M1187, Fieldbook formulas use column names.
A formula applies to the whole column
When you set a formula on a column, the same formula applies to the whole column. You don't have to fill the formula down, and there's no way to make an error by not having the same formula throughout a column.
Setting a formula
You can set a formula on a column using the column menu—or, just start typing an equals sign
= like you would in a spreadsheet, and the formula editor will pop right up.
Summary functions at the bottom of a column
A column can have a summary function that displays below it. By default, this is the sum if a column has numeric values, and blank otherwise. You can set it to one of several functions, or turn it off—just click below the column (or find the “Display formula at bottom” option in the menu).
You can compare values with expressions like:
Functions in formulas
You can use functions in formulas and apply them to columns:
See below for a full function reference.
Dates in formulas
Dates can be used in formulas:
You can use if statements in formulas using the pattern:
if condition then value1 else value2
If a sheet has linked columns (see Linking sheets), you can reference data from the linked rows. Just type the name of the linked column, then a dot
., then the name of the column in the linked sheet.
For instance, this Projects sheet has a billing formula that multiplies
hours worked times the rate for the employee:
The rate is looked up from the Employees sheet:
If a sheet has a linked column with multiple links per cell, you can sum, count, average, etc. the linked values. To continue the previous example, this formula in the Employees sheet sums up the hours from all projects the employee is assigned to:
You can see that this is the same as the sum at the bottom of the Hours column on the Projects sub-sheet of the employee's detail page:
Note: The today() function honors the time zone setting on the book.
Rollup functions can be used on linked rows. For instance, suppose we have a sheet of Customers linked to a sheet of Orders. The following formulas could be used in the Customers sheet:
These special functions can be used to force formatting on the output of a formula.
See examples of the isblank() and iserror() functions used with filters here.