Here are some examples of criteria ranges: a. Simple criteria with one condition.
Example: Store Main Street
b. Criteria with two conditions (placed side-by-side in the same rows). A record must meet both conditions to be selected.
Example:
Store Salesperson Main Street Derre, F.
c. Criteria with two conditions (placed offset in a different row). A record must meet
either of the conditions to be selected. Notice the difference from the preceding
example. Example:
Store Sales amount
Main Street
d. Criteria with multiple conditions. A record must meet all of the conditions to be selected.
Example:
Store Sales amount Date Salesperson
Main Street >3,000 >1/15/2000 Wolftail, J. e. Criteria containing formulas.
Example: New value
=C7>AVERAGE($C$7:$C$44) EXERCISE 8-10
Using a criteria range with only one column label
This exercise demonstrates how to use one column label for the criteria range with the Advanced Filter command.
1. Open the workbook CT2L8P6.XLS. This workbook contains the same records as CT2L8P3.XLS, except that extra rows have been added at the top of the worksheet and the title of the list has been removed.
2. Enter the following labels in cells A1 and A2: A1: Store
A2: Main Street
This criterion specifies that you want to select only those sales records from the Main Street store.
3. Click any cell in the list (for example, cell A10). Choose Advanced. The Advanced Filter dialog box appears. The Action is set to Filter the list, in-place. Do not change this setting. The List range is pre-specified as $A$6:$F$44. If you did not click one of the cells in the list, you must now type the range or name for the list in the List range box.
4. In the Criteria range box, type A1:A2 (see Exhibit 8-15). Click OK, and the list now shows only the records from the Main Street store.
EXHIBIT 8-15
Advanced Filter dialog box
5. In cell A3, type Broadway. The criteria in cells A1:A3 specify that records from both the Main Street store and the Broadway store will be selected.
6. Click cell A6. Choose Advanced. Notice that Excel changed the Criteria range you specified from A1:A2 to $A$1:$A$2. (You should always specify absolute
references in your criteria range, but if you don’t, Excel sets it to absolute reference.) Change the Criteria range to $A$1:$A$3. Click OK.
7. The filtered list now includes records from both the Main Street and Broadway stores. Leave the workbook open for the next exercise.
EXERCISE 8-11
Using a criteria range with more than one condition
This exercise demonstrates how to specify more than one condition in the criteria range. 1. Remove the entry for Broadway in cell A3 (select the cell and press DELETE).
2. Copy the text (Sales amount) in cell C6 to cell B1. In cell B2, type the criterion of >3,000.
The criteria in range A1:B2 specify that you want to select only those sales records from the Main Street store that have sales amounts of more than $3,000.
3. Click a cell in the table. Choose Advanced. Change the Criteria range to $A$1:$B$2. Click OK. The filtered list now shows only six records that meet both of these conditions.
4. Move the value in cell B2 to cell B3. In other words, after the move, cell B2 is blank. The new Criteria range is now A1:B3. By specifying the second criterion in a
separate row from the first criterion, you specify that you want to select those records that are from the Main Street store or are more than $3,000.
5. Click a cell in the table. Choose Advanced. Change the Criteria range to $A$1:$B$3. Click OK. The filtered list now shows 19 records: sales records that meet either of the two conditions. After viewing the results, move the contents of cell B3 back to cell B2.
6. Enter the following criteria into these cells (it is good practice to copy the text of Date from cell B6 to the range C1:D1):
C1: Date C2: >15-Feb-00 D1: Date D2: <22-Feb-00
These two criteria select only those records that are dated between 16-Feb-2000 and 21-Feb-2000, inclusive.
7. Click a cell in the table. Choose Advanced. Change the Criteria range to $A$1:$D$2. Click OK. The filtered list now shows only two records that meet these criteria. In other words, you specified that you want to display only those sales records that are from the Main Street store, of amounts greater than $3,000, and dated between 16- Feb-00 and 21-Feb-00, inclusive.
8. Change the comparison in cell C2 to >1-Feb-00. Notice that the filtered list remains unchanged. When you change criteria, you must refresh the filtered list: Choose Advanced. Click OK in the Advanced Filter dialog box, and the filtered list is updated, showing three records.
9. Save this workbook under your initials and close it. If you had difficulty with this exercise, compare your workbook with the solution in CT2L8P6S.XLS.
EXERCISE 8-12
Using a formula in a criteria range
This exercise demonstrates how to specify a formula in a criteria range. 1. Open the workbook CT2L8P7.XLS.
2. In cells A1 and A2, enter the following: A1: Average sale
A2: =AVERAGE(C7:C44)
Cell A2 displays 2,317.68, being the average of the sales amounts. 3. In cells B1 and B2, enter the following:
B1: Above average B2: =C7>$A$2
Notice that cell B1 must not contain one of the column labels in the list because of the formula. Cell B2 shows a value of FALSE, indicating that the first comparison (C7>$A$2) resulted in a value of 0 (FALSE). This criterion instructs Excel to compare the value in cell C7 with the value in cell A2 for the first record in the list. As Excel processes each record, it will change the cell reference to C8, C9, and so on, without changing the reference to $A$2 (which is fixed by an absolute cell reference).
4. Click any cell in the list (cell A7 will do). Choose Advanced. The Advanced Filter dialog box shows the List range of $A$6:$F$44. Set the Criteria range to
$B$1:$B$2. Click OK. The filtered list now shows only those records that have sales
5. Change the formula in cell B2 to =C7>A2. Repeat step 4, and the filtered list shows no records because the Criteria range was entered incorrectly. Without using absolute cell reference for cell A2, Excel will perform the comparisons of C8>A3, C9>A4, and so on.
6. Change the formula in cell B2 to =C7>AVERAGE($C$7:$C$44). This change has the same effect as the formula =C7>$A$2, except it no longer relies on the computed result in another cell.
7. Repeat step 4, and the filtered list shows the same result as in step 4.
8. Save the completed workbook under your initials. Leave this workbook open for the next exercise. If you had difficulty with this exercise, compare your workbook with the solution in CT2L8P7S.XLS.