Formula Reference

Formulas in a Calculation field use a small, fixed language: numbers, field names, arithmetic, comparisons, &&, ||, a conditional and thirteen functions. It cannot run code, read anything but the form's own answers, or reach outside the form. The same language runs in the browser and on the server, so both give the same answer. How to add a calculation, and what each field type gives a formula, is on the Calculations page.

Numbers and field names

Numbers are written 12, 0.5, .5 or 1e3. There is no thousands separator and no currency symbol in a formula.

A field is named by its Field Name, bare (price) or in braces ({price}). A name uses the letters a to z (either case), digits and _, and cannot start with a digit. A name with a hyphen or a space in it, such as unit-price, cannot be used in a formula, because unit-price is read as unit minus price. Give the field a name like unit_price instead. The braces do not change this rule, they only make a name stand out.

true and false are numbers 1 and 0 and cannot be used as field names. Names are case-sensitive: Price and price are different fields. Function names are not: round, Round and ROUND are the same.

Text in quotes is not part of the language. A formula works with numbers only, so a Dropdown or Radio Buttons field is read by the selected option's key when that key is a number (10, 2.5), and a key that is not a number makes the formula blank. To price options, use numbers as the keys (see What a formula can read).

Operators

From the loosest to the tightest binding:

Parentheses group as usual: 1 + 2 * 3 is 7 and (1 + 2) * 3 is 9. Operators of the same level work left to right.

There is no ^ for powers (use POW), and no = for equality (use ==).

Only the branch that is actually used is worked out. In IF(qty == 0, 0, total / qty) the division never happens when qty is zero, so it does not make the result blank. The same is true for the right side of && and ||.

The % remainder keeps the sign of the left number: -7 % 3 is -1 and 7 % -3 is 1.

Each result is a floating point number, like in most programming languages, so 0.1 + 0.2 is 0.30000000000000004 inside the formula. You never see that, because the field rounds its result to its decimal places. When a formula compares two decimal results, round both first: ROUND(a, 2) == ROUND(b, 2).

Functions

Every function takes the number of arguments shown. A formula with the wrong number, or with a function name that does not exist, is refused (see Checks when you edit). SUM and AVG take their values one by one. There is no range form, so to add up a group of fields write them out, SUM(a, b, c).

How rounding works

ROUND, PREC, FLOOR and CEIL work on the decimal digits of the number, not on its binary form. That is why ROUND(2.675, 2) and ROUND(1.005, 2) give 2.68 and 1.01, where many programming languages give 2.67 and 1.00. Halves go away from zero, so ROUND(-2.5) is -3.

places can be negative to round to tens, hundreds and so on: ROUND(1234, -2) is 1200. A fractional places is cut to a whole number first, so ROUND(1.26, 1.9) is the same as ROUND(1.26, 1), which gives 1.3.

The field applies its own Decimal Places to the final result in the same way, so you only need ROUND or PREC inside a formula when an intermediate step has to be a rounded amount, such as a tax that is rounded to cents before it is added to the total.

Blank values and errors

A formula gives a number, or it gives nothing. When it gives nothing, the calculation shows blank and stores an empty value. This happens when:

• the formula does not parse (a typo, an unbalanced bracket, an unknown function);
• it divides, or takes a remainder, by zero;
• it takes the square root of a negative number;
• any step gives a number too large to hold, such as POW(10, 400);
• it uses a name that is not a field in the form;
• a field it reads holds text that is not a number, such as abc.

An empty field is not an error. A field that is empty, or a checkbox that is not ticked, counts as 0 in arithmetic, so a + b with a empty and b of 5 is 5. If you want a calculation to show nothing until the visitor has filled something in, use Blank Until Filled (see Calculations).

A formula can be at most 2000 characters long and nested at most 64 levels deep. A formula that goes over either limit is refused when you edit it.

Checks when you edit

The formula editor in the builder checks a formula as you type, with plain messages and the spot marked (see Calculations). Saving checks every formula again on the server. A formula that does not parse, names a field the form does not have, or forms a loop with other calculations is refused. An empty formula is accepted as a draft and shows nothing.

Worked examples

A total with a percentage discount:

total:
  type: pro-calculation
  label: Total
  formula: 'subtotal * (1 - discount / 100)'
  prefix: '$'

With a subtotal of 1525 and a discount of 10, the total is 1372.50.

Quantity, price and tax, with the tax shown as a percentage the visitor picks:

total:
  type: pro-calculation
  label: Total
  formula: 'quantity * price * (1 + tax_rate / 100)'
  prefix: '$'

With 3, 19.99 and 8 this gives 64.7676, which the field shows as $64.77 and stores as 64.77.

A circle's area, rounded to two places:

area:
  type: pro-calculation
  label: Area
  formula: 'ROUND(PI() * radius * radius, 2)'

A discount that only applies above a threshold, and a division that protects itself:

price_each:
  type: pro-calculation
  label: Price each
  formula: 'IF(quantity == 0, 0, total / quantity)'

Moving a Calculated Fields Form calculator

If you have a calculator built with the Calculated Fields Form plugin for WordPress, most of its formulas can move across with small changes. Forms Pro keeps the parts that most calculators use: fieldname1-style names work as they are (give your fields those names if you want to keep the formulas as they are), and IF, PREC, ROUND, SUM, MIN, MAX, ABS, SQRT, POW, FLOOR and CEIL have the same meaning.

This fuel calculator is a tested example. The form has two number fields and three calculations, in a form whose mode is calculator:

title: Fuel Calculator
type: forms-pro
mode: calculator
fields:
  fieldname1:
    type: number
    label: Litres
  fieldname2:
    type: number
    label: Price per litre
  fieldname3:
    type: pro-calculation
    label: Cost
    formula: 'PREC(fieldname1 * fieldname2, 2)'
    prefix: '$'
  fieldname4:
    type: pro-calculation
    label: Tax
    formula: 'PREC(fieldname3 * .27, 2)'
    prefix: '$'
  fieldname5:
    type: pro-calculation
    label: Total
    formula: 'IF(fieldname1 > 100, fieldname3 * 0.9, fieldname3) + fieldname4'
    prefix: '$'
    decimals: 2

With fieldname1 of 40 litres and fieldname2 of 1.659, the page shows a cost of $66.36, a tax of $17.92 and a total of $84.28. With 120 litres at the same price the cost is $199.08, the tax $53.75, and, because the volume is over 100 litres and the 10 percent discount applies, the total is $232.92. Until both number fields have a value the three results stay blank, because a calculator shows nothing until everything a formula reads is filled in. The calculations read each other (fieldname5 reads fieldname3 and fieldname4), and the order they sit in on the page does not matter.

Things to check when you move formulas:

• Only the functions in the table above exist. A function from the original plugin that is not listed, such as a date or text function, is refused when you edit the formula.
• A Number field needs its decimals in validate.step, for example validate.step: 0.01 for a price (see Field reference), or the browser refuses a value like 19.99 before the calculation runs.

Related

• Calculations
• Field reference
• Conditional logic