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

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