Skip to content
QuantyDocs
Open Quanty

Formulas and totals

Not every column needs AI. A formula column computes its value from other columns of the same row, for example freight in PLN from freight in EUR, a margin, or days between loading and unloading. It is instant, gives the same result every time and never costs credits. Under the data, the Totals row and any number of formula rows sum up whole columns or the cells you pick.

Before you start

  • Formulas are part of every plan and are free. Only Ask AI for a formula uses AI, so it needs AI to be on for your organization and counts against its AI budget. Without AI: "You can still write the formula yourself and preview it on real rows. Only the drafting is off."
  • Setting a formula changes the column, so it needs edit access to the table.
  • A formula reads values, so file, sub-sheet and document columns cannot be used in it: "File and sub-sheet columns can't be used in formulas".

How a formula works

A formula is one expression that runs for every row: "One expression, run for every row." It reads the other columns of the same row by name:

round(num(row['Fracht EUR']) * 4.3, 2)
  • row['Column name'] reads that column in the same row. The name must match exactly.
  • col('Column name') reads the whole column (every row), for totals and shares: row['Fracht EUR'] / sum(col('Fracht EUR')).
  • num(...) turns a messy value into a number: currencies, thousands separators and both decimal styles ('1.234,56 zł' becomes 1234.56). Use it before you calculate with values typed by people or read by AI.
  • Operators: + - * / // %, comparisons, and, or, not, and A if condition else B. ** is blocked; use power(base, exp).
  • One formula can be up to 2,000 characters long.

A formula belongs to the column, not to one cell: "The formula becomes the column's rule: it runs on every row now and for any new rows." When an input changes, the formula cell changes with it.

Write a formula in the cell bar

  1. Select any cell of the column you want to compute.
  2. In the cell bar above the grid, type = and the expression: "Start with = to compute this column from other columns". The bar says "applies to the whole column".
  3. Press Enter or click Apply. Discard cancels.

Typing a value without = sets just that cell. On a column that was filled by AI, clearing the formula hands the column back to AI. For a long formula, Open larger editor in the bar gives more room; the fx sign opens the formula creator.

The formula creator

The formula creator writes a formula with a live preview on your real rows. Open it with Create formula… in a cell's menu, Formula from selection… with several cells selected, or the fx sign in the cell bar.

