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.

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. |

- 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.

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.

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.