Getting Started

Calculated Fields are accomplished by using formulas within the fields you create; text or number field, check box, table, database, etc. A formula performs calculations or other actions on specific data entered in your created fields. Formulas are entered in the Default Value field found in the Field Properties dialog box within a specific template, for the field where the calculation should occur.

For example, if I want 'Quantity' multiplied by 'Price' to equal 'Total', the formula entered in the Default Value for the 'Total' field would look like this, where 'Quantity' and 'Price' are the specific names of the fields in my template:

=[Quantity]*[Price]

If I then want to add the appropriate sales tax based on the locations found in my Sales Tax drop down field, my formula would look like this:

=([Quantity]*[Price])*[Sales Tax]

In this example, I used parenthesis to enforce the order of operation, where Quantity should multiply Price before considering the Sales Tax. More on this can be found in the section 'Using calculation operators in GoFormz formulas'.

A formula always starts with an equal sign (=), which can be followed by numbers, math operators (such as a plus or minus sign), and functions, which can dramatically expand the power of a formula.

Here are some additional examples of formulas that can be entered in a worksheet:

• =[Field 1]+[Field 2]+[Field 3] Adds the values in cells "Field 1", "Field 2", and "Field 3".
• =ROUND([Field 1]) Uses the ROUND function to round the number in "Field 1" to the nearest whole number.
• =UPPER("hello") Converts the text "hello" to "HELLO" by using the UPPER function.
• =IF([Field 1]>0) Tests the field "Field 1" to determine if it contains a value greater than 0.

Using constants in GoFormz formulas

A constant is a value that is not calculated; it always stays the same. For example, the date 10/9/2008, the number 210, and the text "Quarterly Earnings" are all constants. An expression or a value resulting from an expression is not a constant. If you use constants in a formula instead of references to cells (for example, =30+70+110), the result changes only if you modify the formula.

Using calculation operators in GoFormz formulas

Detailed Tutorial here...

Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur (this follows general mathematical rules), but you can change this order by using parentheses.

Types of operators

There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.

Arithmetic operators

To perform basic mathematical operations, such as addition, subtraction, multiplication, or division; combine numbers; and produce numeric results, use the following arithmetic operators.

Comparison operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value—either TRUE or FALSE.

Text concatenation operator

Use the ampersand (&) to concatenate (join) one or more text strings to produce a single piece of text.

Reference operators

Combine ranges of cells for calculations with the following operators.

The order in which GoFormz performs operations in formulas

In some cases, the order in which a calculation is performed can affect the return value of the formula, so it's important to understand how the order is determined and how you can change the order to obtain the results you want.

Calculation order

Formulas calculate values in a specific order. A formula in GoFormz always begins with an equal sign (=). GoFormz interprets the characters that follow the equal sign as a formula. Following the equal sign are the elements to be calculated (the operands), such as constants or field references. These are separated by calculation operators. GoFormz calculates the formula from left to right, according to a specific order for each operator in the formula.

Operator precedence in GoFormz formulas

If you combine several operators in a single formula, GoFormz performs the operations in the order shown in the following table. If a formula contains operators with the same precedence (for example, if a formula contains both a multiplication and division operator), GoFormz evaluates the operators from left to right.

Use of parentheses in GoFormz formulas

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because GoFormz performs multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

In contrast, if you use parentheses to change the syntax, GoFormz adds 5 and 2 together and then multiplies the result by 3 to produce 21.

=(5+2)*3

In the following example, the parentheses that enclose the first part of the formula force GoFormz to calculate [Field 1]+25 first and then divide the result by the sum of the values in the first 3 rows of the first column of a table.

=([Field 1]+25)/SUM([Table]![1][1]:[1][3])

Using functions in GoFormz formulas

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations.

The Syntax of GoFormz functions

The following example of the ROUND function rounding off a number in field "Field 1" illustrates the syntax of a function.

Structure of a function

1. Structure. The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
2. Function name. For a list of available functions, please reference the list on the side of this page.
3. Arguments. Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, or field references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.

Nesting GoFormz functions

In certain cases, you may need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50.

=IF(AVERAGE([Table]![1][1]:[1][5]>50,SUM([Table]![2][1]:[2][1],0)

The AVERAGE and SUM functions are nested within the IF function.

Valid returns When a nested function is used as an argument, the nested function must return the same type of value that the argument uses. For example, if the argument returns a TRUE or FALSE value, the nested function must return a TRUE or FALSE value. If the function doesn't, GoFormz displays a NaN error value or a blank field.

Using references in GoFormz formulas

A reference identifies a field or a table on a template, and tells GoFormz where to look for the values or data you want to use in a formula. You can use references to use data contained in different parts of a template in one formula or use the value from one field in several formulas.

References to Tables and Fields in GoFormz: