-
Print
-
DarkLight
-
PDF
Working with Item Formulas in Assemblies
-
Print
-
DarkLight
-
PDF
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:
Data Representation of Measurement Types
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.
Below is the layout of the Formula Editor screen and descriptions of the different components.
Note that the item name and unit of measure is shown in the top left corner of the screen.
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.
When creating a formula, you can 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 are the listed variables that begin with Measured in their name. Takeoff Variables represent takeoff data as measured on your plan sheets. The data each Takeoff Variable represents depends on the Takeoff's Measurement Type. See Data Representation of Measurement Types below for more info.
A Takeoff Variable's unit of measure is automatically calculated as imperial or metric based on the Item's unit of measure:
- If the Item's unit of measure is imperial (sq ft, lin ft, or cu yd), the Takeoff Variable unit of measure will be imperial.
- If the Item's unit of measure is metric (sq m, lin m, or cu m), the Takeoff Variable unit of measure will be metric.
- If the Item's unit of measure is each (which is not specifically imperial or metric), data will be calculated as imperial by default. If you want the data to automatically calculate as metric, you must manually select Metric as the measurement system you want to use in the Item's library. (This selection is done in the Custom Library that houses the item - LIBRARIES/Settings/Library Details).
TIP: The Item's unit of measure is shown in the top left corner of the Formula Editor screen.
The unit of measure for each Takeoff Variable is listed below.
Takeoff Variable | Imperial Unit of Measure | Metric Unit of Measure |
---|---|---|
[MeasuredArea] | sq ft | sq m |
[MeasuredLinear] | lin ft | lin m |
[MeasuredCount] | each | each |
[MeasuredLinearWithDrops] | each | each |
[MeasuredDropCount] | each | each |
[MeauredPitchedArea] | sq ft | sq m |
[MeasuredPitchedLinear] | lin ft | lin m |
[MeasuredSurfaceArea] | sq ft | sq m |
[MeasuredVolume2D] | cu yd | cu m |
[MeasuredVolume3D] | cu yd | cu m |
[MeasuredHeight] | lin ft | lin m |
[MeasuredWidth] | lin ft | lin m |
Custom Variables
Custom Varialbes 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.
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.
[MeasuredLinear]/10
Convert from Area to 500' Rolls (10'x500') with built-in waste
[MeasuredArea]/5000*(1+([WastePercentage0to100]/100))
Convert from Count to Hour
[MeasuredCount]/[DoorsPerHour]
Additional Formula Information
Operators
These are the operators you can use in your formula.
Symbol | Name |
+ | Add |
- | Subtract |
( | Open Parenthesis |
) | Close Parenthesis |
* | Multiply |
/ | Divide |
> | Greater Than |
>= | Greater Than or Equal To |
< | Less Than |
<= | Less Than or Equal To |
Order of Operations
STACK uses standard mathematical practices for the order of operations, also known as PEMDAS (“Please Excuse My Dear Aunt Sally”):
- Parenthesis
- Exponents
- Multiply
- Divide
- Add
- 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.
- Click on Test Formula.
- Enter values in variable fields.
- 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 Takeoff Measurement Type.
Below lists the different Takeoff Measurement Types, their associated Takeoff Variables, and what measurement data each variable represents for the specific Takeoff Measurement Type.
Area
Takeoff Variable | Represents |
[MeasuredArea] | Area of measurement |
[MeasuredLinear] | Perimeter of measurement |
[MeasuredCount] | Count of measurement |
Linear
Takeoff Variable | Represents |
[MeasuredLinear] | Length of measurement |
[MeasuredCount] | Count of measurement |
[MeasuredHeight] | Screen vertical "height" dimension of a rectangle area. (Don't confuse this with the 3-dimensional height of the space). |
[MeasuredWidth] | Screen horizontal "width" dimension of a rectangle area. |
NOTE: [MeasuredHeight] and [MeasuredWidth] do not apply to polygonal shapes.
Count
Takeoff Variable | Represents |
[MeasuredCount] | Count of measurement |
Linear with Drops
Takeoff Variable | Represents |
[MeasuredLinearWithDrops] | Total length of measurement (vertical and horizontal) |
[MeasuredDropCount] | Count of drops |
[MeasuredLinear] | Horizontal length of measurement |
[MeasuredCount] | Count of measurement |
([MeasuredLinearWithDrops]-[MeasuredLinear]) | Length of drops |
Pitched Area
Takeoff Variable | Represents |
[MeauredPitchedArea] | Pitched area of measurement (calculated from rise and run input) |
[MeasuredArea] | Flat area of measurement |
[MeasuredPitchedLinear] | Perimeter of measurement at pitch (calculated from rise and run input) |
[MeasuredLinear] | Perimeter of measurement |
[MeasuredCount] | Count of measurement |
Pitched Linear
Takeoff Variable | Represents |
[MeasuredPitchedLinear] | Length of measurement at pitch (calculated from rise and run input) |
[MeasuredLinear] | Length of measurement |
[MeasuredCount] | Count of measurement |
Surface Area
Takeoff Variable | Represents |
[MeasuredSurfaceArea] | Surface area of measurement |
[MeasuredLinear] | Length of measurement |
[MeasuredCount] | Count of measurement |
([MeasuredSurfaceArea]/ [MeasuredLinear]) | Wall height input |
Volume 2D
Takeoff Variable | Represents |
[MeasuredVolume2D] | Cubic yardage of measurement (calculated from depth input) |
[Measured Area] | Area of measurement |
[MeasuredLinear] | Perimeter of measurement |
[MeasuredCount] | Count of measurement |
([MeasuredVolume2D]/[Measured Area]) | Depth Input |
Volume 3D
Takeoff Variable | Represents |
[MeasuredVolume3D] | Cubic yardage of measurement (calculated from width and depth input) |
[MeasuredLinear] | Perimeter of measurement |
[MeasuredCount] | Count of measurement |
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.