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