Microsoft Office Tutorials and References

In Depth Information

The next example displays the rows in which the listing has a pool
and
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-23, the computed criteria formula is

=(D9/G9)<100

Figure 9-23:
Using computed criteria with advanced filtering.

Here is another example of a computed criteria formula. This formula displays the records listed within the past

60 days:

=B9>TODAY()-60

Keep the following points in mind when using computed criteria:

• Computed criteria formulas are always logical formulas: They must return either TRUE or FALSE.

However, the value that's returned is irrelevant.

• When referring to columns, use a reference to the cell in the first data row in the field of interest (not a refer-

ence to the cell that contains the field name).

• When you use computed criteria, do not use an existing field label in your criteria range. A computed cri-

terion essentially computes a new field for the table. Therefore, you must supply a new field name in the

first row of the criteria range. Or, if you prefer, you can simply leave the field name cell blank.

• You can use any number of computed criteria and mix and match them with noncomputed criteria.

• If your computed formula refers to a value outside the table, use an absolute reference rather than a relative

reference. For example, use $C$1 rather than C1.

• In many cases, you may find it easier to add a new calculated column to your list or table and avoid using

computed criteria.