User Defined Functions in Excel
User defined functions are a feature of Excel that is utilized in the Estimating Workbook. These custom functions allow for faster calculation of various estimating cost items.
User Defined Functions Found in the Estimating Workbook
| CalcQuantity | Calculates a value while accounting for waste factor. |
| LFQuantity | Calculates LF of a material given length. Accouns for waste factor and rounds to the nearest whole unit. |
| SFQuantity | Calculates SF of a material given area. Accounts for waste factor and rounds to the nearest whole unit. |
| StudCount | Calculates the number of studs required for a wall, given length, plates, stud spacing, windows and doors. Accounts for waste factor. |
| Wall_Sheathing | Calculates the SF of wall sheathing, given wall length, sheathing height, number of sides, and number of corners. Accounts for waste factor and rounds to the nearest whole unit. |
| WallPlates | Calculates the quantity of wall plates required given the number of plates used in the wall construction and wall length. Accounts for waste factor and rounds to the nearest whole unit. |
Advantages of User Defined Functions in Construction Estimating
- Allows a complex formula to be used in multiple cells without having to manually enter it in every cell.
- Speeds up the process for calculating specific outputs by separating the variables from the formula.
- Requires a material to be entered in the correct cell in order for the output to be calculated.
Examples of User Defined Functions in the Estimating Workbook
Example 01: Use CalcQuantity to estimate the quantity of material required for the Catwalk in the Truss Roof Framing.
1. Identify the material type by looking at the plans. Enter that material in the Estimating Workbook -> Framing Tab -> Truss Roof Framing Material -> Catwalk Row -> Materials Cell.
2. Enter the Size and Units information in the Estimating Workbook, next to the Material.
3. Determine how many feet are required by completing a takeoff from the plans using Bluebeam. In this case, there are four different catwalk lengths measuring at 14.5', 23.5', 23.5', and 12', respectively.
4. Select the Quantity cell in the Catwalk row of the Framing tab of the Estimating Workbook.
5. Click on the Insert Function icon in the menu bar, and search for the CalcQuantity function in the Formula Builder Tool.
6. Select the cells the function should reference for Material, WasteFactor, and Size in the the Formula Builder. Also enter the four lengths of the catwalks from the takeoff (14.5+23.5+23.5+12). Click Done.
The four catwalk lengths add up to 73.5'. The Waste Factor in this case is 0, and the material is purchased in 1 SF increments. Taking all of these variables into account, the CalcQuantity function returns an estimated quantity of 74.
Example 02: Use StudCount to estimate the number of studs required for the bearing walls in a basement .
StudCount Example Video Walkthrough, 2:18 mins
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/construction_estimat/user_defined_functio.