Formula columns are a vital type of Custom Column. They let you define a formula that gets evaluated and returns a value. Formulas work similarly to how they work in other spreadsheet applications, but with a few differences:
- You can only refer to other cells in the same row.
- Formulas are written in JavaScript notation.
You can add a formula column from the pipeline view. Clicking into the cell will open a larger textbox for you to write JavaScript in.
When finished, use ⌘/Ctrl + Enter to save the formula.
Formula scoping
By default, the formulas in a column are the same for every Box in your Pipeline. You can customize formulas for each stage of the Pipeline, or even have a formula specific to one Box, too.
- Entire Column (Default) – the formula will execute for every Box in the Pipeline
- Just this stage – overrides a formula that is defined on the entire column. The formula will only execute for the Boxes that are in the particular stage.
- Just this Box – overrides stage scoped formulas and column scoped formulas. The formula will only execute for this particular Box.
Referencing a column
To reference a column in a formula, the syntax is:
$'Column Name'
For example, a "Deal Size" formula column can be a product of two freeform columns:
= $'Number of Units' * $'Price per Unit'
Requirements for formulae
Formulas should always include an explicit return statement. Simple formulae like the two above will still resolve, but more complex versions will be unable to present an answer without one.
An example of this can be found in a conditional formula:
= if($'Number of Units' > 5) {
return 'true';
}
else {
return 'false';
}