The formula creator with a preview.
The Formula creator: Ask AI for a formula, the formula round(num(row['Fracht EUR']) * 4.3, 2), the columns and functions to insert, the preview with results on real rows, Fill column and Apply to
  1. Write the formula in Formula, or describe it in Ask AI for a formula (for example "gross margin: (revenue minus cost) divided by revenue, as a percent") and click Generate. The draft is checked before you see it, so it always runs.
  2. Click a column under Columns to put row['…'] into the formula, or a function under Functions (Search functions… finds one). Columns you had selected in the grid are listed first as Selected columns.
  3. Read the Preview: the result for real rows, "5 of 24 rows". "Some rows fail. Check the formula" points at rows where it breaks.
  4. Under Fill column, pick the column to fill, or Create new column…: give it a name and choose Number or Text.
  5. Under Apply to, choose Whole column (the formula becomes the column's rule) or Selected cells (writes the results into those cells only, once).
  6. Click Apply to column or Apply to 3 cells. "Formula set: the column is recomputing", or "Formula applied to 3 cells".

Cells locked as ground truth and cells that are still processing are skipped and keep their values ("2 cells were skipped or failed").

You can also make a formula column from the start: in Add column, choose a value type and set How this column is filled to Formula: "Computes from other columns: deterministic, no credits spent" (see Columns and AI).

Functions

GroupFunctionWhat it doesExample
Mathnum(x)Parses a messy value into a number.num('$1,240.50') gives 1240.5
Mathsum((a, b, …)), sum(col('X'))Adds up numbers; skips empty values.sum((1, 2, 3)) gives 6
Mathavg(a, b, …) (also mean)Average; missing values are skipped.avg(10, 20, 30) gives 20
Mathmedian(a, b, …)Middle value.median(1, 5, 100) gives 5
Mathcount(a, b, …)How many arguments are numbers.count(1, 'x', None) gives 1
Mathfilled(a, b, …)How many values are not empty, also text.
Mathmin(a, b), max(a, b)Smallest, largest.max(3, 7) gives 7
Mathround(x, digits)Rounds to the given decimals.round(3.14159, 2) gives 3.14
Mathabs(x), sqrt(x), floor(x), ceil(x), power(base, exp)Absolute value, square root, round down, round up, power.power(2, 10) gives 1024
Textconcat(a, b, …)Joins values into one text; missing values become empty text.concat('FY', 2021) gives 'FY2021'
Textupper(s), lower(s), title(s)Uppercase, lowercase, Title Case.upper('tesla') gives 'TESLA'
Textstrip(s)Removes spaces at both ends.
Textreplace(s, old, new)Replaces every occurrence.replace('a-b', '-', ' ') gives 'a b'
Textcontains(s, part), startswith(s, p), endswith(s, p)True when the text contains, starts or ends with the part (case does not matter).contains('Tesla, Inc.', 'tesla') gives True
Textleft(s, n), right(s, n), mid(s, start, n)First, last, or middle characters (mid counts from 1, like Excel MID).mid('Tesla', 2, 3) gives 'esl'
Textsplit(s, sep), join(sep, list)Text to a list and back.split('a, b', ',') gives ['a', 'b']
Textlen(s)Number of characters or list items.len('Tesla') gives 5
Datesyear(d), month(d), day(d)Parts of a date; common formats such as 31.12.2024 are understood.year('2021-12-31') gives 2021
Datesdays_between(start, end)Whole days from start to end; negative when the end is earlier.days_between('2024-01-01', '2024-03-15') gives 74
LogicA if condition else BPicks A when the condition holds, otherwise B.'over' if num(row['Cost']) > 100 else 'ok'
Logiccoalesce(a, b, …)The first value that is not missing (empty or not found).coalesce(row['A'], row['B'], 0)
Logicany((…)), all((…))True when any or every condition holds.all((num(a) > 0, num(b) > 0))
Logicbool(x)True or false; empty text and zero are false.bool('') gives False

The Totals row

The Totals row sits under the data and shows one summary per column.

  1. Click the cell of the Totals row under a column: "Choose a summary for this column".
  2. Pick Sum, Average, Min, Max, Count numbers or Count filled, or None to clear it.
  3. Or type your own formula after =, for example =sum(col('Fracht EUR')): "Type = with a formula: col('Name') reads a whole column. Enter saves."

The row appears once at least one column has a summary. File columns have no summary.

Formula rows

Formula rows are extra rows of calculations above the Totals row, as many as you need. They are not data rows: they never run AI.

  1. Add one with the Add formula row button (Σ) in the toolbar, or Add row, Add formula row. It is labelled ƒx1, the next ƒx2.
  2. Click a cell of the formula row. The cell bar binds to it: the address reads Fracht EUR · ƒx1 (or Σ for the Totals row).
  3. Type =sum, =avg, =min, =max, =count or =filled for a ready-made summary of the column, or any expression. Committing an empty value clears the cell.
  4. Remove the row with the × on its ƒx label (Remove formula row).

Calculate over the cells you choose

  1. Select the data cells you want: a range, or scattered cells with ⌘+click / Ctrl+click.
  2. Click a cell of a formula row and type a formula with sel(), for example =sum(sel()) or =avg(sel()).
  3. Press Enter.

The selected values are written into the formula when you commit, like pasting values, so the result does not change when the selection goes away. Each formula-row cell can hold its own calculation over its own cells.

With a formula-row cell selected, Alt+↑ / Alt+↓ moves its content to the formula row above or below, and ⌘C / ⌘X / ⌘V (Ctrl on Windows) copy, cut and paste between formula-row cells: "Ctrl+C/X/V copy·cut·paste, Alt+↑/↓ move between rows".

No formula needed for a quick sum

Select a few cells and the status bar under the grid shows their Count, Sum, Avg, Min and Max right away (see The grid).

Number format

Right-click a number cell (or a range) and use Number format: 0 decimal places, 1 decimal place, 2 decimal places, Auto decimal places, a Prefix (for example € ) or a Suffix (for example km), and Clear number format. The format changes how the value looks, not the value itself.

Good to know

  • Free and repeatable. A formula never calls the AI and never spends credits; the same inputs always give the same result.
  • Messy inputs. Values read by AI are often text such as "1 240,50 PLN" or "NOT_FOUND". Wrap them in num(...) and guard empty values with coalesce(...) or if … else ….
  • Locked values stay. A cell locked as ground truth is never overwritten by a formula (see Cell details, reviews and history).
  • Sub-sheets. A sub-sheet's own columns can compute per item the same way, at 0 credits (see Column types).
  • The grid: the cell bar, selection and the status bar.
  • Columns and AI: when to use AI instead of a formula.
  • Column types: lookups, rollups and counts that compute across linked tables.