Formula Editor Overview

The Formula field enables users to create their own custom expressions. Taking users inputs and calculate results that can be used in other calculations, linked to the assembly's cost items or linked to cost items in the Cost Items tab of the Assembly.

Formulas can be created with numbers, math operators (for instance, + or – for addition or subtraction), variables used to store input values or calculation results, or any number of functions that are build into the Formula Editor. The following formula shows an example of how to calculate the cubic foot volume of concrete in a 10 ft. wide by 15 ft. long by 6.0 in. thick slab on grade. The resulting answer is 75 cubic feet.

To calculate the same volume in cubic yards (there are 27 cubic feet in a cubic yard), the formula can be rewritten as follows. The resulting answer is 2.78 cubic yards.

Variables are placeholders for values that can be changed based upon user input or calculation results and they can be used to simplify a complex formula. Variables require a name that is unique within the Cost Item Assembly and the syntax for referencing a variable in a formula is to enclose the entire variable name in [brackets].

Using the preceding example, a calculation named [VOL_CUFT] determines the cubic foot volume of 75. The second formula then references the value stored in the variable [VOL_CUFT] and divides it by 27 to calculate the cubic yard volume of 2.78.

Variables are also used to store user inputs as described above. In the following example, three inputs are created in the Inputs section of the Cost Item Assembly.

Employing this Cost Item Assembly will prompt the user to provide the values for the width, length and thickness of the concrete slab, those values are stored in the variables named [WDTH], [LNGTH] and [THICK] respectively.

The [VOL_CUFT] calculation in the following example is the same as in the preceding example but replaces the 10 ft., 15 ft. and 6.0 in. values with the variable names, which are to be provided by the user when the Cost Item Assembly is employed.

As in the preceding example, the [VOL_CY] calculation takes the result of the [VOL_CUFT] calculation and divides by 27 to convert the volume from cubic feet to cubic yards.

Functions can be used to expand the power of a formula by performing special types of operations on the formulas values. Functions are used most commonly by the name of the function followed by the values that the function will use to perform the special calculations.

As an example, the Ceiling() function can be used to take the result of a calculation and round it up to the nearest whole number. In using the concrete slab example from above, the calculation [VOL_BUY] will take the result of the [VOL_CY] calculation and round it up from 2.78 CY to 3.00 CY using the syntax Ceiling([VOL_CY]), which represents the amount of concrete one would want to purchase for this work.

Creating valid formulas can be challenging when calculations start to become more complex. Improper referencing of variables, incorrect spelling of functions or invalid mathematical operations are all examples of ways in which a formula expression can be invalid.

When a formula results in an invalid expression it will return a NULL value. A NULL value is displayed using a ? character and will preclude the user from employing the Cost Item Assembly in the job. In the following example, a formula that divides any number by zero generates a mathematically invalid result and is indicated by the ? character.