In Part 2 of this tip, we offer some hints for performing common construction cost estimating calculations in Microsoft Excel, such as the total number of work days in a project and whether a project is over budget. (In Part 1, Expanding Excel for construction: Manipulating text, inserting symbols, we discussed basic keyboard shortcuts for manipulating test and inserting symbols.)

Tip #4: Figuring work days

Suppose you are making a schedule for a project that is supposed to be completed in 100 workdays. You want to know the date the project would be completed, minus weekends and holidays.

You first need to type those holiday dates into some cells making sure the cells are formatted as dates.

1. SELECT the cells
2. RIGHT CLICK and SELECT>FORMAT CELLS
3. CHOOSE>DATE
4. SELECT TYPE (x/xx/xxxx or xx/xx/xxxx)
5. Now ENTER the holiday dates into the cells. For illustration purposes we’ll use cells B15, B16 and B17, and use the Fourth of July, Labor Day and Christmas as the holiday dates.
6. Now FORMAT three more cells with the same DATE TYPE
7. Next ENTER the Start Date for the schedule in one of those formatted cells (B18) (We use 7/1/2008)
8. ENTER the number of days allotted for the project in another formatted cell (B19) (It’s a 100 day project in our example)
9. Finally, in the formatted cell where you want the result to appear (B20) ENTER this formula: =WORKDAY(B18,B19,B15:B17)

The result is 11/20/2008. That will be the date of completion. In this case, Christmas is not deducted since it falls outside the 100 day timeframe.

Now, if you create a list of holiday dates somewhere in your Excel estimating workbook, you can quickly include that anytime you need to use this function. Even if you have 12 holidays in the list and you insert them as an array (B3:B14) in your WORKDAY formula (e.g. =WORKDAY(B18,B19,B3:B14) it will only account for those holidays that fall within the number of days you specify.

Tip #5: Filtering data

As you create lists of materials and work with the list, you will undoubtedly end up getting some duplicate items. Here is a quick way to find those and have a new list created that doesn’t include any duplicates.

1. SELECT all the items in the list (e.g. B23:B27)
2. SELECT: Data>Filter>Advanced Filter
3. SELECT: Copy to Another Location Check Box and Enter the new Location (e.g. B29)
4. SELECT: Unique Records Only Check Box
5. HIT Enter

The list of items appears in the specified location minus any duplicates.

Tip #6: Unleashing the power of If

Whenever you have a budget sheet there are typically three columns -- the line item budgeted amount, the amount of the contract, and the difference between those two. When you set this up with formulas so that the amount of the contract is deducted from the budgeted amount, you often find that the full budgeted amount appears in the difference column until you have an amount in the contract column. However, you want the difference column to stay blank until there is an amount in the contract column; then, when there are amounts in both the budget and contract columns, you want to see the difference in the difference column. You can make this happen by using the IF function.

In the Difference column TYPE this formula:

• IF(cell with contract amount=””,””,cell with Budget amount-cell with Contract Amount)

Here’s how it looks if B36 is the cell with the Contract Amount and B35 is the cell with the Budget amount:

• IF(B36=””,””,B35-B36)

Now you won’t see any amount in the difference column until both the Budget amount and Contract amount cells are populated. Once you set this up in one cell simply copy and paste it to all the other cells in the Difference column.

Let’s say you have a summary sheet you show to others -- but, for confidentiality reasons, you don’t want to have budget and contract amounts showing. Using the IF function you can set it up so a cell next to each budget item reports whether the item is on budget or not.

In the cell where you want the result to be reported TYPE this formula:

• IF(cell with Contract Amount>cell with Budget amount,”Over Budget”,”OK”)

Here’s how it looks if B36 is the cell with the Contract Amount and B35 is the cell with the Budget amount:

• IF(B36>B35,”Over Budget”,”OK”)

Now each line item will be identified as OK or Over Budget, depending upon its status.

Tip #7: Calculating from multiple locations

Suppose you have a construction cost estimating workbook with three sheets -- a summary sheet, a materials sheet and a labor costs sheet -- and you want material and labor costs to be added together for various line items and be reported as totals on the summary sheet.

On the summary sheet:

1. SELECT the cell where you want the total to appear
2. GOTO Sheet 2; SELECT the cell with the material amount and then HOLD DOWN SHIFT and HIT +
3. GOTO Sheet 3; SELECT the cell with the labor amount and HIT ENTER

The total of the two cells appears on the summary sheet.

You can have many variations on this, too -- single operations such as multiplication or division, as well as combinations of operations across a number of sheets.

BONUS TIP: How to move data from one Excel 2003 sheet to another

ConstructionSoftwareReview.com recently blogged about using Microsoft Excel for construction cost estimating -- specifically, How to move data from one Excel 2003 sheet to another. As our post indicates, there are two ways to do this -- either create a formula to perform this task or use an add-in called a dataloader.

*** The third and final part of this tip, Expanding Excel for construction: Saving macros, linking worksheets, shows how macros, linking worksheets and importing and viewing PDF files can make Microsoft Excel a powerful construction cost estimating tool.