Formula referenceReference
Everything a Formula_ formula can contain, in one place. Only what is listed here runs — a formula can read its own record and nothing else, so a workbook uploaded by a colleague can't do anything unexpected.
| You can write | How it works |
|---|---|
| Arithmetic | + - * /, plus // (divide and drop the remainder), % (the remainder itself) and ** (power). Use parentheses to group: (a + b) * c. |
| Other fields | Write field names as they are: hotel + meals. A line formula can also use a header field by saying where it lives — rate from HEADER or HEADER.rate. If a line field and a header field share a name, the line's own value wins. |
| Line-item roll-ups | sum of <column> (or sum(<column>)) totals a line-item column into a header field; count of counts the filled rows and average of averages them. When several grids share a column name, say which one you mean with from <SECTION> — or write PART_LINES.amount (underscores stand in for spaces, any capitalization works). Roll-ups only work on a form that has line items; a number across many records belongs on a dashboard, and such a row is skipped with a warning. |
| Comparisons | > >= < <= = != — a single = means "equals". Each comparison answers true or false. |
IF(condition, a, b) | The decision-maker. Returns the second value when the condition is true, otherwise the third. Nest one inside another for more cases: IF(t > 200, "A", IF(t > 100, "B", "C")). Only the branch that gets chosen runs, so IF(qty > 0, total / qty, 0) never divides by zero. |
| Combining conditions | Join conditions with AND / OR between them (hotel > 100 AND meals > 20), or as functions: AND(…), OR(…), NOT(x). |
| Functions | abs, round, min, max, sum, plus len (length of a text) and int, float, str to treat a value as a whole number, a decimal, or text. |
Worked examples. The formula goes in the Formula column; the Output Field column names the read-only field it fills.
| Output field | Formula |
|---|---|
| line_total | quantity * unit_price |
| grand_total | sum of amount |
| mileage | miles_driven * mileage_rate from HEADER |
| band | IF(total > 100, "High", "Low") |
| tier | IF(t > 200, "A", IF(t > 100, "B", "C")) |
| follow_up | IF(total > 100 AND paid = "no", "Yes", "No") |
A formula can answer with a number (rounded to 2 decimals), text (IF(hotel > 100, "High", "Low")), or true / false — a true/false answer on a checkbox field shows as Yes / No.
Name the sheet
Formula_, Formulas_, or Logic_ — Formula_ is the standard spelling. If a formula can't be read, the app never guesses: it skips that row and tells you with a build warning, so a typo can't turn into a silently wrong total.