Skip to main content

Calculate a value with a formula

Add a Formula column that computes a value from other columns, with a preview before you add it. It is free.

Written by Umer Ishaq

A Formula column computes a value from other columns in the same row. It is marked Free.

In the video, chaining a Formula column to another column starts at 0:49.

How do I add one?

  1. Click Add column, then Formula. The panel is titled Transformation › Formula.

  2. Write the formula in the Formula section. It starts with an example: @{col_a} + " " + @{col_b}.

  3. Check the preview. The new column shows a Preview header, and each row shows what the formula returns before you add it.

  4. Click Add column and run 3 rows (the number is your row count).

The Formula panel, titled Transformation then Formula. A box asks What formula would you like to create? and has a Generate button. Below it, the Formula section holds the example @{col_a} +

The Formula panel with the formula @Company profile > employee_count entered, a Referenced columns (1) section, a Free tag and an Add column and run 5 rows button. In the table, a new column headed Preview shows a number in each of the five rows. The Preview column header is outlined in red.

A message appears: "Every row in this column now shows what the formula does. Wrong on a row? Open it and say what you expected."

What can the formula use?

The panel says: "JavaScript, plus IF() VLOOKUP() moment() _.startCase() and firstNonEmpty(). Several lines with a return work too."

How do I refer to a column?

Type @ or /. A list opens with your columns, each showing its first value, and Row ID. Pick the column. It appears as a tag.

The Formula panel with an at sign typed in the Formula box and a list of columns open below it: Company Name, Company Domain, Country, Company profile with 21 fields, and Row ID. Most entries show their first value.

Once a column is tagged, Referenced columns lists it.

Can the app write the formula for me?

Yes. Describe it in the box What formula would you like to create? (the hint is E.g., Get the domain name from the column "Email") and click Generate. The panel says "A formula is generated and applied to every row."

Example

With a column Input holding acme.example, the formula @Input.toUpperCase() returned ACME.EXAMPLE, in a new column called Formula.

Good to know

  • The panel footer shows Free. A formula does not spend from your wallet.

  • Until a formula is written, the panel says "Add a formula before running."

  • A Formula column computes on its own, even when the table's Auto-run is Off. Its header shows a check icon instead of a play button, and its values appear as soon as you add the column.

  • A formula that needs a value shows an error on a blank row. For example, a formula that calls toUpperCase() on a blank value showed "TypeError: cannot read property 'toUpperCase' of null" with a red X. It filled in as soon as the value was typed.

  • To read more about Auto-run, see Run columns safely: Auto-run, test one row, then run the rest.

Related Articles
Did this answer your question?