Using Formulas - Formulas in forms: Introduction

Formulas are used to do calculations using values from fields of other controls. They work like formulas in Excel. 

See also: Formula Field Referencing, List of Formula Functions

Formula fields can be added to your forms from the template editor:

You can click on the cog to open the Settings panel for a formula field, for example:
 - Give the field a Display Name
 - Edit the formula
 - Show formula refs (that is, show the cell references alongside each field in the template - see below)
 - Hide the field (so field users don't see the formula - this can be useful for intermediate calculations, or calculations that are needed for Exports).

See below example of a formula field that adds two numbers together, while showing formula references along the left of the screen:

Note that, unlike Excel, you do not need to include the “=” sign (i.e. the correct formula is “A+B”, not “=A+B”.

Types of values

There are three types of values that are recognised by formulas:

• Numbers, which can be references to Number fields, or else numeric constants like 1, 2.5, 3004.0, etc. For example: A + B (where both A and B refer to numeric fields), 100 + 1, A + 100 (where A refers to a numeric field)
• Times represent the time elapsed since midnight. These can be references to Time fields, or else time constants like 08:00, 17:30, etc. For example: B – A (where both A and B refer to Time fields), 17:30 – 08:00, A – 12:00 (where A refers to a Time field)
• Strings, which are everything that is not a Number or Time (most commonly, these would be string constants or Text Fields. For example, the following uses the IF function to display different strings based on whether a number is zero or not:  IF(A = 0,"No","Yes")

Mixing types in expressions

• Time and Number: This will attempt to treat the number as a time. The value of the number is treated as a number of days. E.g. 1 = 24 hour, 0.5 = 12 hours, etc. So “08:00 + 0.5” should become “20:00”.
• Time and String: This will attempt to convert the string to a time (and will return an error if this cannot be done).
• Number and String: This will attempt to convert the string to a number (and will return an error if this cannot be done). E.g. using 0+A in a numeric formula (instead of A) helps to handle situations where a user inputs a non-numeric entry for A.