Section 14.4. Performing Table Calculations
14.4. Performing Table CalculationsExcel tables have several nifty features that help you out when you're performing calculations on the data inside a table. One example's the way that automatic expansion works when you're adding new columns that contain calculations. For example, say you've got a table with three columns : Product ID, Model Name , and Price. If you want to add a new column that tracks the discounted price of each product (say, 90 percent of the regular price), then a table's a great timesaver. Once you've created the new discount price column for one row (see Figure 14-21), Excel fills the same calculation into every other row (Figure 14-22). You wind up with a table that shows the discounted price of every product. No copying and pasting required.
Once you've added a new column with a calculation, the only thing left to do is to give it a good name. Click the column header cell (which will say something like Column1), and then type in something more relevant (like Discount Price). 14.4.1. Dynamic CalculationsOne of Excel tables' nicest features is the way they handle calculations. You can build formulas that use the data in a table, and Excel adjusts them automatically as the table grows or shrinks.
For example, imagine creating a table that stretches from row 1 to row 5. The first row contains the column headers, followed by four product rows. You create the following formula to add the total price of all the items: =SUM(C2:C5) If you now add a new item (that is, a new row) to the table, Excel automatically updates your formula so it includes this new item: =SUM(C2:C6) The same magic happens if your table shrinks because you've deleted an item. You just need to make sure that your range includes the whole column when you first create the formula. For example, consider the following formula, which omits the first item in the table: =SUM(C3:C5) If you expand the table now, Excel doesn't modify the formula. Fortunately, the program's got your back: It'll remind you if you create a formula that includes only a portion of your table by showing a green triangle in the corner of the cell, which is Excel code for "Is this really what you want to do?" When you move to the cell, an exclamation mark icon appears. Click it, and you get a short explanation and a menu of error-fixing options, as shown in Figure 14-23.
Filtering settings don't affect your formulas. If you filter the table so that only some products are visible, the SUM( ) function still produces the same result, which is the total price of all products. If you don't want this behavior, you can use the total row (Section 14.4.4) or Excel's SUBTOTAL( ) function (Section 14.4.5). 14.4.2. Column NamesSo far, the table calculation examples you've seen have ignored a super- convenient feature called column names . You've already learned how Excel lets you create your own named ranges to refer to frequently used parts of your worksheet. (Section 13.2 has the full story.) But even if you don't create any named ranges of your own, you'll find that Excel equips every table with some built-in names. First of all, Excel creates column names that you can use when you write formulas inside your table. For example, look back at Figure 14-21, which uses this formula to discount the current price: =C2*0.9 When Excel copies this formula down the table, it adjusts the formula automatically using the familiar magic of relative references (Section 8.3.1), so that each discounted priced refers to the product price in the same row. For example, in row 10 (technically, the ninth row of data), the formula becomes: =C10*0.9 But there's a shortcut. Instead of referring to the specific cell, you can use the name of the column (which is whatever text you've placed in the column header) inside square brackets. That shortcut means you can write the formula like this: =[Price]*0.9 The [Price] name automatically refers to the value of the Price column in the current row, no matter where in the table you stick it. Column names are not only major timesavers, they also make your worksheets much easier to understand. After all, who could mistake the following formula? =[Price]*[Tax Rate]+[Shipping Charge] Note: If you use Excel's point-and-click formula creation, Excel uses the column names rather than the cell reference. 14.4.3. Table NamesYou can't use column names in formulas that are in other parts of your worksheet. After all, Excel would have no idea what row you're trying to use. However, Excel gives you another shortcut. You can refer to your entire table anywhere on your worksheet by name . This raises one excellent questionnamely, how does Excel decide what name your table should have? When you first create a table, Excel picks a rather unexciting name like Table1, Table2, and so on. To change this name, click anywhere inside your table, and then edit the text in the Table Tools Design On its own, your table name refers to the entire range of cells that contains the data for your table. That means it includes the entire table, minus the column headers. You may want to use these cells with a lookup function like VLOOKUP( ), as described in Section 12.1.1. For example, if you want to get the price (from the third column) of the product named Persuasive Pencil, you can use this easy-to-read formula: =VLOOKUP("Persuasive Pencil", ProductList, 3, FALSE) Excel includes another treat that lets you dig deeper into your table. You can use the table name in conjunction with a column name to get the range of cells that holds the data for just one column. Here's an example: =SUM(ProductList[Price]) This gets the ProductList table, pulls out the cells in the Price column, and then passes them to the familiar SUM( ) function, which generates a total. You can quickly and efficiently get a hold of parts of a table. Figure 14-24 shows you how Excel helps you create formulas like these with its Formula AutoComplete feature.
14.4.4. The Total Row Excel tables make it easy to calculate totals, averages, standard deviations, and other common formulas by using a dedicated summary row. To show this row, just select Table Tools Design When the total row first appears, it shows only one piece of information: the number of records currently displayed. If you want to show some other type of information, choose a column, and then click the total row cell at the bottom of that column. A drop-down list appears with preset options. Choose one, and the total row displays the calculation in that cell, as shown in Figure 14-25. Of course, you can create your own formulas to show the same information as the total rows do. However, the total row requires no work. It also uses only the rows that are currently visible, ignoring all filtered rows. You may or may not want this behavior. (It's great if you want to calculate totals for a small subset of filtered data, but it's not so good if you want to create grand totals that include everything.) If you don't want this behavior, you can write your own summary formula using functions like SUM( ) (Section 9.2), COUNT( ) (Section 9.2.2), and AVERAGE( ) (Section 9.2.5).
14.4.5. The SUBTOTAL() FunctionThe total row conveniently works only with the currently visible rows in a table. To see this phenomenon in action, simply click one of the cells in the total row. If you look in the formula bar, you see that these cells use the SUBTOTAL() function to perform their calculations. That's because the SUBTOTAL() function is the only Excel function that takes table filtering into account. The SUBTOTAL() function is the perfect solution for all the calculations in the total row, including sums, averages, counts, and more. The trick is that the first argument of the SUBTOTAL() function is a numeric code that tells Excel what type of calculation it should perform, while the second argument is the range of cells for the entire table column, from the first row to the last. SUBTOTAL(function_code, column_range) Table 14-1 lists all the function codes you can use with SUBTOTAL(). Note that each calculation type actually has two function codes associated with it. Function codes above 100 ignore hidden rows (rows that you've hidden using the Home Table 14-1. Function Codes for SUBTOTAL()
In Figure 14-26, compare the results of the SUBTOTAL() functions in cells C2 to C6 to the formulas in cells D2 to D6, which just use the regular functions, like AVERAGE(), SUM(), MAX(), MIN(), and so on. In this figure, the formulas show the totals after filtering's limited the table to items in the Communications category.
14.4.6. The Database FunctionsExcel also includes functions exclusively for use with long tables. These are the database functions , a set of 11 functions that let you analyze groups of data. The database functions are very similar to the basic statistical functions like SUM(), AVERAGE(), and COUNT(). In fact, the database functions have the exact same names, but with an initial letter Dso you find a DSUM(), DAVERAGE(), DCOUNT(), and so on. The database functions differ from their non-database counterparts in that they can selectively filter out rows. In other words, when you use DSUM(), you can specify a set of criteria that a record must match in order to be included in the sum. (The filtering that you've applied with the AutoFilter feature makes no difference to the database functions. They don't ignore hidden rows.) Table 14-2 lists the database functions, along with comparable statistical function. Table 14-2. Database Functions
DGET() is the only function without a statistical counterpart . DGET() works a little like the VLOOKUP() and HLOOKUP() functions (Section 12.1), and it returns a single value in a row that meets the specified criteria. Each database function uses the exact same three parameters: DFUNCTION(table_range, field, criteria)
To use a database function successfully, you need to create a suitable range of cells that you can use for criteria. Excel expects this range of cells to be in a strict arrangement. Here are some rules you'll need to follow:
Filter conditions follow the same rules that you used for search criteria in the data form window. Thus, you can use comparison operators like less than (<) and greater than (>) to create conditions like < 500 (all prices under $500) or <> Travel (all products not in the Travel category). If you don't specify a comparison operator, Excel acts as though you've chosen the equal sign. In addition, it allows partial matches with text criteria. For example, the category criteria T will match both Travel and Tools. You have total freedom to decide where on the worksheet to place your filter cells. Figure 14-27 shows an example that puts the filter cells at the top of the worksheet. To find the average price of all the products that match these criteria, you'd use the following formula: =DAVERAGE(A9:D51, "Price", C1:E2)
The database functions are nice because they force you to define your criteria on the worksheet. If you want to change the criteria, you simply need to modify the condition cells, which makes the database functions a perfect solution for building dynamic reports . |
Properties 
![Excel 2007[c] The Missing Manual](/icons/blank_book.jpg "Excel 2007[c] The Missing Manual"){kind=icon}