This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007/2008

Establishing Criteria

To perform many of Excel's Table/Database analysis functions (Win | Mac), you first need to provide criteria. Criteria is information you provide to relate your data with a particular function; it specifies a cell range, a column label, and a condition (e.g., begins with, contains, between).

Criteria can be established to match a single field or multiple fields, multiple conditions (i.e., AND), one of multiple conditions (i.e., OR), or a range of conditions (BETWEEN). Criteria can look for an exact match or a match within specified parameters. Using range names (Win | Mac) may make database functions easier to write.

NOTE: A Table in the Windows format is referred to as a Database in the Macintosh format.

return to topTypes of Conditions

To properly write criteria, it is important to understand how to format the condition for each criterion. There are three different formats: alphabetic conditions, numeric conditions, and date conditions. The following tables provide the conditions, the correct format, and a sample for each criterion.

Alphabetic Conditions

Condition Format Sample
exact match ="=text_string" ="=john"
begins with text_string john
greater than
or equal to
>letter
>=letter
>j
>=j
less than
or equal to
<letter
<=letter
<j
<=j
between* >letter <letter >j <q

*must be in separate cells within the same row

Numeric Conditions

Condition Format Sample
exact match value 15
contains n/a n/a
greater than
or equal to
>value
>=value
>15
>=15
less than
or equal to
<value
<=value
<15
<=15
between* >value <value >15 <25

*must be in separate cells within the same row

Date Conditions

Condition Format Sample
one date month/day/year 4/1/2008
contains n/a n/a
date after
or equal to
>month/day/year
>=month/day/year
>4/1/2008
>=4/1/2008
date before
or equal to
<month/day/year
<=month/day/year
<4/1/2008
<=4/1/2008
range of dates* >month/day/year<month/day/year >1/1/2008<12/31/2008

*must be in separate cells within the same row

return to topDefining a Single Criterion

A single criterion defines a condition that, when your Table/Database is searched, will return only one type of match for the particular field. The field name goes in the first cell; the condition for that field goes below the field name.

Format

Example

column label
condition
Pay Period
15

NOTE: In this example, only records in which the pay period was equal to 15 would be evaluated for the selected function.

return to topDefining Multiple Criteria

Multiple criteria define conditions that when the Table/Database is searched, will return two or more matches. If both conditions must be met, the criteria need to be set up as AND. If a range of conditions must be met, the criteria need to be set up as BETWEEN. However, if only one of multiple conditions must be met, the criteria should be set up as OR.

AND … Match Two Conditions

For "AND" criteria, the fields are within the same row.

Format

Example

column label column label 2
condition condition
Pay Period Student
15 Johnson

NOTE: In this example, only records in which the pay period is 15 and the student name contains Johnson would be evaluated for the selected function.

BETWEEN ... Match Two Conditions

For "BETWEEN" criteria, the field is repeated in separate cells within the same row.

Format

Example

column label column label
condition condition
Date Date
>3/31/2008 <6/30/2008

NOTE: In this example, only records between March 31, 2008 and June 30, 2008 would be evaluated for the selected function.

OR ... Match Either of Two Conditions (Same Field)

For "OR" criteria with the same field, the field criteria are listed in a column under the field name.

Format

Example

column label
condition
condition
Pay Period
15
16

NOTE: In this example, only records in which the pay period is 15 or 16 would be evaluated for the selected function.

OR … Match Either of Two Conditions (Different Fields)

For "OR" criteria with different fields, the conditions are listed under the appropriate field name but in separate rows so that they are not treated like "AND" conditions.

Format

Example

column label column label 2
condition  
  condition
Pay Period Student
15  
  Doe

NOTE: In this example, records in which the pay period is 15 or the student name contains Doe would be evaluated for the selected function.

Excellence. Our Measure. Our Motto. Our Goal.