If-Then Statements In my template, column A (labeled "Avg. cost/hr") holds a formula that divides total cost by total hours. In its simplest form, the formula would look like this:

=D3/E3

The problem with this formula, and with others that use division, is that any time column E is empty or contains a zero, the formula automatically considers this a "divide-by-zero" error. Instead of calculating a number, the formula puts the message #DIV/0! (or ERR! in some versions) in column A. When clients see this — and I know plenty of contractors who have shown spreadsheets to their clients with ERR! all over the place — they begin to wonder about the accuracy of the rest of the numbers. Fortunately, there’s a simple fix using an "if-then" statement. (In Excel, this is called the IF function.) The formula in cell A3 looks like this:

=IF(E3=0,"0",D3/E3)

The formula argument (the part within the parentheses) has three parts, separated by commas: a "logical test," a "value if true," and a "value if false" (Figure 3). Figure 3. When a cell is left blank, a spreadsheets interprets it as a zero. In formulas that use division, this can cause divide-by-zero error messages. To solve the problem, use an if-then statement. The built-in IF function has three parts, separated by commas: a logical test, a value if true, and a value if false. In this example, if the argument E3=0 is true, the cell holding the formula will display a zero (or any other text typed between the quote marks). If the argument is false, the cell will display the result of D3 divided by E3. Without this if-then formula, when cell E3 is zero, the spreadsheet will display an error message. In plain language, the if-then statement first "tests" to see if the value of cell E3 is zero; if it is, the formula will display "0" (or whatever message you type between the quotes); if the value is not zero, then the formula divides D3 by E3 and displays the result. Using this IF function, when the value in Column E is zero, "0" appears in column A instead of "ERR!."

Job-Costing

To estimate accurately, you have to do some sort of job-costing. If you are not using actual costs to update your estimating system, then you are missing a big opportunity to become a more confident estimator. In my company, I want to know on a day-to-day basis where I stand on each phase of every job. If we have gone over budget on the foundation, for example, I want to know as soon as possible so I can work with my people in the field to make up the overrun someplace else in the job. You don’t need accounting software to job-cost; instead, you can set up a spreadsheet that keeps track of all of a job’s invoices, bills, paychecks, and so on, along with a code to indicate which phase of work these costs belong to. For the first four years I was in business, this was the way I kept track of job expenses. The spreadsheet I used was accurate, and it allowed me to have as many categories as I liked. An abbreviated version is shown in Figure 4. Figure 4. In this job-cost spreadsheet, column E calculates percent complete by dividing the cost-to-date (column D) by the budget (column C). But this calculation doesn't bear any relationship to actual progress on the job. The solution is two additional columns: one (G) holds a percent complete estimate; the other (H) uses an if-then statement (yellow cell) to determine whether the job will be over or   under budget. The formula combines two if-then statements, one inside the other. The first handles the divide-by-zero problem; the second calculates dollar amounts. The simplest way to use this job-cost spreadsheet is to use only columns A through E. The "Code" and "Category" columns are the same as those I use for estimates, and I enter amounts into the "Budget" column by hand from my estimate. As invoices and timesheets come into the office, I enter total amounts in column D. Column E holds a formula that calculates the percent complete for each category — it simply divides the Cost-to-Date by the Budget. Column F tells me how much money is left in the budget for each category — it subtracts Cost-to-Date from the Budget. These five columns work well as a quick comparison of estimated costs to actual costs. But the calculated percentage in column E is based on dollars budgeted and dollars spent — it doesn’t bear any relationship to actual progress of the work on the site. For example, in row 5, the spreadsheet tells me that I’ve spent about $11 more for carpentry than I had budgeted. This is no big deal if all of the carpentry work is done. But if there’s still some carpentry work to be finished, I could overrun my carpentry budget by more than $11.

Nested if-then statements.

Columns G and H tie the spreadsheet to what’s actually happening on the job site. I enter the numbers in column G based on site visits and discussions with the crew about how far each category of work has progressed on the job. Column H holds a formula that uses these estimated percent complete numbers to predict whether we’ll come in under budget or run over. The formula is intimidating at first glance, because it "nests" one if-then statement inside another:

=IF(G3=0,"",IF(G3=100,F3,F3-((D3/(G3/100))-C3)))

Everything between the first and second "IFs" is necessary to handle the divide-by-zero problem. The seemingly complex series of equations after the second "IF" actually perform a couple of simple calculations. The first part

G3=100,F3

looks at the percentage I’ve entered and if it’s 100 (meaning that phase of the job is complete), it uses the number from column F to show how much we went over or under budget. If the percentage I’ve entered in column G is less than 100, however, that means work in that category is still going on. The second part of this formula

F3-((D3/(G3/100))-C3

predicts how much money will be left for each category if work proceeds at its current pace. By looking at the dollar amounts in column H, I can tell whether any category is running over budget. To make this easier to see, I’ve set the "Format" menu on the spreadsheet toolbar (Figure 5) to display negative numbers in red. Figure 5. Negative numbers are easier to see if they are displayed and printed in red. This setting can be changed on the format menu, which can also be used to display dollar signs, percentage signs, and time or date notations, among other information. As long as my estimate of percent complete is fairly accurate, I can use this spreadsheet at any stage of construction to evaluate progress and make adjustments to bring the job back in line with my budget. Scott Shelley owns Scott C. Shelley Construction, a remodeling company in Petaluma, Calif.