• 1. Estimating Basics
  • 2. Construction Estimating Tools
  • 3. General Estimating Guides and Aids
  • 4. Assignment Walkthroughs
  • 5. Construction Materials and Process Index
  • Who Hires Construction Estimators?
  • Translations
  • 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

    List of User Defined Functions 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

    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"x6" Material is used for the catwalk
    2"x6" Material is used for the catwalk

    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.

    Plans with four different lengths of 2"x6" catwalk identified
    Plans with four different lengths of 2"x6" catwalk identified

    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.

    Excel menu bar with the insert function icon next to the formula bar
    Excel menu bar with the insert function icon next to the formula bar

    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.

    Formula Builder Tool with CalcQuantity function
    Formula Builder Tool with CalcQuantity function

    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.