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

(which just happens to be 40,161). Note in Figure 2-7 how Excel 2013 auto- matically converts the cell references ($F$2:$F$33) in the AVERAGE func- tion’s argument to the range name equivalent, (Table2[Salary]).
When entering formulas for calculated criteria that compare values outside the data list to values in a particular field, you should always reference the cell containing the very first entry for that field in order to ensure that Excel applies your criteria to every record in the data list.
You can also set up calculated criteria that compare entries in one or more fields to other entries in the data list. For example, to extract the records where the Years of Service entry is at least two years greater than the record above it (assuming that you have sorted the data list in ascending order by years of service), you would enter the following logical formula under the cell labeled Calculated Criteria:
=I3>I2+2
Most often, when referencing cells within the data list itself, you want to leave the cell references relative so that they can be adjusted, because each record is examined, and the references to the cells outside the database are absolute so that these won’t be changed when making the comparison with the rest of the records.
When you enter the logical formula for a calculated criterion, Excel returns the logical value TRUE or FALSE. This logical value applies to the field entry for the first record in the data list that you refer to in the logical formula. By inspecting this field entry in the database and seeing whether it does indeed meet your intended selection criteria, you can usually tell whether your logi- cal formula is correct.
Using the AND and OR functions in calculated criteria
You can also use Excel’s AND, OR, and NOT functions with the logical opera- tors in calculated criteria to find records that fall within a range. For exam- ple, to find all the records in the employee database where the salaries range between $55,000 and $75,000, you would enter the following logical formula with the AND function under the cell with the label Calculated Criteria:
=AND(F2>=55000,F2<=75000)
To find all the records in the Employee data list where the salary is either below $29,000 or above $45,000, you would enter the following logical for- mula with the OR function under the cell with the label Calculated Criteria:
=OR(F2<29000,F2>45000)
Filtering Data 619
Book VI Chapter 2
Filtering and Querying a Data List