Microsoft Office Tutorials and References
In Depth Information
Specifying Advanced Filter Criteria
Specifying computed criteria
Using computed criteria can make filtering even more powerful. Computed criteria filter the table
based on one or more calculations. For example, you can specify computed criteria that display
only the rows in which the List Price (column D) is greater than average.
=D9>AVERAGE(D:D)
Notice that this formula uses a reference to the first data cell in the List Price column. Also, when
you use computed criteria, the cell above it must not contain a field name. You can leave the top
row blank or provide a descriptive label, such as Above Average. The formula will return a value,
but that value is meaningless.
By the way, you can also use a standard filter to display data that’s above (or below) average.
The next computed criteria example displays the rows in which the price per square foot is less
that $100. Cell D9 is the first data cell in the List Price column, and cell G9 is the first data cell in
the SqFt column. As shown in Figure 9-24, the computed criteria formula is
=(D9/G9)<100
Figure 9-24: Using computed criteria with advanced filtering.
Following is another example of a computed criteria formula. This formula displays the records
listed within the past 60 days:
=B9>TODAY()–60
 
Search JabSto ::




Custom Search