Page 636 - Excel 2013 All-in-One For Dummies

618 Filtering Data
Figure 2-7:
Extracted records for personnel whose salaries are above the salary average.
Note that this logical formula is placed under the label Calculated Criteria in cell V2, which has been added to the end of the Criteria Range. Cell F2 is the first cell in the data list that contains a salary entry. The cell range, $F$2:$F$33, used as the argument of the AVERAGE function, is the range in the Salary field that contains all the salary entries.
To use this calculated criterion, you must remember to place the logical formula under a name that isn’t used as a field name in the data list itself. (In this example, the label Calculated Criteria does not appear anywhere in the row of field names.) You must include this label and formula in the Criteria Range. (For this query example, the Criteria Range is defined as the cell range L2:V2.)
When you then perform the query by using the Advanced Filter feature, Excel applies this calculated criterion to every record in the database. Excel does this by adjusting the first Salary field cell reference F2 (entered as a relative reference) as the program examines the rest of the records below. Note, however, that the range reference specified as the argument of the AVERAGE function is entered as an absolute reference ($F$2:$F$33) in the criterion formula so that Excel won’t adjust this reference but compare the Salary entry for each record to AVERAGE computed for this entire range