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.

Types of Conditions
Defining a Single Criterion
Defining Multiple Criteria

Types 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

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

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

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

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

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

Defining 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

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

Defining 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

Pay Period

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.