Appearance
Formula basics for spreadsheet users
If you've written Excel formulas, you already know most of what you need. Sintel BI formulas use a different notation (JavaScript), and this page translates the parts you'll meet in this guide. You don't need to learn JavaScript itself.
Fields instead of cell references
In Excel you write =B2*C2. In Sintel BI you refer to fields by name, inside {[ ]}:
js
{[Hours]} * {[Rate]}You don't type these by hand: click a field in the formula editor's Fields list, or type @ and pick it, and the editor inserts the token for you. There's no = at the start.
<Name> in this guide
Some fields have a generated internal name, such as dynamic columns and aggregated fields. Where this guide writes <Total hours> inside a token, as in {[Tasks_<Total hours>]}, it means "click the Total hours field". Don't type the < > yourself.
Excel to Sintel BI
| In Excel | In Sintel BI | Example |
|---|---|---|
=B2*C2 | {[Hours]} * {[Rate]} | Multiply two fields |
=IF(A2>100, "Over", "OK") | {[Used]} > 100 ? 'Over' : 'OK' | ? : means "if ... then ... otherwise" |
=A2=B2 | {[A]} === {[B]} | Three = signs to compare. !== means "not equal" |
=AND(...), =OR(...), =NOT(...) | &&, ||, ! | {[Hours]} > 8 && {[Status]} === 'Open' |
=A2&" "&B2 | {[FirstName]} + ' ' + {[LastName]} | Join text with + |
=ROUND(A2, 0) | Math.round({[Amount]}) | |
=IF(ISBLANK(A2), "None", A2) | {[Region]} ?? 'None' | ?? gives a default when a field is empty |
=SUM(B:B) | Sum({[Budget]}) | In an aggregated field |
=SUMIF(C:C, "Open", B:B) | SumIf({[Hours]}, {[Status]}, 'Open') | In an aggregated field, see Helpers |
=SUMIFS(...) | Rows.filter twice, then Rows.sum | SumIf takes one condition. For two, see filter twice |
=TODAY() | DateTime.now() | Today's date and time, see Dates |
=TODAY()-A2 | Math.floor(DateTime.now().diff(DateTime.fromISO({[DueDate]}), 'days').days) | Days since a date. See Days overdue for empty dates |
=DATEDIF(A2, B2, "d") | Math.floor(DateTime.fromISO({[EndDate]}).diff(DateTime.fromISO({[StartDate]}), 'days').days) | Days between two dates. Use 'months' and .months, or 'years' and .years, for "m" and "y" |
Text goes in single quotes ('Open'). Upper and lower case matter: 'open' isn't the same as 'Open'.
Empty values: null
A field with no value is null, which you can read as "empty". ?? replaces it with a default:
js
{[Region]} ?? 'Unassigned'Small tests: (x) => ...
Some formulas contain a short test, such as:
js
(status) => status !== 'Done'Read it as "for each value, call it status, and keep it if it isn't Done". The name before => is just a label you choose, and the part after => is the test. You'll see these in SumIf and CountIf and in the advanced recipes.
Where to go next
- The formula editor: where you write formulas and see the result as you type.
- Writing dynamic column formulas: the everyday formulas, with examples from the demo.
- Cheat sheet: every pattern on one page.
Recipes that use .filter, .map, .reduce or several lines of code are advanced. You can copy them as they are; you don't need to understand every symbol to use them.