Estimating with Spreadsheets

I have been surprised to discover many of my customers and students do not use spreadsheets. As estimators, we deal with numbers all day (and night) long. Doesn’t it make sense to embrace a tool that can automate every mathematical task you perform? With that in mind, I decided to write this article for those of you who are unfamiliar with the capabilities of a spreadsheet. Just using basic features, you can replicate and automate every estimating form you use, and many you may not have thought of yet.

Microsoft Excel is currently the most popular spreadsheet program, so I will use its functionality in my examples. If you own Excel, you may want to open it and follow my examples. The picture below is of part of a spreadsheet. When you open Excel, it will display a blank spreadsheet similar to the picture. The spreadsheet is organized into columns and rows. The columns are labeled alphabetically across the top. I just checked my version of Excel, and it went all the way to column XFJ. That’s more columns than I will ever use. The rows are labeled numerically down the left side of the spreadsheet. My spreadsheet has 1,048,576 rows, which again, is more than I will ever use.

The intersection of a column and a row is called a cell. The cells are identified by the column and row they intersect, such as B3. Now here is the cool part. Each cell can hold text, dates, numbers or formulas, and the formulas can reference numbers in other cells.

Here is an example of a simple formula. I typed 10 into cell A1, 15 into cell A2 and 20 into cell A3. In cell A5, I typed +A1+A2+A3. The spreadsheet did the math and displayed the result. If I change any of the numbers in cells A1, A2 and A3, the formula in A5 will automatically recalculate and display the new total. An important rule here is most formulas must start with a mathematical operator, like =, +, -, * or /. In case you don’t know, the asterisk is for multiplication, and the forward slash is for division.

Are you starting to see the possibilities? Let’s take it a step further. Another thing you can put in a cell is a function. Functions automate or simplify more complex tasks. In my example above, another way to add up those numbers would be the SUM function. In cell A5, I typed =SUM(A1:A3). I started by typing the equal sign, since formulas must start with a mathematical operator. Then I typed the function name, followed by what Excel calls the argument. The argument is what happens between the parentheses. In the argument I typed, a range of cells is specified for the sum function to add up. You will see the same result as before, but this function has an advantage. You can add up hundreds of cells with one short formula, instead of typing hundreds of cell references. There are many functions available. However, the basic functions such as sum, average and count are all you need to build many spreadsheets.

Something important to think about when creating formulas is a mathematical concept called precedence. Simply put, math operations are evaluated in the following order: multiplication and division, and then addition and subtraction. If you have both multiplication and division in a formula, the spreadsheet will evaluate them left to right. Here is a simple example. Type the following into a cell; =5+2*3. The result is 11, because the spreadsheet evaluated the multiplication before the addition. If you really meant that you wanted the addition done first, you can control the order of evaluation with parentheses. Change your formula to =(5+2)*3. Now, the spreadsheet evaluates the operation inside the parenthesis first, giving you a result of 21. The complete order of precedence is also known by the mnemonic acronym PEMDAS, which stands for parenthesis, exponents, multiplication, division, addition, subtraction.