Section 13.4. Performing List Calculations
13.4. Performing List Calculations
Excel 2003 data lists are particularly useful when you're adding new columns that contain calculations. For example, say you've got a list 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), a data list is a great timesaver. Once you've created the new discount price column, Excel has the smarts to automatically insert the formula into every new record you add, as shown in Figure 13-12. Sadly, this convenience isn't offered to Excel 2002 users (although they can still get the job done with the Copy and Paste commands).
|
Note: The following sections describe a couple of conveniences that Excel 2002 doesn't provide. If you're using Excel 2002, skip ahead to the last two sections, Section 13.4.3 and Section 13.4.4. Both those features, you'll be happy to know, work equally well in Excel 2002.
| WORKAROUND WORKSHOP The Disappearing Cells |
| There is one quirk in list filtering. When you filter a list, Excel hides the rows that contain the filtered records. For example, in Figure 13-10 you'll notice that the row numbers jump straight from 6 to 43, indicating that all the rows in between are hidden. In fact, all Excel really does is shrink each of these rows to have a height of 0 so they're neatly out of sight. The problem is that when Excel hides a row, it hides all the data in that row, even if the data is not a part of the list . That means that if you place a formula in one of the cells to the right of the list, this formula might disappear from your worksheet temporarily when you filter the list! This behavior is quite a bit different than what happens if you delete a row, in which case cells outside the list aren't affected. If you frequently use filtering, you might want to circumvent this problem by putting your formulas underneath or above the list. Generally, putting the formulas above the list is the most convenient choice because the cells don't move as the list expands or contracts. |
13.4.1. Dynamic Calculations
One of the nicest features in Excel 2003 data lists is the way they handle calculations. You can build formulas that use the data in a list, and Excel adjusts them automatically as the list grows or shrinks.
For example, imagine creating a list 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 to the list, Excel automatically updates your formula so it includes this new item:
=SUM(C2:C6)
The same magic happens if your list shrinks because you've deleted an item. The only rule you need to follow is 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 list:
=SUM(C3:C5)
If you expand the list now, Excel doesn't modify the formula. Fortunately, the program has got your back: it'll remind you if you create a formula that includes only a portion of your data list, as shown in Figure 13-13.
Filtering settings don't affect your formulas. If you filter the list so that only some products are visible, the SUM( ) function still produces the same result, which is the total price of all products. If this behavior isn't what you want, you can get around the issue using the total row or Excel's SUBTOTAL( ) function, which are described in the next two sections.
|
13.4.2. The Total Row
Excel 2003 data lists 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 Data 
List Total Row, or click the Toggle Total Row button on the List toolbar. Excel adds an extra row at the bottom of the list, just after the blank new row placeholder.
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 click in the total row cell at the bottom of that column. A drop-down list appears with preset options. Chose one, and the total row displays the calculation in that cell, as shown in Figure 13-14.
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. This may or may not be the behavior you want. (It's great if you want to create grand totals that include everything, but it's not so good if you want to calculate totals for a small subset of filtered data.) If this isn't the behavior you want, you can write your own summary formula using functions like SUM( ) (Section 8.2.1), COUNT( ) (Section 8.2.2), and AVERAGE( ) (Section 8.2.5).
13.4.3. The SUBTOTAL( ) Function
The total row has the handy ability of working only with the rows in a list that are currently visible. To see this phenomenon in action, you simply need to click one of the cells in the total row. If you look in the Formula Bar, you'll 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 list 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 list column, from the first row to the last.
SUBTOTAL(function_code, column_range)
Table 13-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 (from using the Format Row Hide command). These are the function codes that the total row uses. Function codes under 100 don't ignore hidden rows, but they still ignore rows that you've filtered out. Figure 13-15 shows the SUBTOTAL( ) function in action.
Table 13-1. Function Codes for SUBTOTAL( )
| Function Used for Calculation | Code That Ignores Hidden Rows | Code That Includes Hidden Rows |
|---|---|---|
| AVERAGE( ) | 101 | 1 |
| COUNT( ) | 102 | 2 |
| COUNTA( ) | 103 | 3 |
| MAX( ) | 104 | 4 |
| MIN( ) | 105 | 5 |
| PRODUCT( ) | 106 | 6 |
| STDEV( ) | 107 | 7 |
| STDEVP( ) | 108 | 8 |
| SUM( ) | 109 | 9 |
| VAR( ) | 110 | 10 |
| VARP( ) | 111 | 11 |
In Figure 13-15, 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 has limited the list to items in the Communications category.
|
13.4.4. The Database Functions
Excel also includes functions exclusively for use with long lists. 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'll find a DSUM( ), DAVERAGE( ), DCOUNT( ), and so on. The difference between the database functions and their non-database counterparts is that the database functions have the ability to 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 13-2 lists the database functions, along with comparable statistical function.
Table 13-2. Database Functions
| Function | Similar To | Description |
|---|---|---|
| DAVERAGE( ) | AVERAGE( ) | Calculates the average in rows that meet the specified criteria. |
| DCOUNT( ) | COUNT( ) | Counts the number of rows that meet the specified criteria. |
| DCOUNTA( ) | COUNTA( ) | Calculates the number of non-blank values in rows that meet the specified criteria. |
| DGET( ) | - | Returns the value that meets the specified criteria. If more than one value matches, DGET( ) returns the #NUM! error. If no records match, it returns the #VALUE! error. |
| DMAX( ) | MAX( ) | Returns the maximum value in rows that meet the specified criteria. |
| DMIN( ) | MIN( ) | Returns the minimum value in rows that meet the specified criteria. |
| DPRODUCT( ) | PRODUCT( ) | Calculates the product produced by multiplying all values in rows that meet the specified criteria. |
| DSTDEV( ) | STDEV( ) | Calculates the standard deviation in rows that meet the specified criteria. |
| DSUM( ) | SUM( ) | Calculates the sum of values in rows that meet the specified criteria. |
| DVAR( ) | VAR( ) | Estimates the variance of a sample population in the rows that meet the specified criteria. |
| DVARP( ) | VARP( ) | Estimates the variance of an entire population in the rows that meet the specified criteria. |
The only function that doesn't have a statistical counterpart is DGET( ), which works a little like the VLOOKUP( ) and HLOOKUP( ) functions (Section 11.1), and returns a single value in a row that meets the specified criteria.
Each database function uses the exact same three parameters:
DFUNCTION(list_range, field, criteria)
-
The list_range is the range that the function uses. It should include the entire list, including the column you want to use for your calculation and the columns to use for your criteria. The list range must include the column headers, because that's how the database functions identify each column and match it up with the criteria.
-
The field is the name of the column you want to use for the calculation. For example, if you're using DSUM( ), the field is the numeric column you want to total. Excel scans the column headers until it finds the column that has the same name.
-
The criteria is a range of cells that specifies all the conditions that rows must meet to be included in the calculation. This range can be as large as you want, and you're free to define conditions for multiple columns, or multiple conditions that apply to the same column. If the range contains only empty cells, the database function operates on all the items in the list.
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:
-
Each condition needs two cells. One cell specifies the name of the field, and the other cell specifies the filter condition. For example, you can enter "Category" for the field name and "Tools" for the filter condition.
-
The cell with the filter condition must be directly under the cell with the field name. If you put them side-by-side, the database function just gives you an error.
-
You can add as many conditions as you want. You can even use multiple criteria that impose different conditions on the same columns. However, you must place them in columns next to each other. You can't stack them on top of one another.
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.
Where you decide to place your filter cells on the worksheet is completely up to you. Figure 13-16 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 nice thing about the database functions is that 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 .
EAN: 2147483647
Pages: 185