Working with Item Formulas in Assemblies

En Español

Each item in an assembly has an Item Formula. The Item Formula is the calculation STACK will perform to quantify the item and convert the Takeoff Variable into the Purchase Unit of the item. It basically represents how you normally calculate the item. 

By default, the item’s assigned Coverage Rate is used for the Item Formula, but more complex formulas can be created to allow for real life variations (multiple layers, thickness, etc.). Formulas are created/edited in the Formula Editor.

See below for more info:

Formula Editor

• Variables
  • Takeoff Variables
  • Custom Variables
• Functions
• Create Formula
• Additional Formula Information
  • Operators
  • Order of Operations
• Test Formula
  

Formula Validation

Data Representation of Measurement Types

• Area
• Linear
• Count
• Linear with Drops
• Pitched Area
• Pitched Linear
• Surface Area
• Volume 2D
• Volume 3D

Formula Editor

The Formula Editor is where you can override the default Coverage Rate formula for an item in an assembly with a custom Item Formula. You access the Formula Editor from the Assembly Information screen - just click the edit (pencil) icon next to the Item Formula that you want to change.

This is the layout of the Formula Editor screen and descriptions of the different components:

Variables

The VARIABLES tab displays a list of Takeoff Variables you can use in your formulas but you can also create your own Custom Variables by clicking + Create New Variable at the top of the list. The new variable will be saved to the list for future use.

Click the + next to any of the variables on the list to add it to the Create Formula workspace.

NOTE: The Formula Editor will not allow you to create a custom variable that exactly matches a STACK provided variable.

TIP: When naming a Custom Variable, you cannot use special characters* or have spaces between words, so we recommend capitalizing the first letter of each word.  

We also recommend specifying how the variable value will need to be entered as part of the name. E.g., Include ‘Inches’ in the name if the value should be entered in inches. 

*The names will automatically be wrapped in brackets [ ] when saved.

Takeoff Variables

Takeoff Variables represents a data point as measured on the plan sheets. The data each Takeoff Variable represents depends on the chosen measurement type. See Data Representation of Measurement Types below for more info.

Takeoff Variable names begin with "Measured" in the variable list:

• [MeasuredArea]
• [MeasuredLinear]
• [Measured Count]
• [MeasuredLinearWithDrops]
• [MeasuredDropCount]
• [MeauredPitchedArea]
• [MeasuredPitchedLinear]
• [MeasuredSurfaceArea]
• [MeasuredVolume2D]
• [MeasuredVolume3D]
• [MeasuredHeight]
• [MeasuredWidth]

TIP: Refer to Takeoff Measurement Types to see what each measurement type provides and examples.

Custom Variables

Custom Variables represent the specific or unique conditions you want expressed in the formula.
E.g., The height of the wall in feet can be expressed as [WallHeightInFt], or the number or layers can be expressed as [NumberOfLayers]). 

Custom Variables are specific to the assembly. You will enter their values when the assembly is added to a takeoff in the project.

Functions

The FUNCTIONS tab lists the different STACK-supported Excel functions that you can use when creating a custom formula. 

Click the + next to any of the functions on the list to add it to the Create Formula workspace.

See Excel Functions in Formulas for more info and examples of the functions.

Create Formula

The Create Formula/Formula Editor workspace is where you will actually create your formula. Variables and Functions that you add will automatically populate in the workspace, but you can type in information and copy/paste info to get your formula ordered/formatted appropriately.

The formula you create entirely depends on the item. Remember, the goal of the formula is to convert a Takeoff Variable into the Purchase Unit of the item. 

NOTE: The formula of an item will be executed for every measurement you draw, then added together and displayed on the Item Reports and Estimates.

Additional Formula Information

Operators

These are the operators you can use in your formula.

Order of Operations

STACK uses standard mathematical practices for the order of operations, also known as PEMDAS (“Please Excuse My Dear Aunt Sally”):

1. Parenthesis
2. Exponents
3. Multiply
4. Divide
5. Add
6. Subtract

TIP: To learn about other ways to convert data, see Mastering Custom Formulas

Test Formula

Once you have entered a custom Item Formula, you'll want to test it to make sure it calculates as expected. 

1. Click on Test Formula.
2. Enter values in variable fields. 
3. Click Test and confirm that the Calculated Value is correct.

Once you have tested and confirmed the accuracy of the formula, you can save it.

Formula Validation

STACK follows standard mathematical practices when calculating a value:

• Invalid Formula:

Occurs when the formula is not complete; either a parenthesis was missed or added, or an operator, such as +, has been added by mistake. Verify the formula is complete before continuing.

• Invalid Variables:

Occurs when the formula editor does not recognize one of more variables used in the formula. Verify the correct spelling of each variable used in the formula matches the variable listed under the Variables header. If the variable does not exist, create the new variable.

• This Cell Contains an Invalid Value:

Appears on the item-based reports and on the Material and Labor Estimate and occurs when:
1. A custom variable is used in the denominator of a formula AND
2. The custom variable field of the assembly, under the takeoff, is set to zero '0' resulting in a calculated value that is undefined. If the item has been used in several takeoffs or assemblies, ANY calculation for the item that results in an undefined value will cause the error to be displayed. Find and resolve the custom variable of the affected takeoff and assembly.

TIP: Use the Item Cost by Takeoff Report to find the affected takeoff. Then edit the assembly added.

TIP: If you are unsure which variable is causing the issue, look at the assembly in the Assemblies Library. The custom variable will be listed in the formula of the affected item.

Data Representation of Measurement Types

While a Takeoff Variable name doesn't change, what it represents depends on the chosen measurement type.

Area

Linear

NOTE: [MeasuredHeight] and [MeasuredWidth] do not apply to polygonal shapes.

Count

Linear with Drops

Pitched Area

Pitched Linear

Surface Area

Volume 2D

Volume 3D

Related Articles

What is an Item
What is an Assembly
Create a Custom Assembly
Mastering Custom Formulas
Excel Functions in Formulas

If you need additional assistance, please Contact Us.