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
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
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.
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 |
| condition |
|
|
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.
Make sure to let me know in the comments below or on our
No comments:
Post a Comment