Table Calculation

Formula criteria use calculations that refer to one or more list fields or cells outside the list and will return either TRUE or FALSE. Those records that evaluate to TRUE will be displayed in the filtered list. For example, you could use computed criteria to filter a list to display only those products where the sales for January is greater than the sales for February. Use the following steps as a guideline to creating computed criteria:

1. Add a column to the criteria range using a different name from the other criteria fields (or leave the column heading field blank).
2. Enter the calculation in the criteria range using the first row of data in the list for any references-for example, =A5=C5 will return those records where the value in column A equals the value in column C or =A5>AVERAGE(A:A) will return those records where the value in column A is greater than the average for column A. Use absolute referencing for any cells outside the list but relative referencing for cells within the list (unless you want to test a single reference that does not change).
3. Use AND, NOT, and OR to create compound computed criteria-for example, =AND(A5>AVERAGE(A5:A25), A5<100) will find those records where the value in column A is greater than the average for A5:A25 and less than 100.
4. Add any further criteria that you require. You can use more than one computed criterion in a filter, and you can use computed criteria along with ordinary criteria.
5. Apply the advanced filter as normal (remembering to include the additional column in the criteria range). Ignore any value returned in the criteria range this will only refer to the first row of the list.

You can use the label for the list column in the computed criteria formula instead of a cell reference to test each cell in that column. For example, to find those records where the value in the Hours column is less than 20, you could enter =Hours>20. The cell in the criteria range containing the computed criteria formula will return a #NAME? error when the filter is applied, but the computed criteria will work in the same way.