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.
Search JabSto ::

Custom Search