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

Filtering Data 617
Figure 2-6:
Copied records for the Boston and San Francisco location using an OR condition.
Setting up calculated criteria
You can use calculated criteria when filtering or querying your data list. All you need to do is enter a logical formula that Excel can evaluate as either TRUE or FALSE in the Criteria Range under a name that is not used as a field name in the data list (I repeat, is not a field name in the data list). Calculated criteria enable you to filter or query records based on a comparison of entries in a particular field with entries in other fields of the list or based
on a comparison with entries in the worksheet that lie outside the data list itself.
Figure 2-7 shows an example of using a calculated criterion that compares values in a field to a calculated value that isn’t actually entered in the data list. Here, you want to perform a query that copies all the records from the Employee data list where the employee’s salary is above the average salary. In this figure, cell V2 contains the formula that uses the AVERAGE function to compute average employee salary and then compares the first salary entry in cell F2 of the data list to that average with the following formula:
=F2>AVERAGE($F$2:$F$33)
Book VI Chapter 2
Filtering and Querying a Data List