Formulas in the items table

The items table can carry custom columns whose value is calculated from other columns, the way a spreadsheet cell would be. Formulas are evaluated by BillFlow's own engine over exact decimals; there is no spreadsheet behind the scenes and nothing you type is ever run as code.

Writing a formula#

Open the editor's Formula tab. Its Formula Builder panel lists the calculated columns; Add Formula Column opens the Configure Formula dialog, where you build an expression by picking fields and operators or typing it directly. A formula can use:

• numbers, such as 1, 2.5 or .75;
• fields in square brackets, such as [Quantity] or [Unit Price];
• the operators +, -, *, / and % (remainder), with parentheses;
• comparisons =, <>, <, >, <= and >= inside an IF;
• the functions SUM, AVERAGE, MIN, MAX, ROUND, ABS and IF.

Row fields hold the value in the current row. A column field holds every row's value and only makes sense inside SUM, AVERAGE, MIN or MAX; the engine says so if you use one on its own. An empty cell counts as zero, so a half-filled row never breaks the column.

Try it

Formula playground

Edit the quantities and prices, pick a preset or type your own formula. Results come from the same engine the editor uses.

Formula for the custom column

Fields: [Quantity] [Unit Price] [Amount] [All Amounts]. Functions: SUM AVERAGE MIN MAX ROUND ABS IF.

Line amountWith 7.5% VATBulk discountShare of totalUnknown fieldSyntax error

Description	Quantity	Unit price	Amount	Custom column
First aid box, 20-piece	Quantity, row 1	Unit price, row 1	54,000	54,000
Fire extinguisher 6kg DCP	Quantity, row 2	Unit price, row 2	114,000	114,000
Delivery within Lagos	Quantity, row 3	Unit price, row 3	15,000	15,000

Custom column: 54,000 · 114,000 · 15,000

What the engine refuses#

The engine answers with a message rather than a wrong number when a formula cannot be evaluated: a field that does not exist, division by zero, a value that is not a number, a result too large to be a price, or a function it does not know. It also checks the columns as a set before evaluating any of them: two columns that depend on each other are refused by name, so a cycle can never spin.